none
Recorrer tabla Detalle con While y aplicar Update a los registros

    Pregunta

  •  

    Buenas tardes amigos, necesito su apoyo, tengo las siguientes tablas relacionadas en SQL server 2005.

    Tablas:

    tabla Movimientos

    m_id

    Tabla detalleMov

    m_id
    imp_id

    Articulos
    imp_id

    Tabla Stock
    imp_id

    Necesito anular el movimiento de salida y con ello se la cantidad retirada regresa al stock inicial, es por eso que con el numero de mov busco cuanto registro tiene el detalle y ejecuto el while extreando datos de:

    cod de articulo,cantidad y stock q tiene ese articulo

    quiero realizar el update dentro del bucle.

    saludos.

    declare @valor as int
    
    --Cuantos registro tenemos con el @cod
    set @valor= (select count(*) from detallemov where m_id='M120038')-- Extraigo la cantidad de items que tiene el detalle
    print @valor
    --Recorremos si el valor nos devuelve mas
    WHILE @valor > 0
    begin
    
    declare @cant  int
    declare @cod  nvarchar(12)
    declare @stock  int
    
    set @cod=(select imp_id from detalleMov where m_id='M120038')-- extraigo el codigo de articulo segun el n° movimiento
    set @stock =(select s_stock from stock where imp_id=@cod) -- extraigo el stock actual del articulo segun el codigo articulo
    set @cant=(select d_cant from detalleMov where m_id='M120038') -- extraigo la cantidad de la tabla detalle segun el numero de mov.
    update stock set s_stock=@stock + @cant from stock where imp_id=@cod -- actualizo el stock sumando el stock actual + la cantidad segun los aritculos dentro 
    --del detalle
    
    break;
    end


    martes, 22 de mayo de 2012 20:49

Respuestas

  • Te estás enrredando y creando Querys pocos optimizadas, con ésto probablemente resuelvas tu problema evitando crear bucles innecesarios, y teniendo un plan de ejecución cientos de veces mejor..

    update Stock set
    	s_stock = s_stock + tabAux.colCant
    from 
    	stock s
    inner join	
    	(select
    		s.imp_id colId,
    		SUM(dm.d_cant) colCant
    	from detallemov dm
    		join Stock s on dm.imp_id = s.imp_id
    	where 
    		m_id='M120038'
    	group 
    		by s.imp_id) tabAux
    on s.imp_id = tabAux.colId

    Te doy 2 tips extras que te va en forma de consejo:

    1- Nunca utilices como PK una columna de tipo nvarchar(x) ya que tiras al tacho todo el poder de un Clustered Index puede ofreceerte (que por lo general suele ser la PK) y son muy lentas a la hora de buscar en el indice comparadas a los valores numericos.

    2- Tampoco utilices una columna cuyo valor podría variar con el paso del tiempo como PK, que sucedería si la nomemclatura para asignar el ID de la tabla movimientos cambia alguna vez? estarías en graves problemas..


    jueves, 21 de junio de 2012 1:17

