Options
Accesing stored procedure value using output parameter
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
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
Comments
int returnVal = (int) GetNextID("Customer").OutputParameters["KeyID"];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
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.
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 ...
Helpful, Thanks!
Yahtzee! there is my answer!