November 30, 2011 at 12:46 pm
I created stored proc so that user can select multiple comma separated values into single parameter.
Here I am getting some problem in looping those values. here is the code
Alter PROCEDURE [dbo].[Testing] @databases varchar(4096)
AS Begin
SET NOCOUNT ON;
Declare @cnt varchar(500)
Declare @sql varchar(Max) = ''
Declare @DB varchar(50)
;WITH MyCTE AS (SELECT * FROM [dbo].[fnSplitStringList] (@Databases))
select * into #temp from MyCTE
DECLARE tenant_cursor CURSOR FOR select * from #temp
OPEN tenant_cursor;
FETCH NEXT FROM tenant_cursor INTO @cnt;
WHILE @@FETCH_STATUS = 0
BEGIN
select @DB = dbname from CountryLookup where dbName = @cnt
if @sql = ''
Begin
SET @sql = 'select C1, C2, C3
from ' + @DB + '.dbo.T1
inner join DB1.T2 on T1.C4 = T2.C4
End
Else
Begin
SET @sql = 'select C1, C2, C3
from ' + @DB + '.dbo.T1
inner join DB1.T2 on T1.C4 = T2.C4
'
End
FETCH NEXT FROM tenant_cursor INTO @cnt;
END
CLOSE tenant_cursor;
DEALLOCATE tenant_cursor;
Drop table #temp
exec (@sql)
END
Now when I execute my proc like exec [dbo].[Testing] 'Usa,japan,France'
It is only giving me records for france.
Can some one help me where I am wrong.?
November 30, 2011 at 1:13 pm
ankurk2 (11/30/2011)
I created stored proc so that user can select multiple comma separated values into single parameter.Here I am getting some problem in looping those values. here is the code
Alter PROCEDURE [dbo].[Testing] @databases varchar(4096)
AS Begin
SET NOCOUNT ON;
Declare @cnt varchar(500)
Declare @sql varchar(Max) = ''
Declare @DB varchar(50)
;WITH MyCTE AS (SELECT * FROM [dbo].[fnSplitStringList] (@Databases))
select * into #temp from MyCTE
DECLARE tenant_cursor CURSOR FOR select * from #temp
OPEN tenant_cursor;
FETCH NEXT FROM tenant_cursor INTO @cnt;
WHILE @@FETCH_STATUS = 0
BEGIN
select @DB = dbname from CountryLookup where dbName = @cnt
if @sql = ''
Begin
SET @sql = 'select C1, C2, C3
from ' + @DB + '.dbo.T1
inner join DB1.T2 on T1.C4 = T2.C4'
End
Else
Begin
SET @sql = 'select C1, C2, C3
from ' + @DB + '.dbo.T1
inner join DB1.T2 on T1.C4 = T2.C4
'
End
FETCH NEXT FROM tenant_cursor INTO @cnt;
END
CLOSE tenant_cursor;
DEALLOCATE tenant_cursor;
Drop table #temp
exec (@sql)
END
Now when I execute my proc like exec [dbo].[Testing] 'Usa,japan,France'
It is only giving me records for france.
Can some one help me where I am wrong.?
You need to either append to @sql each time and add a UNION ALL to every query after the first:
if @sql = ''
Begin
SET @sql = 'select C1, C2, C3
from ' + @DB + '.dbo.T1
inner join DB1.T2 on T1.C4 = T2.C4'
End
Else
Begin
SET @sql = @sql + ' UNION ALL select C1, C2, C3
from ' + @DB + '.dbo.T1
inner join DB1.T2 on T1.C4 = T2.C4
'
End
or create a another temp table #temp2 to hold the results and insert into it each loop, then select C1, C2, C3 from #temp2
or:
create view vwAllDbT1 as
select 'France' DBName, * from France.dbo.T1 UNION ALL
select 'Usa' DBName, * from Usa.dbo.T1 UNION ALL
select 'Japan' DBName, * from japan.dbo.T1
go
select C1, C2, C3
from vwAllDbT1 T1
inner join DB1.dbo.T2 on T1.C4 = T2.C4
where DBName in (SELECT * FROM [dbo].[fnSplitStringList] (@Databases))
Advantages of the last approach, no dynamic sql, no cursor.
November 30, 2011 at 1:14 pm
You are saying IF @sql = '' and after the first run through, it does not = ''. Then you are setting @sql = something. It should be set @sql = @sql + something. That is your issue.
Why are you using a cursor for this. if I may ask?
Lastly, why are you using a CTE and inserting it into a temp table, why not just SELECT * INTO #temp FROM tableFunction()?
Jared
EDIT: fixed original IF statement
Jared
CE - Microsoft
November 30, 2011 at 1:15 pm
Yes I am getting expected result in print statement. But when I execute this proc it is giving me result for only last value which i gave in parameter.
November 30, 2011 at 1:17 pm
Yeah I can use directly into temp table. But i think that's not the issue. Some where lack in looping . If you know please let me know.
November 30, 2011 at 1:20 pm
ankurk2 (11/30/2011)
Yeah I can use directly into temp table. But i think that's not the issue. Some where lack in looping . If you know please let me know.
if @sql = ''
Begin
SET @sql = 'select C1, C2, C3
from ' + @DB + '.dbo.T1
inner join DB1.T2 on T1.C4 = T2.C4'
End
Else
Begin
SET @sql = @sql + ' select C1, C2, C3
from ' + @DB + '.dbo.T1
inner join DB1.T2 on T1.C4 = T2.C4
'
End Note the "SET @sql = @sql + " in your else now.
Jared
Jared
CE - Microsoft
November 30, 2011 at 1:22 pm
I still want to let you know that a cursor is the wrong way to do this. So, unless this is a homework assignment you may want to ask about a better way to do this.
Jared
Jared
CE - Microsoft
November 30, 2011 at 1:26 pm
Hi SSC Enthuastic
Your first method id working perfectly.
Thanks
November 30, 2011 at 1:35 pm
Hi Mr or Mrs. 500
Can you please me the better way to do this .
Thanks
November 30, 2011 at 1:46 pm
ankurk2 (11/30/2011)
Hi Mr or Mrs. 500Can you please me the better way to do this .
Thanks
If you have the ability, I would consolidate all of these into 1 database with a country code identifier. However, if you do not, I would create a view on whichever database you query most that contains data from all databases. Then simply query this view as shown in a previous response from SpringTownDBA.
So, first create the view:
create view vwAllDbT1 as
select 'France' AS DBNAME, * from France.dbo.T1 UNION ALL
...
select 'Japan', * from japan.dbo.T1
Then you write the sp as:
CREATE PROCEDURE test
@databases varchar(4096)
AS BEGIN
select DBNAME, C1, C2, C3
from vwAllDbT1
where DBNAME in (SELECT * FROM [dbo].[fnSplitStringList] (@Databases))
END
Jared
Jared
CE - Microsoft
November 30, 2011 at 2:43 pm
Thanks Jared
I will try this method too. Thanks again for your time.
November 30, 2011 at 2:44 pm
ankurk2 (11/30/2011)
Thanks JaredI will try this method too. Thanks again for your time.
You are very welcome 🙂 Good luck to you!
Jared
Jared
CE - Microsoft
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply