November 29, 2011 at 6:50 am
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
November 29, 2011 at 6:55 am
1. Search the SPLIT function on this site.
2. Read up on Dynamic SQL.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgNovember 29, 2011 at 7:00 am
1) logic for stored proc for accepting multiple comma separated values?
Possible. (don't look at me for SP pls 😛 )
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).
Possible but what's the criteria to add / ignore databases in UNION query?
November 29, 2011 at 7:03 am
there are plenty of articles on how to split comma separated strings on this site.
for (2) you can pass DBName as a parameter and use dynamic SQL
DECLARE @DBNAME sysname;
SET @DBNAME = 'mydb'
DECLARE @SQL nvarchar(max);
SET @SQL = 'SELECT * FROM ' + @DBNAME + '.dbo.Table';
EXEC (@SQL);
for parameter passing to dynamic sql, check out the stored procedure sp_executesql;
November 29, 2011 at 7:19 am
Read the link in my signature for Jeff Moden's splitter. It is super fast and easy to use.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 29, 2011 at 7:27 am
Sean Lange (11/29/2011)
Read the link in my signature for Jeff Moden's splitter. It is super fast and easy to use.
Probably not the best starting point if you're new to this. Its highly technical.
Try http://qa.sqlservercentral.com/articles/T-SQL/62867/ - the earlier version, which is a little easier and just as good for smaller CSV strings.
November 29, 2011 at 7:55 am
create function split(@par varchar(max)
returns table
declare @t table(id varchar(max))
declare @val varchar(100)
begin
while charindex(',',@par)>1
begin
set @val=substring(@val,1,(charindex(',',@par)-1))
insert into @t1 values(@val)
set @par=substring(@par,(charindex(',',@par)+1),len(@par))
end
insert into @t1 values(@par)
return @t1
end
-----------
example
select split('a,b,c')
o/p
----------
a
b
c
November 29, 2011 at 8:39 am
subbareddy542 (11/29/2011)
create function split(@par varchar(max)returns table
declare @t table(id varchar(max))
declare @val varchar(100)
begin
while charindex(',',@par)>1
begin
set @val=substring(@val,1,(charindex(',',@par)-1))
insert into @t1 values(@val)
set @par=substring(@par,(charindex(',',@par)+1),len(@par))
end
insert into @t1 values(@par)
return @t1
end
-----------
example
select split('a,b,c')
o/p
----------
a
b
c
Read the link in my signature or the one Tom posted above. You do NOT want to use a loop for this. It is horribly slow. A tally table is a MUCH faster approach. It takes a little bit of brain tweaking to understand it but once you do it is well worth it.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 29, 2011 at 9:22 am
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.
November 29, 2011 at 9:33 am
ankurk2 (11/29/2011)
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.
Use COLLATE to resolve it.
Example:
SELECT *
FROM TestTab
WHERE GreekCol = LatinCol COLLATE greek_ci_as;
Collation Precedence (Transact-SQL)
November 29, 2011 at 1:06 pm
Thanks all for your help.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply