Returning a value in OUTPUT parameter in a sproc

Question

I have a stored procedure that returns all fields of an object.

CREATE PROCEDURE getCustomer
(
   @CustomerId int OUTPUT
)
AS
BEGIN
    SELECT @CustomerId=CustomerId,FirstName,LastName FROM Customers
END

I want to be able to return the id of the object as an output parameter, so that another sproc can use it. I get this error in this example:

A SELECT statement that assigns a value to a variable must not be combined with 
data-retrieval operations.

CREATE PROCEDURE getCustomer_and_more
AS
BEGIN
DECLARE @CustomerId int

EXEC getCustomer @CustomerId OUTPUT

-- call another sproc that requires this @CustomerId
END

Answer 1

When your SELECT statement assigns variable values, all fields in the SELECT must assign to a local variable.

Change your SPROC to

CREATE PROCEDURE getCustomer
(
 @CustomerId int OUTPUT
)
AS
BEGIN
    DECLARE @FirstName VARCHAR(50)
    DECLARE @LastName VARCHAR(50)

    SELECT @CustomerId=CustomerId, @FirstName = FirstName, @LastName =LastName 
    FROM Customers

You don't have to use them variables, but they must be assigned in this fashion.

Your BETTER option is to remove the fields. If they're not needed, don't bother selecting them.

[EDIT] to respond to your comment

So, you could do what you're looking to do by splitting out the assignment of the variable and selecting the other fields as follows:

CREATE PROCEDURE getCustomer
(
 @CustomerId int OUTPUT
)
AS
BEGIN

    SELECT @CustomerId=CustomerId FROM Customers

    SELECT FirstName, LastName, {A_BUNCH_OF_OTHER_FIELDS}
    FROM
        Customers

This would allow you to get your output param and select all of the other data without having to define params for each field. You have to weigh ease of use (not having to define a lot of local vars) versus performance (having to run two statements as opposed to one). This seems a little strange to me however.

I wasn't quite clear if you needed the other values from the GetCustomer sproc available to you in getCustomer_and_more. If you do, then yes, you'd have to define OUTPUT params for each value you needed

Answer 2

There is an error in the query: FirstName and LastName are redundant.

SELECT @CustomerId = CustomerId
FROM Customers

Also I assume you have more than one customer in the Customers table. So this query will return the last CustomerId, which I assume is not what you want. So you need to add WHERE condition, or TOP 1.

Something like this:

CREATE PROCEDURE getCustomer  (@CustomerId int OUTPUT)
AS
BEGIN
    select @CustomerId = id
    from Customer
    where <SomeCondition>
END

[EDIT] As you state that there the error is not in the SELECT statement and it compiles and works fine, here is the proof. Please try this code will work without problems.

CREATE PROCEDURE getCustomer
(
   @CustomerId int OUTPUT,
   @CustomerName varchar(10) OUTPUT
)
AS
BEGIN
    SELECT @CustomerId = 666, @CustomerName = 'Test'
END
GO

CREATE PROCEDURE getCustomer_and_more
AS
BEGIN
    DECLARE @CustomerId int
    DECLARE @CustomerName varchar(10)

    EXEC getCustomer @CustomerId OUTPUT, @CustomerName OUTPUT

    SELECT @CustomerId, @CustomerName
END
GO

EXEC getCustomer_and_more

This is the code that is causing your error:

DECLARE @CustomerId int

SELECT @CustomerId = CustomerId, FirstName, LastName
FROM Customers

This is not legit SELECT statement. Try this code and you will see exactly the same error as you showed in the question.