sqlserver output parameter empty

Question

Why does the output parameter return a blank value from identity column after insert?

I have tried various tests and the return output parameter is always empty

Here is the C# 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();

This is the SQL server Stored Procedure:

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

And here is the Table DDL:

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

score 0 · Answer 1 · answered Jul 29 '13 at 18:21

0

Have you tried using a CommandType of CommandType.StoredProcedure instead?

For the command text, use the name of the procedure, then assign your parameters and run it.

I am not sure if OUTPUT parameters are supported for CommandType.Text.

answered Jul 29 '13 at 18:21

revlayle

71
5

score 0 · Answer 2 · answered Jul 29 '13 at 19:07

Most people use the "using" statement when dealing with SqlConnection objects.

Example here:

http://www.dotnetperls.com/sqlconnection

I would get the value before closing the connection.

string x = cmd.Parameters["@RetKeyp"].Value.ToString();
conn.Close();

But again, look at the "using" syntax.

sqlserver output parameter empty

2 Answers2