• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 386
  • Last Modified:

Stored Pocedure return value

In the following SP, how can I return1 for the update and delete and 0 if no update and delete?

IF NOT EXISTS (
     SELECT a.* FROM Table1 where qty > 100 and Id=@Id
         
  BEGIN
       UPDATE Table1 SET field1=@field1 where Id=@Id
       delete from Table1 where Id=@Id
   END

thanks

ayha
0
ayha1999
Asked:
ayha1999
2 Solutions
 
reb73Commented:
Like this -

IF NOT EXISTS (
     SELECT a.* FROM Table1 where qty > 100 and Id=@Id
         
  BEGIN
       UPDATE Table1 SET field1=@field1 where Id=@Id
       delete from Table1 where Id=@Id
       RETURN 1
   END
ELSE
 BEGIN
     RETURN 0
 END
0
 
Carl TawnSystems and Integration DeveloperCommented:
You would be better to SELECT 1 / SELECT 0 rather than RETURN purely because you can use ExecuteScalar() to retrieve the value if you use SELECT, otherwise you have to mess about with return parameters on your command object.
0
 
Michael-ThomasCommented:
You could also use an output parameter to return values.  

create procedure dbo.MyStoredProcedure @ID INT, @Field1 varchar(10), @Updated BIT OUTPUT
as
IF NOT EXISTS (SELECT a.* FROM Table1 where qty > 100 and Id=@Id)
          
  BEGIN
       UPDATE Table1 SET field1=@field1 where Id=@Id
       delete from Table1 where Id=@Id
		set @Updated = 1
   END 
 ELSE
	SET @Updated = 0

Open in new window

0
 
Ephraim WangoyaCommented:

Your statement does not seem to be correct

  IF NOT EXISTS (SELECT a.* FROM Table1 where qty > 100 and Id=@Id) --this means the record is not found  
  BEGIN
       UPDATE Table1 SET field1=@field1 where Id=@Id  //what exactly are you updating, the search condition was not satisfied
       delete from Table1 where Id=@Id                          //will delete all records with id=@id  ????
   END
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now