Error withCast 1

[bobpv]

Hi all, I have a SQL database with a field (job number) which is created as a char 5. I want to use it in Crystal reports as a number. So in my stored procedure I did a cast as Numeric

(CAST(joblist.job AS numeric) as job ,

I also set the parameter as follows
CREATE PROCEDURE XXX
@nJobNumber numeric = 0
as
SELECT

Then the where clause
WHERE joblist.job = @nJobNumber

Due to the nature of the database, I have about 7 unioned selects, all the same setup on job number. But I get an error when trying to run the report in Crystal. It's a SQL database error. I am rather new at this and would appreciate some advice. Do I also need to cast the where clause?

Appreciate the help!

 

[topher0303]

There are several ways to approach this. It all depends on how the values are stored in the database.

The best method is to make it a numeric column when the table was created if numeric is the only type of valued that is wanted.

If you do not have control of the design, you could cast the where clause. The only problem with this is that if it is using an index on that column, the index will no longer be used causing a full table scan.

A better way might be to cast the incoming parameter value. This way any indexes can still be used. Example:

WHERE joblist.job = cast(@nJobNumber as varchar)

If the value in the column is formatted with leading zeros or spaces such as '00001' or ' 1', then you must format your parameter value first. Example:

CREATE PROCEDURE XXX
@nJobNumber numeric = 0
as
declare @vJobNum varchar(5)
declare @vFormatJobNum varchar(5)

set @vJobNum = cast(@nJobNumber as varchar)
set @vFormatJobNum = replicate('0', 5 - len(@vJobNum)) + @vJobNum

SELECT
...
WHERE joblist.job = @vFormatJobNum


This will only work if the number in the alpha field is formatted consistently.

Another problem is if a non-numeric value makes its way into the field, you will have to take this into account. Casting a non-numeric will error. You will have to replace it with a numeric value or null.

Yet another way would be to pass a varchar value as the parameter and require the value be passed exactly as it is in the database.

By the way, what exactly is the error message?

 

[bobpv]

Very cool, thank you. I am going to give it a shot in the morning, I am east coast time and burned.

The crystal error is a SQL server error followed by a blank message box. Very hard to figure out what is wrong! I'll let you know how I make out.

Again, many thanks!

 

[bobpv]

Worked sweet! Thanks a bunch!