CODEWITHSUNDEEP
sqlsql-server-2008
sequel.learner
sequel.learner

Reputation: 3951

How to perform a task repeatedly in SQL?

There is a SQL script with some declared variables. I want to run this script for various sets of values of these variables and see the outputs. How do I do this?

Upvotes: 1

Views: 144

Answers (3)

PseudoToad
PseudoToad

Reputation: 1574

I would either use a Cursor or a While loop (preference would be the While). It would be something like this

DECLARE @i INT
SET @i = 1

WHILE (@i <=10)
BEGIN    
   -- do whatever you need to do
   SET @i = @i + 1
END

Upvotes: 1

Brian
Brian

Reputation: 5119

Just a note: this answer is copied from here but is a great resource for what you are asking.

More examples for set-based vs. procedural can be found here, here and here.

And here is an actual example in SQL code:

DECLARE @someFlag INT
SET @someFlag = 0
WHILE (@someFlag <=5)
BEGIN
PRINT @someFlag 
SET @someFlag = @someFlag + 1
END
GO

Upvotes: 1

Jason Whitish
Jason Whitish

Reputation: 1438

If you have the appropriate permissions to do it, you could set up the script as a stored procedure and then run the procedure multiple times. Reference on how to do it: http://msdn.microsoft.com/en-us/library/ms187926(v=sql.100).aspx

You don't have to make a permanent proc either, if you don't need it or want it in whatever database you're running it in, you can set it up as a temp proc instead.

So instead of CREATE PROCEDURE dbo.usp_SomeProcedure AS .... you would do CREATE PROCEDURE #usp_SomeProcedure

Your other option is to put your script into an nvarchar(max) variable and use that along with your other variables to run sp_executesql (http://msdn.microsoft.com/en-us/library/ms188001.aspx).

Upvotes: 1

Related Questions