How to return the output of stored procedure into a variable in sql server

Question

I want to execute a stored procedure in SQL Server and assign the output to a variable (it returns a single value) ?

Answer 1

That depends on the nature of the information you want to return.

If it is a single integer value, you can use the return statement

 create proc myproc  as   begin      return 1  end  go  declare @i int  exec @i = myproc 

If you have a non integer value, or a number of scalar values, you can use output parameters

create proc myproc   @a int output,   @b varchar(50) output as begin   select @a = 1, @b='hello' end go declare @i int, @j varchar(50) exec myproc @i output, @j output 

If you want to return a dataset, you can use insert exec

create proc myproc as  begin      select name from sysobjects end go  declare @t table (name varchar(100)) insert @t (name) exec myproc 

You can even return a cursor but that's just horrid so I shan't give an example :)