CODEWITHSUNDEEP
sqlsql-server
TorontoUser
TorontoUser

Reputation: 111

What does this query do in SQL Server?

I am reading somebody's code and I don't quite get this.

DECLARE  @return int 
EXEC @return = dbo.Update

If you are setting return to be an integer, what does the next line mean? How can you set @return to a stored procedure? Does the stored procedure get called?

Upvotes: 1

Views: 127

Answers (3)

SqlZim
SqlZim

Reputation: 38023

That returns the return value from a stored procedure. If a value is not explicitly returned (e.g. return 2;) in the procedure then it will return 0 for a successful execution, or a negative value if an error was encountered.

Reference:

Here are some quick examples: 

create proc dbo.AllGood as
begin;
  select 1 as One into #temp;
end;
go
create proc dbo.ReturnOneForOdd (@i int) as
begin;
  if (@i % 2) = 1 return 1;
end;
go
create proc dbo.DivByZero as
begin;
  begin try
    select 1/0;
  end try
  begin catch
  end catch;
end;
go

declare @r int;

exec @r = dbo.AllGood;
select @r as AllGood; /* returns 0 */

exec @r = dbo.ReturnOneForOdd 1 ;
select @r as ReturnOneForOdd; /* returns 1 */

exec @r = dbo.ReturnOneForOdd 2;
select @r as ReturnOneForOdd; /* returns 0 */

exec @r = dbo.DivByZero;
select @r as DivByZero; /* returns -6 */

rextester demo: http://rextester.com/WPNHX39195

Upvotes: 0

Misery
Misery

Reputation: 495

Yes the stored procedure is called and the return value is stored in @return

As documentation says :

A procedure can return an integer value called a return code to indicate the execution status of a procedure. You specify the return code for a procedure using the RETURN statement. As with OUTPUT parameters, you must save the return code in a variable when the procedure is executed in order to use the return code value in the calling program. For example, the assignment variable @result of data type int is used to store the return code from the procedure my_proc, such as:

DECLARE @result int;  
EXECUTE @result = my_proc;

Return codes are commonly used in control-of-flow blocks within procedures to set the return code value for each possible error situation. You can use the @@ERROR function after a Transact-SQL statement to detect whether an error occurred during the execution of the statement.

Upvotes: 4

maulik kansara
maulik kansara

Reputation: 1107

Check this link for more details regarding storing stored procedure output to a variable. And Stored procedure with OUTPUT variable as parameter.

Upvotes: 0

Related Questions