Here's my scenario:
Let's say I have a stored procedure in which I need to call another stored procedure on a set of specific ids; is there a way to do this?
i.e. instead of needing to do this:
Doing something like this:
My Main goal here is simply maintainability (easy to remove/add id's as the business changes), being able to list out all Id's on a single line.. Performance shouldn't be as big of an issue
OMG Ponies
266k63 gold badges452 silver badges471 bronze badges
JohnJohn
9,71814 gold badges53 silver badges74 bronze badges
6 Answers
Adam RobinsonAdam Robinson
153k27 gold badges256 silver badges323 bronze badges
What I do in this scenario is create a table variable to hold the Ids.
-- (or call another table valued function to generate this table)
Then loop based on the rows in this table
or..
Either of above approaches is much faster than a cursor (declared against regular User Table(s)). Table-valued variables have a bad rep because when used improperly, (for very wide tables with large number of rows) they are not performant. But if you are using them only to hold a key value or a 4 byte integer, with a index (as in this case) they are extremely fast.
Charles BretanaCharles Bretana
117k20 gold badges127 silver badges200 bronze badges
use a static cursor variable and a split function:
Cursors have a bad rep since the default options when declared against user tables can generate a lot of overhead.
But in this case the overhead is quite minimal, less than any other methods here. STATIC tells SQL Server to materialize the results in tempdb and then iterate over that. For small lists like this, it's the optimal solution.
Noctis
9,8553 gold badges32 silver badges69 bronze badges
Peter RadocchiaPeter Radocchia
8,7181 gold badge25 silver badges55 bronze badges
kristofkristof
35.3k22 gold badges75 silver badges106 bronze badges
AjV Jsy
4,1112 gold badges24 silver badges26 bronze badges
Ramakrishna TallaRamakrishna Talla
James A Mohler
7,62812 gold badges34 silver badges55 bronze badges
MosheMoshe
Not the answer you're looking for? Browse other questions tagged sqlsql-servertsql or ask your own question.
Non-database programmer here. It happens so, that I need to create a function in T-SQL which returns workdays count between given dates. I believe that the easiest how it's done is with while loop. Problem is, that as soon as I write something like
the statement won't execute, claiming 'incorrect syntax near the keyword 'return' (not very helpful). Where's the problem?
P.S. Full code:
Arnthor
ArnthorArnthor
1,2334 gold badges26 silver badges50 bronze badges
2 Answers
Update Using While Loop In Sql Server
Unlike some languages, the
BEGIN
/END
pair in SQL Server cannot be empty - they must contain at least one statement.
As to your actual problem - you've said you're not a DB programmer. Most beginners to SQL tend to go down the same route - trying to write procedural code to solve the problem.
Whereas, SQL is a set-based language - it's usually better to find a set-based solution, rather than using loops.
In this instance, a calendar table would be a real help. Such a table contains one row for each date, and additional columns indicating useful information for your business (e.g. what you consider to be a working day). It then makes your query for working days look like:
Populating the Calendar table becomes a one off exercise, and you can populate it with e.g. 30 years worth of dates easily.
Damien_The_UnbelieverDamien_The_Unbeliever
201k17 gold badges262 silver badges354 bronze badges
Using any loop within SQL server is never a good idea :)
There are few better solutions, referring to one presented on StackOverflow already.
Community♦
ulathulath
Not the answer you're looking for? Browse other questions tagged sqlsql-server-2008tsql or ask your own question.
In this chapter, we will discuss Loops in PL/SQL. There may be a situation when you need to execute a block of code several number of times. In general, statements are executed sequentially: The first statement in a function is executed first, followed by the second, and so on.
Sql Cursor Loop
Programming languages provide various control structures that allow for more complicated execution paths.
A loop statement allows us to execute a statement or group of statements multiple times and following is the general form of a loop statement in most of the programming languages −
PL/SQL provides the following types of loop to handle the looping requirements. Click the following links to check their detail.
S.No | Loop Type & Description |
---|---|
1 |
PL/SQL Basic LOOP
In this loop structure, sequence of statements is enclosed between the LOOP and the END LOOP statements. At each iteration, the sequence of statements is executed and then control resumes at the top of the loop.
|
2 |
PL/SQL WHILE LOOP
Repeats a statement or group of statements while a given condition is true. It tests the condition before executing the loop body.
|
3 |
PL/SQL FOR LOOP
Execute a sequence of statements multiple times and abbreviates the code that manages the loop variable.
|
4 |
Nested loops in PL/SQL
You can use one or more loop inside any another basic loop, while, or for loop.
|
Labeling a PL/SQL Loop
PL/SQL loops can be labeled. The label should be enclosed by double angle brackets (<< and >>) and appear at the beginning of the LOOP statement. The label name can also appear at the end of the LOOP statement. You may use the label in the EXIT statement to exit from the loop.
While Loop In Function Sql Server Free
The following program illustrates the concept −
When the above code is executed at the SQL prompt, it produces the following result −
The Loop Control Statements
Loop control statements change execution from its normal sequence. When execution leaves a scope, all automatic objects that were created in that scope are destroyed.
PL/SQL supports the following control statements. Labeling loops also help in taking the control outside a loop. Click the following links to check their details.
S.No | Control Statement & Description |
---|---|
1 |
EXIT statement
The Exit statement completes the loop and control passes to the statement immediately after the END LOOP.
|
2 |
CONTINUE statement
Causes the loop to skip the remainder of its body and immediately retest its condition prior to reiterating.
|
3 |
GOTO statement
Transfers control to the labeled statement. Though it is not advised to use the GOTO statement in your program.
|
WHILE loop is the looping construct supported by Sql Server. Sql server doesn’t have for…loop, do…while loop etc, but with WHILE loop we can simulate these missing looping constructs behaviour.
This article will cover the following:
- Introduction to WHILE loop
- BREAK Statement
- CONTINUE Statement
- DO WHILE loop
WHILE LOOP
A while loop will check the condition first and then executes the block of Sql Statements within it as along as the condition evaluates to true.
Syntax:
WHILE Condition
BEGIN
Sql Statements
END
Syntax:
WHILE Condition
BEGIN
Sql Statements
END
Example: Basic while loop example. The below while loop executes the statements within it 4 times.
RESULT:
1
2
3
4
1
2
3
4
BREAK Statement
If a BREAK statement is executed within a WHILE loop, then it causes the control to go out of the while loop and start executing the first statement immediately after the while loop.
Example: WHILE loop with BREAK statement
Uniquegoods 12V 24V 36V 48V 40A 2000W PWM DC Motor Speed. 60A DC Motor Speed Control PWM HHO RC Controller 12V 24V 48V 3000W MAX $19.99. AmpFlow E30-400 Brushed Electric Motor, 12V, 24V or 36 VDC, 5700 RPM. TriStar-60 amp PWM Charge controller(12/24/48 Volt). Controller Type: PWM Meter: No Length: 10.30 IN Width: 5.00 IN. ASC 12/8-AEF, Charge Controller- 8 AMP- 12V- SHUNT- TEMP COMP- Low Voltage Disconnect-. 24 volt rheostat. Amazon.com: ENZPOWER 60Amp PWM 12V 24V Auto with LCD Intelligent Solar Panel Battery Regulator Solar Charge Controller Temperature Compensation. The TriStar pulse width modulated (PWM) charge controller can operate as a solar charge controller, a load controller, or a diversion regulator in 12-, 24-.
RESULT:
CONTINUE Statement
If a CONTINUE statement is executed within a WHILE loop, then it skips executing the statements following it and transfers control to the beginning of while loop to start the execution of the next iteration.
Example: WHILE loop with CONTINUE statement
RESULT:
DO…WHILE Loop in Sql Server
Sql Server doesn’t have a DO…WHILE loop construct, but same behaviour can be achieved using a WHILE loop as shown by the below example.
RESULT:
Looping through table records one row at a time
Article Looping through table records in Sql Server lists out the various approaches of looping through table records row by row.
[ALSO READ] PRINT/SELECT Statement messages within WHILE LOOP or BATCH of statement is not displayed immediately after it’s execution- Sql Server