Cursor loop is not working. Only taking last value in parameter?
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.?
I don't recommend this approach, but if you look closely you'll see that you're never appending to @sql, merely assigning to it.
Change your else clause to SET @sql = @sql + ....
Well, I can see some weird logic, like the if @sql = ''; but the cause of your error is simply that you are overwriting yor @sql variable, so when you execute it, it only returns values for the last country that the cursor retrieved.