Output paramter not fetched after ExecuteNonQuery() method

[sujal]

I have a STORED PROC for dynamic T-SQL that returns a OUTPUT varaible of typevarchar . This works fine in query analyzer.

But when I try to get values in asp.net page it does not return anything. No execeptions or errors also.

HDConn = CommonMethods.BuildConnection();
SqlCommand cmd1 = new SqlCommand("GenerateRowids",HDConn);
cmd1.CommandType = CommandType.StoredProcedure;
// add parameters for Proc
cmd1.Parameters.Add("@TableName",SqlDbType.VarChar,50).Value= "MetricsMaster";
cmd1.Parameters.Add("@ColumnName",SqlDbType.VarChar,50).Value= "MetricId";
cmd1.Parameters.Add("@rowidval",SqlDbType.VarChar,30);
cmd1.Parameters["@rowidval"].Direction = ParameterDirection.Output;
try
{
cmd1.ExecuteNonQuery();
//the below line does not print the value
Response.Write(cmd1.Parameters["@rowidval"].Value.ToString());
}

 

[tgreer]

Use the assignment operator with .ExecuteNonQuery():

string myResult = cmd1.ExecuteNonQuery();




Thomas D. Greer

Providing PostScript & PDF
Training, Development & Consulting

 

[sujal]

I'm Sorry. But ExecuteNonQuery() returns number of affected rows while I want the OUTPUT param from procedure.

Here is how my procedure looks like:


CREATE PROCEDURE dbo.GenerateRowids
@TableName varchar(50),
@ColumnName varchar(50),
@rowidval varchar(30) output
AS
Begin
declare @sql nvarchar(4000), @Idval varchar(50)

set @sql = N'select left(MAX(' + @ColumnName +') ,1)+ convert(varchar(5),Cast(SUBSTRING((MAX(' +@ColumnName+ ')),2,(len(MAX(' +@ColumnName+ '))))as int)+1) from ' + @TableName
exec sp_executesql
@query = @sql,
@params = N'@rowidval varchar OUTPUT',
@rowidval = @rowidval OUTPUT
END
GO

When I execute this in query analyzeras below I get value M5 which is expected

declare @test varchar
exec generaterowids 'MetricsMaster','MetricId', @rowidval = @test
print @test

 

[tgreer]

Oh, that's right. Sorry for the off-the-cuff answer; I remember working on this exact issue, and thought I'd remembered the answer.

In my case, the result I wanted was an integer, so I used "return" in my stored procedure, slightly different scenario and syntax.

Are you sure your Stored Proc is properly setting @rowidval?

Thomas D. Greer

Providing PostScript & PDF
Training, Development & Consulting

 

[sujal]

I tried return earlier. It does not go well with varchar types :-(

Another quick question :

Is it better to have the Id column in a table
(ex: studentid) as a Identity datatype or as a char datatype ( to have ids like S1,S2 etc...).

I have so many master tables in my DB and have to maintain a ID column in all these tables.Currently I used the above stored proc to increment those id column values.

 

[tgreer]

Absolutely. Set the ID column as an identity, auto-incremented. You're making yourself do completely unnecessary housekeeping.

Thomas D. Greer

Providing PostScript & PDF
Training, Development & Consulting

 

[sujal]

Thanks a lot. I am just in the initial phase and hence will change my DB design. This should help me eliminate the issue I am facing right now