Can you call a SQL Stored Procedure that returns a record set and have those values loaded into variables?

Question

Hello fellow stackers

Please consider the following SQL Server table and stored procedure.

create table customers(cusnum int, cusname varchar(50))

insert into customers(cusnum, cusname) values(1, 'Ken')
insert into customers(cusnum, cusname) values (2, 'Violet') --The Wife

create procedure getcus 
  @cusnum int
as 
Begin
   select cusname
   from customers (nolock)
   where cusnum = @cusnum
End

You know how you can write T-SQL code like this:

declare @cusname varchar(50)

select @cusname = cusname
from customers 
where cusnum = 1

Can I do this with my stored procedure?

for example the code would look like this:

declare @cusnum int
declare @cusname varchar(50)

set @cusnum = 1

exec @cusname = cusname pbogetcus @cusnum

Thanks in advance.

Answer 1

No, you can't return values like that.

You need to use OutputParameters: MSDN.

EDIT:

This might be a better link:

SQL Team

Check out the section about midway down: Using OUTPUT variables

Answer 2

You would have to create a user defined table type and use it as an output parameter (this will only work with SQL Server 2008)

CREATE TYPE my_table_type AS TABLE
( customer_name VARCHAR(40) );

CREATE PROCEDURE get_cus(@cusnum AS INT, @out AS my_table_type OUTPUT)
AS
  DECLARE @t AS my_table_type ;

  INSERT INTO @t (customer_name)
  SELECT cusname
  FROM customers 
  WHERE cusnum = @cusnum ;

OR

If you will not be doing any data modification inside your procedure, you could use a function. This is an ideal use of a function, actually, since you probably want to join to this recordset, otherwise you'd just send it back to the client.

CREATE FUNCTION get_cus
AS
  RETURN 
    SELECT cusname
    FROM customers 
    WHERE cusnum = @cusnum ;

Incidentally, you should avoid using NOLOCK hints unless you have a really good reason to use them.