Very simple problem, about an hour of my time wasted. So I thought to save others.
SCOPE_IDENTITY not returning value
I have a stored procedure which inserts a new row into a table. I need the primary key or @@IDENTITY/SCOPE_IDENTITY() returned. It is not. Why?…This is what the code generally looked like:
[sql]CREATE PROCEDURE dbo.table1@firstname NVARCHAR(255),@lastname NVARCHAR(255)
AS
INSERT INTO table1 VALUES (@firstname, @lastname)
SELECT SCOPE_IDENTITY()[/sql]
Now, when I run the query in SQL Server, I get the output I want, which is a single row containing the primary key which was just inserted. When the query is run in my app, the entry gets inserted, but no value is returned (side note, I am not using Visual Studio, or any other IDE with debugging tools, so all output is ending up on an rendered html page).
How to return the correct value
In the stored procedure, (after my parameters but before the logic) put:
[sql]SET NOCOUNT ON[/sql]This will suppress the SQL Server message regarding the number of rows affected.
SET NOCOUNT ON prevents the sending of DONE_IN_PROC messages to the client for each statement in a stored procedure
From: MSDN
It’s an annoying little quirk, which I am sure can be useful, but in the long run seems to send unnecessary data across the network, and if you look at the above MSDN article, Microsoft suggests that by turning it on, you can get a significant performance boost, especially when your stored procedure will affect many rows in your database.
Here is the final:
[sql] CREATE PROCEDURE dbo.table1 @firstname NVARCHAR(255), @lastname NVARCHAR(255)AS
SET NOCOUNT ON
INSERT INTO table1 VALUES (@firstname, @lastname)
SELECT SCOPE_IDENTITY()[/sql]
Reference:
Thanks so much for the post. I’ve just spent a day trying to figure this out. I had exactly the same problem.