Parameter passing

[Eyespi20]

I've sucessfully passed string parameters back to a stored procedure to limit data coming into my report, but now have to pass a list of integers to a report to filter on. My report is failing saying it's unable to convert a string to an integer.

I guess that SSRS passes all such back to an sp as a string. I know there must be a way to "translate" said string to SQL, but am drawing a blank.

Can someone help, please??


TIA,

Margaret
(who plays a DBA on t.v.)

 

[xlbo]

depends what is being passed to what and how

Is the list of integers being passed to a stored proc or from the stored proc to the report. If the latter, how is it being passed through ? to a parameter ? or just as a data set ?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Eyespi20]

It's being passed TO the SP.

Thanks

Margaret

 

[xlbo]

so how is the SP being called?

exec SPName @IntegerList

where IntegerList is a parameter that gets populated with the appropriate values ?

If so then you need to have the variable in the SP defined as varchar(x)

How is the variable used in the SP? is it inserted into the WHERE clause as is or does it need to be split out at all?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Eyespi20]

It's in the where clause as a

Where priority in (@parameter).

Since priority is an integer, having the @parameter come back as a varchar isn't going to work well.

I suppose I could convert the priority field as I call the parameter, but that just seems wrong.

Margaret

 

[Eyespi20]

I just tested that theory and I'm correct.

making the query read Convert(varchar(2), priority) in (@priority)

will not give me an error,however, only returns data if I've chosen one priority and I need for them to be able to choose more than one if they wish.

Margaret

 

[xlbo]

Hows about you have the whole SQL as a string and then use

exec (strSQL)

to execute it. That way you could have somehting like:

set strSQL_SELECT = 'SELECT blah blah FROM X inner join Y on etc etc '

set strSQL_WHERE = 'WHERE Fieldname In (' + @parameter + ') AND Any other WHERE Clause'

set strSQL_FINAL = strSQL_SELECT + strSQL_FINAL

exec (strSQL_Final)

That way it doesn't matter that the list is passed as a string as you are building a string anyway. Within the exec statement, it should parse it all out.

I am assuming by the way that what is passed to the SP is something along the lines of

"1,3,8,10"

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Eyespi20]

yes, the parameter being passed back is 1,2,3 or 8,9 (usually).

The SP is quite involved but the parameter is in the first where clause .... so are you saying instead of using the SP, to completely encompass the entire script into this exec sqlstatement? I don't think I can do that.... I create about 6 temp tables in the sp and then join those together to produce the dataset. I think I would far outstrip the 8000 characters allowed for such a statement.

I just can't understand why it won't pass the parameter back properly....

Margaret

 

[xlbo]

Have you tried making it a varchar in the SP yet ?

I would've thought it wouldn't matter if the string was passed sa a varchar - as long as the numbers don;t have ' ' around them.....

Other than that, can you do a CONVERT or CAST the field to varchar?

WHERE Convert(int,varchar(x)) In (@Parameter)

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Eyespi20]

yes, I've tried that --now I get a "cant covert smallint" error which I'm trying to track down.


Margaret

 

[xlbo]

Which bit did you try ?

passing as varchar or converting the field?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Eyespi20]

I've tried them both now.

Margaret

 

[xlbo]

According to msdn, there should be no problem converting a smallint to a varchar. MAybe worth using CAST instead. There is a specific example here:

http://doc.ddart.net/mssql/sql70/ca-co_1.htm

WHERE CAST(ytd_sales AS char(20)) LIKE '3%'

which is casting a field to a char so your WHERE statement could look like:

WHERE CAST(FieldName AS Varchar(2)) In (@Parameter)



Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Eyespi20]

I finally got it to work....

I had to set the parameter to a string in ssrs
Convert the parameter values to characters
In the dataset/properties(?? The ellipsis )/parameters tab make that parameter CChar(parameters!parameters.Value(0))

Then in my stored procedure set @parameters varchar(11) and in the where clause of the query just use it like "normal" where tablea.columnc in (@parameter)



Thanks for all your help!

Margaret

 

[xlbo]

nice - glad you got it to work - sorry - I didn;t realise that you had the param set up as integer in RS.

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[dkillilea]

I have a similar situation and thought it would be perfect to add to this thread.

I have set up a multi-value parameter which passes a string to dataset. The dataset data type is numeric.

The multi-value parameter is non-queried. It's simply asking clients if they want to select 1, 2, 3, 4 or >=5.

The >=5 is what's throwing the error: Error converting data type varchar to numeric. Even though the parameter is returning a string, individual numbers selected (such as 1,2,4) will work and return the dataset properly. But as soon as I check the box for >=5 it throws the error.