x

Logic for Stored Proc for selecting multiple comma separated values in parameter?

I have created a report in which I have to select multiple comma separated values in parameter. When I put only query like

select 'DB1' DBName, C1, C2, C3 from DB1.T1 inner join DB1.T2 on T1.C4 = T2.C4
where 'DB1' in (@SelectedDatabase)

UNION ALL
select 'DB2' DBName, C1, C2, C3 from DB2.T1 inner join DB2.T2 on T1.C4 = T2.C4
where 'DB2' in (@SelectedDatabase)

UNION ALL
select 'DB3' DBName, C1, C2, C3 from DB3.T1 inner join DB3.T2 on T1.C4 = T2.C4
where 'DB3' in (@SelectedDatabase)

Report is working fine for multiple values. But when I put this logic into stored proc. For single value it is working fine but for multiple value it is not working. Can some one please help me in this .

1) logic for stored proc for accepting multiple comma separated values?

2) Is this possible i can make my stored proc dynamic in a way so that it takes database name dynamically (for every new database I don't have to alter my proc each and every time).

Thanks
more ▼

asked Nov 29, 2011 at 05:52 AM in Default

dev_ritesh gravatar image

dev_ritesh
13 2 2 3

(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

A while ago I wrote an article about passing multi-value params into a stored proc. It can be found here: http://blog.hoegaerden.be/2009/11/21/reporting-on-data-from-stored-procedures-part-2/ That should help you out with Q1.

As for Q2: well, yes, you could use dynamic SQL, but it does have some drawbacks. Erland Sommarskog wrote a lot of info regarding that topic on the following page: http://www.sommarskog.se/dynamic_sql.html Take your time to read it through and you'll see how helpful it is!

In your particular case, as you need to deal with a random number of databases, you'll probably have to do something like this: loop over all incoming database names and construct each individual query to retrieve data from each DB dynamically. UNION ALL them together and use sp_executesql to fetch the result.
more ▼

answered Nov 29, 2011 at 06:56 AM

Valentino Vranken gravatar image

Valentino Vranken
1.5k 1 2 7

Thanks Valentino. This is very helpful.
Nov 29, 2011 at 07:32 AM dev_ritesh
(comments are locked)
10|1200 characters needed characters left

As @Valentino Vr... mentioned in his answer and blog post, you can pass the coma separated list into the stored proc and then split the string to return a table.

He presented a method using a while loop...

If you want to use a more optimized method for splitting such delimited strings, take a look On @Jeff Moden post http://www.sqlservercentral.com/Forums/Topic943562-338-1.aspx#bm944589 where you can find the fastest known T-SQL solution for splitting strings using a Tally table.

@Jeff Moden has also a great post on SQL Server Central Tally OH! An Improved SQL 8K “CSV Splitter” Function.

If you want the fastest solution, you can incorporate a CLR. You can find a CLR solution on my blog post [Fastest CSV strings splitting using CLR (T-SQL vs. CLR revisited)][3].

[3]: http://www.pawlowski.cz/2010/10/fastest-csv-strings-splitting-using-clr-t-sql-vs-clr-revisited/
more ▼

answered Nov 29, 2011 at 11:57 AM

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.3k 9 11 21

Thanks Pavel.
Nov 29, 2011 at 12:09 PM dev_ritesh
@Pavel Pawlowski: thanks for that link to Jeff's tally table solution, quite interesting and indeed a good alternative to the split function with a loop! It takes a little longer to decipher though :)
Nov 30, 2011 at 06:47 AM Valentino Vranken
(comments are locked)
10|1200 characters needed characters left

I am getting this error while doing this process. I created function which will covert string into table and using that function in my stored proc. the error is :

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AI" in the equal to operation.

Can some one resolve this issue.
more ▼

answered Nov 29, 2011 at 08:21 AM

dev_ritesh gravatar image

dev_ritesh
13 2 2 3

(comments are locked)
10|1200 characters needed characters left

Hello try this:

CREATE FUNCTION [dbo].[UF_MyFunctin]
(
 @psCSString VARCHAR(8000)
)
RETURNS @otTemp TABLE(sID VARCHAR(20))
AS
BEGIN
 DECLARE @sTemp VARCHAR(10)

 WHILE LEN(@psCSString) > 0
 BEGIN
  SET @sTemp = LEFT(@psCSString, ISNULL(NULLIF(CHARINDEX(',', @psCSString) - 1, -1),
                    LEN(@psCSString)))
  SET @psCSString = SUBSTRING(@psCSString,ISNULL(NULLIF(CHARINDEX(',', @psCSString), 0),
                               LEN(@psCSString)) + 1, LEN(@psCSString))
  INSERT INTO @otTemp VALUES (@sTemp)
 END

RETURN
END

And you can use it like:

select * from dbo.UF_MyFunctin('1,2,3')

OR

SELECT *
FROM   myTAble
WHERE  Id IN
(
  select sID from dbo.UF_MyFunctin('1,2,3')
)

OR

SELECT *
FROM   myTAble a
INNER JOIN dbo.UF_MyFunctin('1,2,3') t ON a.Id = t.sID

OR

CREATE TABLE #T (Name varchar(100)) insert into #T VALUES ('value1'),('value2'),('value3')

OR

DECLARE @Temp TABLE(Name varchar(100))
insert into @Temp VALUES ('value1'),('value2'),('value3')

SELECT  *
FROM    @Temp 
WHERE   Name IN (
SELECT * from dbo.UF_CSVToTable('value1,value3'))
more ▼

answered Nov 29, 2011 at 11:27 AM

yaqubonnet gravatar image

yaqubonnet
247 16 17 20

Thanks yaqubonnet :)
Nov 29, 2011 at 12:08 PM dev_ritesh
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1951
x1853
x991
x415

asked: Nov 29, 2011 at 05:52 AM

Seen: 3363 times

Last Updated: Nov 29, 2011 at 05:54 AM