Accesing stored procedure value using output parameter

JonSmith

I have a database that has a stored procedure to fetch a new key for a specified table. Essentially it takes a table name as input parameter, an output parameter for the key value. It grabs the current value for the key and stashes it, then increments the key value, returning the stashed key value.

Procedure [dbo].[GetNextID]
@TableName Varchar(45),
@KeyID Int OutPut

LinQPad sees the stored proc and it thinks the format should be:
ReturnDataSet TypedDataContext.GetNextID(string TableName, int? KeyID)

It should in fact according to all the docs I have read accept the output value as a reference.

int Key = 0;
GetNextID("Customer",ref Key); // LINQpad will not accept this

Instead it insists on the format
int? Key = 0;
GetNextID("Customer",Key);
Console.Writeline("New Key = " + Key). // results in "New Key = 0"

This executes, but I get no valid Key back - Key remains at 0 (of course it would). The value in teh database is incremented as expected of the stored proc.

Any ideas what's going on here? Running the beta 4.42.05

JoeAlbahari

To get the value back, do this:

int returnVal = (int) GetNextID("Customer").OutputParameters["KeyID"];

JonSmith

KeyNotFoundException - the given key was not present in the dictionary.
It also complained that I needed to place the expected KeyID arg in the calling parameters. So I had to write it thusly:
int Key = 0;
Key = (int)GetNextID(Tname, Key).OutputParameters["KeyID"];

This is the SQL generated (looks ok to me)
-- Region Parameters
DECLARE @RETURN_VALUE Int
DECLARE @TableName VarChar(45) = 'CRS_Log'
DECLARE @KeyID Int = 0
-- EndRegion
exec @RETURN_VALUE = [dbo].[GetNextID] @TableName, @KeyID OUTPUT

JoeAlbahari

This is probably because the SP has defined the Key parameter as non-nullable.

So you'll need to do what you did - or this:

int Key = (int)GetNextID(Tname, 0).OutputParameters["KeyID"];

LINQPad deals with output parameters in this way because most of the time, you don't want the output value of parameters defined in SPs as InOut. This avoids forcing you to define a variable and use the ref keyword when you want to call a SP in a "normal" fashion.

JonSmith

Thanks but it is giving me a "key not found" exception trying to access the parameters. The SP does not declare the parameter non-nullable:

ALTER Procedure [dbo].[GetNextID]
@TableName Varchar(45),
@KeyID Int OutPut
As

Declare @SelectError Int,
@ActionError Int,
@UpdateCount Int,
@Environment Varchar(45)

set @SelectError = 0
set @ActionError = 0
set @UpdateCount = 0

Begin ...

JoeAlbahari

Sorry - that should be:

int Key = (int)GetNextID(Tname, 0).OutputParameters["@KeyID"];

You can see all the output parameters by dumping the collection:

GetNextID(Tname, 0).OutputParameters.Dump();

fwd079

Awesome, thanks for the tip. :-)

NikhilSimform

Helpful, Thanks!

NoCo_Founder

Yahtzee! there is my answer!