StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POsqlserver output parameter empty
text
Body
copied!<p>Why does the output parameter return a blank value from identity column after insert?</p> <p>I have tried various tests and the return output parameter is always empty </p> <p>Here is the C# code:</p> <pre><code>using System.Data.SqlClient; SqlConnection conn = new SqlConnection SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; cmd.CommandType = CommandType.Text; cmd.CommandText = "TestSerPsp @ValColp, @RetKeyp"; cmd.Parameters.Add("@RetKeyp", SqlDbType.Int); cmd.Parameters["@RetKeyp"].Direction = ParameterDirection.Output; cmd.Parameters.Add("@ValColp", SqlDbType.NChar); cmd.Parameters["@ValColp"].Value = "QQ"; conn.Open(); cmd.ExecuteNonQuery(); conn.Close(); string x = cmd.Parameters["@RetKeyp"].Value.ToString(); </code></pre> <p>This is the SQL server Stored Procedure:</p> <pre><code>ALTER PROCEDURE [dbo].[TestSerPsp] @ValColp nvarchar(5), @RetKeyp int OUTPUT AS BEGIN SET NOCOUNT ON *** Test 1- This uses the scope identity function Insert into TestSP (ValCol) Values (@ValColp); SELECT @RetKeyp = SCOPE_IDENTITY() *** Test 2- This uses the @@ identity function Insert into TestSP (ValCol) Values (@ValColp) SET @RetKeyp = @@Identity </code></pre> <p>And here is the Table DDL:</p> <pre><code>CREATE TABLE [dbo].[TestSP]( [KeyCol] [int] IDENTITY(1,1) NOT NULL, [ValCol] [nchar](10) NULL, CONSTRAINT [PK_TestSP] PRIMARY KEY CLUSTERED ( [KeyCol] ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] END </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload