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
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