June 29, 2009 at 7:37 am
I have a proc with @param1 numeric(2,0) and @param2 numeric(16, 2)
Here is the code
ALTER PROCEDURE TestProc
-- Add the parameters for the stored procedure here
@Param1 NUMERIC(2, 0),
@Param2 NUMERIC(16, 2)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT @Param1, @Param2
END
GO
When I execute the proc with following code
EXEC TestProc@Param1 = 92229.0, @Param2 = 99999.99
I get sql error: Error converting data type numeric to numeric.
This is very confusing error as it does not give me which parameter failed rather. Say if there were 100 parameters and one failed because an application is trying to execute the proc with one param's value bigger than its actual size. It would be too difficult to troubleshoot and find out why proc execution failed from a generic error message shown above.
Did anyone got around this problem?
Bhavesh
.NET and SQL Server Blog
June 29, 2009 at 7:47 am
@Param1 NUMERIC(2, 0)---this says the definition is 2 digits, none after the decimal....but you are assigning a 5 digit number to it: @Param1 = 92229.0
since the biggist that number can be is 99.0, it fails.
change that number to be larger, ie numeric(5,0) at a minimum; i personally assume any number to use the "money" datatype, which is really a decimal(19,4) as well as using decimal because it is more precise...so my typical proc would have a decimal(19,4) definition.
Lowell
June 29, 2009 at 8:50 am
Lowell (6/29/2009)
@Param1 NUMERIC(2, 0)---this says the definition is 2 digits, none after the decimal....but you are assigning a 5 digit number to it: @Param1 = 92229.0since the biggist that number can be is 99.0, it fails.
change that number to be larger, ie numeric(5,0) at a minimum; i personally assume any number to use the "money" datatype, which is really a decimal(19,4) as well as using decimal because it is more precise...so my typical proc would have a decimal(19,4) definition.
I agree what you said, however we have several old procs with 100s of parameters. These procs are used to import data from excel files to database through application. Sometimes data in the excel for a column contains bigger value then its size, which causes sql error specified above. So to reduce user support we want to give user friendly log message to the user about which parameter failed. This will help them to go back to the excel file and sort out values for that column.
Also we dont want to go and change 100s of parameters to support bigger size as the size given are accurate and those columns should not be holding data any bigger then that size. The actual underlying table schema as those size as well.
Bhavesh
.NET and SQL Server Blog
June 29, 2009 at 8:56 am
yeah i agree; either fix the data, or the one parameter raising the issue. I would not try to change hundreds of other parameters...to many things for the QA team to actually have to test.
but having a consistent datatype and size going forward would eliminate annoying issues like this.
Lowell
June 29, 2009 at 9:13 am
Lowell (6/29/2009)
yeah i agree; either fix the data, or the one parameter raising the issue. I would not try to change hundreds of other parameters...to many things for the QA team to actually have to test.but having a consistent datatype and size going forward would eliminate annoying issues like this.
So I take it no way to report error message about which param failed!! apart from developers ensuring they allocate bigger size to params?
But I still belive logging user friendly messages would solve our problems much better as this will ensure they import correct data from the excel.
Bhavesh
.NET and SQL Server Blog
June 29, 2009 at 12:41 pm
How do you access Excel files? Are you using SSIS, linked server, openrowset or any other method?
From my point of view data format validation should be done before you even pass the values to your proc.
For example, you could verify your data with a separate proc that will use the target format, e.g.
SELECT MIN(CAST(ColA AS NUMERIC(4,1)) FROM #t
I would wrap the check in a TRY .. CATCH block.
In the CATCH part I'd use the ERROR_LINE() function to get the parameter that failed.
If you cannot validate the data before calling the proc you cold start the "error analyzer proc" to give you the first Excel column that fails.
This method has the disadvantage of showing you just the first column that failed...
But I couldn't come up with a more easy way as a "quick fix".
Before looking deeper into it I'd like to know a little more about the process you're using to get the Excel data to your stored proc.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply