By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,766 Members | 1,252 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,766 IT Pros & Developers. It's quick & easy.

While loop with dynamic sql

P: 31
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?
May 8 '09 #1
Share this Question
Share on Google+
9 Replies

P: 50
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.
May 8 '09 #2

P: 31
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 could u plz help me
May 9 '09 #3

Expert 100+
P: 1,017
Try this example:

Expand|Select|Wrap|Line Numbers
  1. declare @a table (ID int, Val varchar(10))
  2. insert into @a values(1, 'aaa')
  3. insert into @a values(2, 'bbb')
  4. insert into @a values(3, 'ccc')
  5. insert into @a values(4, 'ddd')
  7. Declare @ID int 
  8. Select @ID = min(ID) from @a
  10. While Exists(select * from @a where ID >= @ID)
  11. BEGIN
  12.     Select * from @a where ID = @ID
  13.     Select @ID = min(ID) from @a where ID > @ID
  14. END 
Let me know if you have more specific question.
Good Luck.
May 11 '09 #4

P: 31
Thnks for ur reply ..I want to know how can I use the following statement inside while loop
'Select * from '+@localDBName+'..Customers'
May 13 '09 #5

P: 10
That means you want to use dynamic sql

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

Hope this will work for the part of you SP


-Pankaj Tambe
Jul 16 '09 #6

Expert 100+
P: 1,134
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)

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
Jul 16 '09 #7

P: 31
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.
Jul 29 '09 #8

Expert 100+
P: 1,134
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
Expand|Select|Wrap|Line Numbers
  1. DECLARE @Sql nvarchar(100)
  2. DECLARE @localDBName 
  3. DECLARE @Cnt int
  4. set @localDBName='Server1'
  5. set @Cnt=1
  6. WHILE @cnt<9
  7. BEGIN
  8.    SET @Sql ='Select * from '+ @localDBName + '..Customers'
  9.    EXEC @Sql
  10.    set @Cnt=@Cnt+1
  11.    set @localDBName='Server' + convert(char(1),@Cnt)
  12. 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.
Jul 29 '09 #9

Expert 100+
P: 1,134
Oops, I should have said databases, not servers
Jul 30 '09 #10

Post your reply

Sign in to post your reply or Sign up for a free account.