While loop with dynamic sql

[SnehaAgrawal]

Hi could you please help me with the following...
The syntax for while loop is

While Exists(select * from Customers)
End While

How can I use the above select statment using dynamic SQL?

[Krandor]

That syntax won't work and that really is not the way to go anyway.

Try setting up a cursor (Google sql cursor for details). That will keep looping through the recordset until it is complete.

[SnehaAgrawal]

The above code is just a part of my SP.In that SP I m already using a cursor and in ttht cursor I m using while loop...Bcoz cursors have the tendency to make system slow I think so it wont be a good prog. approach to use it..so could u plz help me

[iburyak]

Try this example:

declare @a table (ID int, Val varchar(10))
insert into @a values(1, 'aaa')
insert into @a values(2, 'bbb')
insert into @a values(3, 'ccc')
insert into @a values(4, 'ddd')
 
Declare @ID int 
Select @ID = min(ID) from @a
 
While Exists(select * from @a where ID >= @ID)
BEGIN
    Select * from @a where ID = @ID
    Select @ID = min(ID) from @a where ID > @ID
END 

Let me know if you have more specific question.
Good Luck.

[SnehaAgrawal]

Thnks for ur reply ..I want to know how can I use the following statement inside while loop
'Select * from '+@localDBName+'..Customers'

[pankajvtcse]

That means you want to use dynamic sql

DECLARE @Sql nvarchar(100)
SET @Sql ='Select * from '+ @localDBName + '..Customers'
EXEC @Sql

Hope this will work for the part of you SP

-HAPPY QUERY

-Pankaj Tambe

[Delerna]

@SnehaAgrawal
Absolutely correct
A while loop and the use of variables to adjust the results of a query
based on the values of the variables definiteley has superior performance than using a cursor.

Never resort to cursors unless you have no other way to achive your goal
and performance is not an issue or the recordset is small.


One correct use for cursors (well correct until I find a better way)
You have a table that lists a set of email addresses that you select as a result of some event.
You loop through the recordset using a cursor so that you can
xp_sendmail to each address.
In this scenario each email sent is similar but different from each other
(not the same)


DYNAMIC QUERIES
These are great and I often use them as a way of not using cursors.
One word of caution however. Don't pass parts of the dynamic query string in as parameters. Doing so opens the door for hackers to use SQL injection.


Just my 2 cents worth

[SnehaAgrawal]

Hi I want to know how can I use a Dynamic SQL Statement inside a While loop

I mean In cursors we fetch rows one by one how can I do that in a while loop that too using Dynamic SQL bcoz I have to access a different server.No issues regarding SQL Injection bcoz this is done internally user isn't invloved.

[Delerna]

You haven't given much of a description of what you are trying to achieve.
Why would you want to select records one by one. You may as well use a cursor if you must do that.
The whole point is that working through a recordset 1 record at a time is slow in comparison to working on the whole recordset as a batch
In other words using a dynamic query to work through a recordset 1 record at a time is no more efficient than using a cursor. Probably less efficient.


The basic idea is like this

DECLARE @Sql nvarchar(100)
DECLARE @localDBName 
DECLARE @Cnt int
set @localDBName='Server1'
set @Cnt=1
WHILE @cnt<9
BEGIN
   SET @Sql ='Select * from '+ @localDBName + '..Customers'
   EXEC @Sql
   set @Cnt=@Cnt+1
   set @localDBName='Server' + convert(char(1),@Cnt)
END
 

Its a ridiculous bit of code that selects the customers records in 8 different servers.
(Server1 through Server8)
But it illustrates the basics of how you use dynamic queries within a while loop.

[Delerna]

Oops, I should have said databases, not servers