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.?

Answers


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.


Need Your Help

Can't change the size of a UIWindow

iphone ios cocoa-touch uiwindow

I'm trying to add a new window on top of my current one, on an iPad application, but I can't figure out why it always presents itself as fullscreen.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.