Solved

Stored Pocedure return value

Posted on 2011-03-07
4
362 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…

772 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