asked
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
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.
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'))
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][1] 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][2]. 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]. [1]:
http://www.sqlservercentral.com/Forums/Topic943562-338-1.aspx#bm944589 [2]:
http://www.sqlservercentral.com/articles/Tally+Table/72993/ [3]:
http://www.pawlowski.cz/2010/10/fastest-csv-strings-splitting-using-clr-t-sql-vs-clr-revisited/
@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 :)
