T-SQL query to add multiple valued report parameter

Question

Post reply

T-SQL query to add multiple valued report parameter

charanadevi

SSC Journeyman

Points: 83
More actions
October 17, 2012 at 9:50 pm

#283522

SELECT
Persons.P_id
,Persons.LastName
,Persons.FirstName
,Persons.Address
,Persons.City
FROM
Persons
where Persons.P_id IN (@P_id)
the above query consider only one parameter when I try to pass two parameters say '1,2' error: conversion failed. how can i pass multiple values by adding simple T-SQL query as,I am directly working on dataset I can not work with ssrs reports and allow multiple values or stored procedure. Is their any other way Just to write any simple t-sql query to solve this. Any help would be appreciated.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

subbu1 Hall of Fame Points: 3281 More actions · Answer 1

first create function after call that function in your query...

Text

CREATE FUNCTION SPLIT(@VAL VARCHAR(MAX))

RETURNS @T1 TABLE(COL1 VARCHAR(MAX))

AS

BEGIN

WHILE CHARINDEX(',',@VAL)>0

BEGIN

INSERT INTO @T1 VALUES(SUBSTRING(@VAL,1,(CHARINDEX(',',@VAL))-1))

SET @val=SUBSTRING(@VAL,(CHARINDEX(',',@VAL))+1,LEN(@VAL))

END

INSERT INTO @T1 VALUES(@VAL)

RETURN

END

SELECT

Persons.P_id

,Persons.LastName

,Persons.FirstName

,Persons.Address

,Persons.City

FROM

Persons

where Persons.P_id IN (select * FROM split(@P_id))

charanadevi SSC Journeyman Points: 83 More actions · Answer 2

charanadevi

SSC Journeyman

Points: 83

October 17, 2012 at 10:42 pm

#1550292

Thank you so much. Thant worked for my code

charanadevi SSC Journeyman Points: 83 More actions · Answer 3

Hi,

This code works good when my souce is SQL. Now when my datasource is not SQL and it is Oracle then it is not working. how should I implement this code to work when choose Oracle datasource for shared dataset query designer.

Any suggestions.

Thanks

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 4

Your original requirement was a T-SQL query which, if I understand correctly, isn't going to work against an Oracle database. You'll need to go to an Oracle forum to get help on that. There may be a way of writing code that works on both Oracle and SQL Server.

John

yudy.varma Right there with Babe Points: 755 More actions · Answer 5

i executed the the below function

CREATE FUNCTION SPLIT(@VAL VARCHAR(MAX))

RETURNS @T1 TABLE(COL1 VARCHAR(MAX))

AS

BEGIN

WHILE CHARINDEX(',',@VAL)>0

BEGIN

INSERT INTO @T1 VALUES(SUBSTRING(@VAL,1,(CHARINDEX(',',@VAL))-1))

SET @val=SUBSTRING(@VAL,(CHARINDEX(',',@VAL))+1,LEN(@VAL))

END

INSERT INTO @T1 VALUES(@VAL)

RETURN

END

Below mentioned is my SP

--exec Fms_Subject 'M001,L001,D004,B001'

Alter Proc Fms_Subject

@Subject_No Varchar(25)

As

Begin

Set NoCount ON

select Subj_SCode,Subj_SName from ERP_Subj

WHERE (Subj_SCode IN (select Subj_SCode from split(@Subject_No)))

order by Subj_SCode

End

when i executed this SP its showing all the records,it is supposed to show the records pertained to passed values i.e: 'M001,L001,D004,B001'

can you tell me how to get the data pertained to only specified values.

ScottPletcher SSC Guru Points: 101099 More actions · Answer 6

SSRS will automatically properly expand an "IN (@multi_valued_report_parameter)" if it's in an SSRS dataset and follows some basic rules. You may want to verify that you have a proper SSRS dataset.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".