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