stored procedure LANGUAGE 'sql'

[djam]

Does sql stored procedures support loops??

thanks " ahhh computers, how they made our lives much simpler "

 

[osjohnm]

Hi djam

Yes sql stored procedures do support loops. You can use a WHILE statement although SQL uses it slightly differently to say VB code. You can also use a CURSOR which is a SQL recordset, which you loop through and execute other commands per record.

CURSORS should be avoid as they can slow your query times down. Often you can get by without using cursors by using mutliple sql statments. The flip side of the coin is that often there are no alternatives to using a cursor but if the cursor is written correctly and optimally then they can be a powerful function within your procedure.

Have a look in SQL BOL index for the WHILE and CURSOR keyword. You can also search using the keyword search in this forum, there are hundreds of articles on procedures, cursors, loops and while statements.

Hope this helps

John

 

[sguslan]

You can loop like this:

declare @i
set @i=1

while @i<100
begin
set @i=@i+1
end ________________________________________________________________________________
If you do not like change, get out of the IT business...

 

[tlbroadbent]

For good or bad, T-SQL also has a GOTO and other flow-of-control commands. See SQL BOL for details on these commands.

http://msdn.microsoft.com/library/en-us/tsqlref/ts_ca-co_0tnp.asp

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm