Store Proc where parameter is SELECT column 2

[jromero63]

Hi all,

I've been having a hard time writing a store procedure in MS-SQL 2000 and was wondering if I am going about it all wrong.

In all store procs that I've seen that take parameters, these parameters are commonly used as values for i.e. a SELECT WHERE clause. In my case, one of the parameters needs to be a COLUMN name parameter.

When executing the code below, I get the following error:

Server: Msg 8115, Level 16, State 6, Procedure sp_JLR, Line 21
Arithmetic overflow error converting varchar to data type numeric.

Here is the store proc:

ALTER PROCEDURE sp_JLR
@Period_ID int,
@Rider_ID int,
@Field_To_Query varchar(100),
@Override numeric(25,15) OUTPUT

AS

DECLARE @SQLCommand varchar(200)

SET @SQLCommand =
'SELECT ' + @Field_To_Query + ' AS [value] ' +
'FROM tblRidersByPeriod ' +
'WHERE period_id=' + CONVERT(varchar(100), @Period_ID) + ' AND ' +
'rider_id=' + CONVERT(varchar(100), @Rider_ID)

SELECT @Override = @SQLCommand

RETURN (@@error)

Here is the call to the store proc:

DECLARE @period_id int
DECLARE @rider_id int
DECLARE @field_to_query varchar(100)
DECLARE @return_value numeric(25,15)
DECLARE @Error int

SET @field_to_query = 'Fixed_Retention_Base_Amt'
SET @period_id = 1
SET @rider_id = 1

EXEC @Error = sp_JLR
@Period_ID = @period_id,
@Rider_ID = @rider_id,
@Field_To_Query = @field_to_query,
@Override = @return_value OUTPUT


Any help would be greatly appreciated.

Regards,

Juan Romero

 

[SQLBill]

Here's the way I see it...

You create @SQLCommand as a VARCHAR

DECLARE @SQLCommand varchar(200)

But you've created @Override as a NUMERIC

@Override numeric(25,15) OUTPUT

You are then trying to put a VARCHAR (@SQLCommand) into a NUMERIC (@Override)

SELECT @Override = @SQLCommand

What kind of data is stored in the field that you are querying (VARCHAR or NUMERIC)??

I believe if you make @SQLCommand and @Override the same datatype, your program will work.

-SQLBill

 

[jromero63]

Hi SQLBill,

Thanks for getting back to me as soon as you did.

Your assumptions are correct. The data stored in the field that I am querying is NUMERIC(25,15). @SQLCommand is a VARCHAR containing my built SQL string command.

I've even tried the following:

DECLARE @SQLCommand2 varchar(200)
...
SELECT @SQLCommand2 = @SQLCommand

All I get in return is my entire SQL command and not the returned value which should be @Override Numeric(25,15) OUTPUT.

I know I've just been looking at this code for so long that I can't seem to spot the error.

 

[tlbroadbent]

You need to execute the command that you created. All you've done is attempt to store the command string into a variable.

You can use sp_executesql to execute a dynamic SQL command and return a value. Check the following links.

thread183-148972
thread183-116653
thread183-185849

http://support.microsoft.com/default.aspx?scid=KB;en-us;q262499

Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[osjohnm]

Hi

You need to execute the SQL string...

exec sp_executesql @SQlCommand

that will return the values from your "select" string.

The following should also work,

set @Override = @SQLCommand

exec sp_executesql @Override

Hope this helps

John

 

[jromero63]

Thank you for all of your guidance and examples on using sp_executesql. I've applied the changes as detailed below and I do get a resultset back. However, I can NOT execute any other commands after my call to EXEC sp_executesql. Am I missing something?

ALTER PROCEDURE sp_JLR
@Period_ID int,
@Rider_ID int,
@Field_To_Query varchar(50),
@Override numeric(25,15) OUTPUT

AS

DECLARE @SQLString NVARCHAR(500)
DECLARE @ParmDefinition NVARCHAR(500)

SET @SQLString = N'SELECT ' + @Field_To_Query + ' AS [value]
FROM tblRidersByPeriod
WHERE period_id=@Period_ID
AND rider_id=@Rider_ID'

SET @ParmDefinition = N'@Field_To_Query varchar(100),
@Period_ID int,
@Rider_ID int,
@Override numeric(25,15) OUTPUT'

EXECUTE sp_executesql
@SQLString,
@ParmDefinition,
@Field_To_Query=@Field_To_Query,@Period_ID=@Period_ID,@Rider_ID=@Rider_ID,@Override=@Override OUTPUT

PRINT 'I am back, or am I!?'

RETURN (@@ERROR)

My calling stored procedure:

DECLARE @period_id int
DECLARE @rider_id int
DECLARE @field_to_query varchar(100)
DECLARE @return_value numeric(25,15)
DECLARE @Error int

SELECT @field_to_query = 'Fixed_Retention_Base_Amt'
SELECT @period_id = 1
SELECT @rider_id = 1

EXEC @Error = sp_JLR
@Period_ID = @period_id,
@Rider_ID = @rider_id,
@Field_To_Query = @field_to_query,
@Override = @return_value OUTPUT

PRINT 'Override = ' + CONVERT(varchar(100),@return_value)

Regards,
Juan

 

[tlbroadbent]

You've made more complex than it needs to be. Try the following script. It is untested so it may contain syntax errors.

ALTER PROCEDURE sp_JLR
@Period_ID int,
@Rider_ID int,
@Field_To_Query varchar(50),
@Override numeric(25,15) OUTPUT

AS

DECLARE @SQLString NVARCHAR(500)
DECLARE @ParmDefinition NVARCHAR(30)

SET @SQLString = N'SELECT @override=' + @Field_To_Query +
' FROM tblRidersByPeriod' +
' WHERE period_id=' + str(@Period_ID) +
' AND rider_id=' + str(@Rider_ID

SET @ParmDefinition=N'@Override numeric(25,15) OUTPUT'

EXECUTE sp_executesql
@SQLString,
@ParmDefinition,
@Override=@Override OUTPUT

PRINT 'I am back, or am I!?'

My calling stored procedure:

DECLARE @period_id int
DECLARE @rider_id int
DECLARE @field_to_query varchar(100)
DECLARE @return_value numeric(25,15)
DECLARE @Error int

SELECT @field_to_query = 'Fixed_Retention_Base_Amt'
SELECT @period_id = 1
SELECT @rider_id = 1

EXEC @Error = sp_JLR
@Period_ID = @period_id,
@Rider_ID = @rider_id,
@Field_To_Query = @field_to_query,
@Override = @return_value OUTPUT

PRINT 'Override = ' + CONVERT(varchar(100),@return_value) Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[jromero63]

Terry, that did the trick!

Thank you all very much for your guidance and support!

Kudos to this awesome site!