Todas las respuestas

  • Hola,

    Te podríamos colaborar mas si nos ayudas con los scripts de creación de tablas y algunos datos de ejemplo para trabajar con información real !


    Un saludo, Cristian Pérez

    martes, 22 de mayo de 2012 22:34
  • -- tabla Stock
    
    CREATE TABLE [dbo].[Stock](
    	[s_id] [int] IDENTITY(1,1) NOT NULL,
    	[s_stock] [int] NULL,
    	[imp_id] [int] NOT NULL,
     CONSTRAINT [pk_Stock] PRIMARY KEY CLUSTERED 
    (
    	[s_id] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    ALTER TABLE [dbo].[Stock]  WITH CHECK ADD  CONSTRAINT [FK_Stock_Implementos] FOREIGN KEY([imp_id])
    REFERENCES [dbo].[Implementos] ([imp_id])
    GO
    ALTER TABLE [dbo].[Stock] CHECK CONSTRAINT [FK_Stock_Implementos]
    
    
    -- Tabla Implementos / articulos
    CREATE TABLE [dbo].[Implementos](
    	[imp_id] [int] NOT NULL,
    	[imp_ref] [nvarchar](30) COLLATE Modern_Spanish_CI_AS NULL,
    	[imp_des] [nvarchar](254) COLLATE Modern_Spanish_CI_AS NULL,
    	[me_id] [int] NULL,
     CONSTRAINT [PK_Implementos] PRIMARY KEY CLUSTERED 
    (
    	[imp_id] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    ALTER TABLE [dbo].[Implementos]  WITH CHECK ADD  CONSTRAINT [FK_Implementos_Medidas] FOREIGN KEY([me_id])
    REFERENCES [dbo].[Medidas] ([me_id])
    GO
    ALTER TABLE [dbo].[Implementos] CHECK CONSTRAINT [FK_Implementos_Medidas]
    
    -- Tabla DetalleMovimientos
    
    CREATE TABLE [dbo].[DetalleMov](
    	[d_id] [int] IDENTITY(1,1) NOT NULL,
    	[imp_id] [int] NULL,
    	[d_cant] [int] NULL,
    	[m_id] [nvarchar](20) COLLATE Modern_Spanish_CI_AS NULL,
     CONSTRAINT [pk_DetalleMov] PRIMARY KEY CLUSTERED 
    (
    	[d_id] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    ALTER TABLE [dbo].[DetalleMov]  WITH CHECK ADD  CONSTRAINT [fk_DetalleMov_Implementos] FOREIGN KEY([imp_id])
    REFERENCES [dbo].[Implementos] ([imp_id])
    GO
    ALTER TABLE [dbo].[DetalleMov] CHECK CONSTRAINT [fk_DetalleMov_Implementos]
    GO
    ALTER TABLE [dbo].[DetalleMov]  WITH CHECK ADD  CONSTRAINT [fk_DetalleMov_Movimiento] FOREIGN KEY([m_id])
    REFERENCES [dbo].[Movimiento] ([m_id])
    GO
    ALTER TABLE [dbo].[DetalleMov] CHECK CONSTRAINT [fk_DetalleMov_Movimiento]
    
    -- Tabla Movimientos
    
    CREATE TABLE [dbo].[Movimiento](
    	[m_id] [nvarchar](20) COLLATE Modern_Spanish_CI_AS NOT NULL,
    	[m_fecha] [datetime] NOT NULL,
    	[m_tipo] [nvarchar](50) COLLATE Modern_Spanish_CI_AS NOT NULL,
    	[m_anular] [bit] NULL,
    	[u_id] [int] NOT NULL,
    	[m_obser] [nvarchar](200) COLLATE Modern_Spanish_CI_AS NULL,
    	[dni] [nvarchar](50) COLLATE Modern_Spanish_CI_AS NULL,
    	[serie_vale] [nvarchar](50) COLLATE Modern_Spanish_CI_AS NULL,
    	[num_vale] [nvarchar](50) COLLATE Modern_Spanish_CI_AS NULL,
     CONSTRAINT [PK_Movimiento] PRIMARY KEY CLUSTERED 
    (
    	[m_id] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    ALTER TABLE [dbo].[Movimiento]  WITH CHECK ADD  CONSTRAINT [FK_Movimiento_Personal] FOREIGN KEY([dni])
    REFERENCES [dbo].[personal] ([DNI])
    GO
    ALTER TABLE [dbo].[Movimiento] CHECK CONSTRAINT [FK_Movimiento_Personal]
    GO
    ALTER TABLE [dbo].[Movimiento]  WITH CHECK ADD  CONSTRAINT [fk_Movimiento_Usuarios] FOREIGN KEY([u_id])
    REFERENCES [dbo].[Usuarios] ([u_id])
    GO
    ALTER TABLE [dbo].[Movimiento] CHECK CONSTRAINT [fk_Movimiento_Usuarios]

    Amigos ahi les paso el Script, unos ejemplos por decir necesito anular el movimiento que se genero como ingreso

    Ejemplo de Egreso

    ------ Datos de Tabla Movimientos
    m_id      M_FECHA                      M_TIPO    DNI
    M120038	2012-05-20 09:11:59.033	      EGRESO	16704583
    
    --- Datos de la tabla detalleMov
    itm     imp_id    cant      m_id
    87	13	  1	M120038
    88	120004	  2	M120038
    
    --- Datos de la tabla Implementos
    
    imp_id  imp_des                me_id
    120004	BOTA DE JEBE N° 32  	1
    
    -- Datos de la tabla Stock
    imp_id      s_stock
    120004        1
    120007        5
    13            3
    
    
    Cuando Guardo los datos del movimiento Egreso / salida 
    genero este scripts 
    
    ALTER procedure [dbo].[spp_GetSaveDetMov]
    (
    @implemento		int,
    @cant			int,
    @id			nvarchar(20)
    )
    as
    declare @stock  varchar(20)
    Begin Tran
    
    
    set @stock = (select s_stock from stock where imp_id=@implemento)
    insert into DetalleMov (imp_id,d_cant,m_id)values(@implemento,@cant,@id)
    
    update stock set s_stock=@stock - @cant where imp_id=@implemento
    Commit tran
    
    con este script resto la cant ingresada en el stock y va recorriendo item por item segun la grilla (Recorro grilla en vb net y con ello genero el bucle).
    
    
    ahora si el usuario se equivoca deberia anular este movimiento y en la grilla que tengo tengo un reporte :
    
    M_ID      M_FECHA               M_TIPO   M_ANULAR
    M120036	20/05/2012 EMPLEADO  	EGRESO	NULL
    M120037	20/05/2012 EMPLEADO  	EGRESO	NULL
    M120038	20/05/2012 EMPLEADO  	EGRESO	NULL
    
    AL SELECCIONAR UNO DE LOS REGISTROS NECESITO EJECUTAR EL SP SIMILAR A ESTO :
    
    create procedure spp_GetAnularMovEgreso
    (
    @cod		nvarchar(12) ' imp_id
    )
    as
    declare @valor as int
    
    --Cuantos registro tenemos con el @cod
    set @valor= (select count(*) from detallemov where m_id='M120038')-- Extraigo la cantidad de items que tiene el detalle
    print @valor
    --Recorremos si el valor nos devuelve mas
    WHILE @valor > 0
    begin
    
    declare @cant  int
    declare @cod  nvarchar(12)
    declare @stock  int
    
    set @cod=(select imp_id from detalleMov where m_id='M120038')-- extraigo el codigo de articulo segun el n° movimiento
    set @stock =(select s_stock from stock where imp_id=@cod) -- extraigo el stock actual del articulo segun el codigo articulo
    set @cant=(select d_cant from detalleMov where m_id='M120038') -- extraigo la cantidad de la tabla detalle segun el numero de mov.
    update stock set s_stock=@stock + @cant from stock where imp_id=@cod -- actualizo el stock sumando el stock actual + la cantidad segun los aritculos dentro 
    --del detalle
    
    break;
    end
    
    Ejecuto este SP y bota un mensaje :
    
    Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
    
    e buscado en internet y segun indica no acepta multiplos  filas o resultados, sin embargo si el detalle movimiento tiene un solo item este si corre pero si tuviera mas no lo ejecuta ya que como sabes una tabla maestro detalle, este ultimo presenta o se agregan mas datos segun el requerimiento.
    
    Espero puedan apoyarme a encontrarme una solucion,gracias
    
    
    
    

    
    miércoles, 23 de mayo de 2012 1:24
  • Hola.

    El error que recibes se debe a que alguna de las 3 sentencias en las que obtienes @cant, @cod y @stock retorna más de un registro. Revísalo.

    Pero quería llamar tu atención sobre el propio nombre del hilo. Trata de trabajar siempre en conjunto, seguro que puedes realizar el update de una vez en lugar de ir recorriendo los registros y lanzando un montón de updates. Cruza "detallemov" con "stock" y un único update hará lo mismo, pero muchísimo más rápido.


    Alberto López Grande
    SQL Server MVP
    Visita mi blog en http://qwalgrande.com
    Sígueme en twitter en http://twitter.com/qwalgrande

    sábado, 09 de junio de 2012 7:48
  • Hola.

    ¿Resolviste el problema?


    Alberto López Grande
    SQL Server MVP
    Visita mi blog en http://qwalgrande.com
    Sígueme en twitter en http://twitter.com/qwalgrande

    domingo, 17 de junio de 2012 15:01
  • Hola Alberto Q tal gracias por responder.. amigo habria forma de hacerlo como dices en un solo update tienes una sintaxis similar..aun no lo puedo realizar cuando ejecuto esta intruccion SQL desde mi aplicacion en .net desde un ctrl button , como puedo ejecutarlo con transacciones talvez?

    gracias por tu apoyo

    miércoles, 20 de junio de 2012 23:00
  • Te estás enrredando y creando Querys pocos optimizadas, con ésto probablemente resuelvas tu problema evitando crear bucles innecesarios, y teniendo un plan de ejecución cientos de veces mejor..

    update Stock set
    	s_stock = s_stock + tabAux.colCant
    from 
    	stock s
    inner join	
    	(select
    		s.imp_id colId,
    		SUM(dm.d_cant) colCant
    	from detallemov dm
    		join Stock s on dm.imp_id = s.imp_id
    	where 
    		m_id='M120038'
    	group 
    		by s.imp_id) tabAux
    on s.imp_id = tabAux.colId

    Te doy 2 tips extras que te va en forma de consejo:

    1- Nunca utilices como PK una columna de tipo nvarchar(x) ya que tiras al tacho todo el poder de un Clustered Index puede ofreceerte (que por lo general suele ser la PK) y son muy lentas a la hora de buscar en el indice comparadas a los valores numericos.

    2- Tampoco utilices una columna cuyo valor podría variar con el paso del tiempo como PK, que sucedería si la nomemclatura para asignar el ID de la tabla movimientos cambia alguna vez? estarías en graves problemas..


    jueves, 21 de junio de 2012 1:17