April 14, 2009 at 8:26 am
How can I pass a list of multi-values from a report parameter into a stored procedure and the data type still be an int? When multiple values are selected it becomes a string of 1,6,21...etc, correct? In the stored procedure this string of values need to be in the datatype of int.
I am using the IN keyword in the where clause:
ANDc.location in (@LocationKey) <-- this needs to be an int however muti-values are coming in as varchar
@LocationKey is set as int datatype in the stored procedure
I keep getting the following error due to the data types being different.
Error converting data type varchar to int.
There must be something simple that I have overlooked. Can anyone provide some insight?
Thanks,
Brian
April 14, 2009 at 10:46 am
In my experience the best way to handle this is to "split" the delimited list into a table and join to it. Check out this article[/url] on the most efficient way to do this.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 14, 2009 at 1:10 pm
I 2nd Jack's idea. The last time, I had similar need involving multi-valued parameter, I ended up going the subreport route. Anyways, good luck.
April 14, 2009 at 1:35 pm
Thank you Jack for the helpful information. I created a function to split out the parameter data and this seems to best solution for what I need to accomplish.
April 15, 2009 at 1:31 am
Hello,
Try this
CREATE TABLE [dbo].[Test]
(
ID INT,
NAME NVARCHAR(50)
)
INSERT INTO [dbo].[Test] VALUES (1,'Sample1')
INSERT INTO [dbo].[Test] VALUES (2,'Sample2')
INSERT INTO [dbo].[Test] VALUES (3,'Sample3')
INSERT INTO [dbo].[Test] VALUES (4,'Sample4')
CREATE PROCEDURE [dbo].[Sample]
(
@ID NVARCHAR(50)
)
BEGIN
DECLARE @IDSTR NVARCHAR(100)
SET @ID = '1,2'
SET @IDSTR = CHAR(39) + REPLACE(@ID,',',CHAR(39) + ',' + CHAR(39)) + CHAR(39)
SELECT ID
,NAME
FROM [dbo].[Test]
WHERE ','+ @IDSTR + ',' LIKE '%,' + CHAR(39)+ CAST(ID AS NVARCHAR) + CHAR(39)+',%'
END
Hope helpful...
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply