Return value in OUTPUT parameter in 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 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 a SELECT assigns values ​​to variables, all fields in the SELECT must be assigned 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 do not need to use these variables, but they must be assigned in this way.

Your BEST option is to remove the fields. If they are not needed, do not bother to choose them.

[EDIT] to reply to your comment

So, you can do what you want to do by dividing the variable assignment and selecting 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 will allow you to get your output parameter and select all other data without the need to define parameters for each field. You should weigh ease of use (without having to define many local vars) compared to performance (to run two operators, not one). It seems a little strange to me.

I was not entirely clear if you needed other values ​​from GetCustomer sproc available to you in getCustomer_and_more. If yes, then yes, you will need to define OUTPUT parameters for each value you need.

Answer 2

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

 SELECT @CustomerId = CustomerId FROM Customers 

I also assume that there are several clients in the Customers table. So this query will return the last CustomerId, which I believe is not what you want. Therefore, you need to add the WHERE or TOP 1 WHERE .

Something like that:

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

[EDIT] As you state that the error is not contained in the SELECT , and it compiles and works fine, here is the proof. 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 causing your error:

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

This is not a legal SELECT . Try this code and you will see exactly the same error as in the question.