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

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.

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).

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.

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'))