CODEWITHSUNDEEP
sql-servert-sqlstored-procedures
Splendonia
Splendonia

Reputation: 1369

Stored procedure within stored procedure in order to know if a record needs to be updated using SQL Server 2008

I was wondering if it's possible to use the result set from a stored procedure inside another stored procedure to know if a record is different (if it has been edited) in order to update it on the database.

I was thinking something like this:

CREATE PROCEDURE [SISACT].[find_ubicacion] 
@ubicacion_to VARCHAR(8)
AS
SELECT ubicacion, descripcion FROM UBICACIONES WHERE ubicacion = @ubicacion_to

and the one that calls it

CREATE PROCEDURE [SISACT].[update_ubicacion]
@ubicacion VARCHAR(8),
@old_descripcion VARCHAR(60),
@new_descripcion VARCHAR(60)
AS
BEGIN
@old_descripcion = EXEC [SISACT].[find_ubicacion] (@ubicacion)
PRINT @old_descripcion
IF @old_descripcion != @new_descripcion
UPDATE [SISACT].[UBICACIONES] SET descripcion=@new_descripcion WHERE
ubicacion=@ubicacion
ELSE
PRINT 'Nothing to update'
END

Except EXEC [SISACT].[find_ubicacion] (@ubicacion) doesn't work. I'm new to SQL SERVER and Stored Procedures, i'm sorry if this is really basic. I have to do something more complex than this in a couple of days. Is this possible? Also, how do I may know what field to update in case of many fields and not just one, is there a "easy" way to do this?

NOTE: I know this -> 

@old_descripcion = EXEC [SISACT].[find_ubicacion] (@ubicacion)
    PRINT @old_descripcion
    IF @old_descripcion != @new_descripcion

is wrong, i just wanted you to get an idea of what i'm trying to accomplish.

What i'm trying to do here is, verify if the record has been modified or not before updating. That's what i intend using "find_ubicacion", finding the old one, compare it to the input parameters and then updating or not

Like i said, i'm sorry if this is an stupid question or really basic, i'm trying to learn on my own. Thank you in advance for any help.

Upvotes: 0

Views: 59

Answers (1)

EricZ
EricZ

Reputation: 6205

I'm not sure why you need two stored procedures. This can be done by one query

UPDATE [SISACT].[UBICACIONES] 
SET descripcion = @new_descripcion 
WHERE ubicacion = @ubicacion
AND descripcion <> @new_descripcion

UPDATE: If you really need a stored procedure. It should be something like this (untested)

CREATE PROCEDURE [SISACT].[update_ubicacion]
    @ubicacion VARCHAR(8),
    @new_descripcion VARCHAR(60)
AS
BEGIN
  SET NOCOUNT ON

  UPDATE [SISACT].[UBICACIONES] 
  SET descripcion = @new_descripcion 
  WHERE ubicacion = @ubicacion
  AND descripcion <> @new_descripcion 

  IF @@ROWCOUNT = 0
     PRINT 'Nothing to update'     
END

Upvotes: 1

Related Questions