Solved

Stored Pocedure return value

Posted on 2011-03-07
4
360 Views
Last Modified: 2012-05-11
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
Comment
Question by:ayha1999
4 Comments
 
LVL 25

Assisted Solution

by:reb73
reb73 earned 50 total points
ID: 35059346
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
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 35059362
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
 
LVL 2

Accepted Solution

by:
Michael-Thomas earned 75 total points
ID: 35063259
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
 
LVL 32

Expert Comment

by:ewangoya
ID: 35063322

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

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

912 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now