To supplement the basic knowledge, part of the summary of commonly used process control statements
1 -- BEGIN... END: Execute multiple TSQL statements together
BEGIN TRANSACTION SELECT @@TRANCOUNT--Transaction counting,Result 1 IF @@TRANCOUNT=0 BEGIN--Statement block start SELECT TOP 1 * FROM dbo.LthUinfo ROLLBACK TRAN PRINT 'ROLLBACK1' END--Statement Block End ROLLBACK TRAN--Roll back transactions, count minus 1 SELECT @@TRANCOUNT--Here result 0 PRINT 'ROLLBACK2' --Result: Print message ROLLBACK2
2--IF..ELSE:IF the Boolean expression after IF is true execution, it executes its statement, and it executes the post-ELSE statement for false execution.
IF DATENAME(WEEKDAY,CURRENT_TIMESTAMP) IN('Sunday','Saturday') PRINT 'Today is the weekend' ELSE PRINT 'Today is a working day.'; --Be careful if..else in ELSE Is an optional keyword,Usually they execute the first statement next to it --If more than one can be used BEGIN..END Formal Implementation of Containing Statement Blocks
3--WHILE: Loop statement that repeats execution as long as the condition specified for the loop is true
- Several keywords commonly used in control loops: BREAK,CONTINUE
DECLARE @count AS INT=0 WHILE @count<10 BEGIN IF @count=4 BEGIN SET @count+=1 CONTINUE--Break this cycle, ignore it CONTINUE The following statement continues //Start the next cycle END PRINT 'count='+CAST(@count AS CHAR); SET @count+=1 IF @count=6 BREAK;--End the current loop. If it's an internal nested loop, it ends the inner loop and starts //External circulation END --Output results: /* count=0 count=1 count=2 count=3 count=5 */
4--GOTO: Jump the execution flow to the tag to execute the statement after the tag
--CASE It's a scalar expression.,Can't be used to control execution flow --GOTO It must be followed by a label and cannot be replaced by a variable. DECLARE @label AS INT=1 DECLARE @value AS VARCHAR(20) /* CASE WHEN @label=1 THEN GOTO ONE WHEN @label=2 THEN GOTO TWO WHEN @label=3 THEN GOTO THREE END */ SET @value=CHOOSE(@label,'ONE','TWO','THREE')--according to label Value returns the following index value SELECT @value--Returns the result: ONE IF @value='ONE' GOTO ONE -- IF @value='TWO' GOTO TWO IF @value='THREE' GOTO THREE --Define labels ONE: PRINT 'ONE' GOTO OVERV TWO: PRINT 'TWO' THREE: PRINT 'THREE' OVERV: PRINT 'over' PRINT 'Output test' --Returns the result: ONE over //Output test --Note: If GOTO If the statement points to the tag, it is the starting point for processing, and all the following statements can be executed --Whether used or not GOTO Statements and labels can be used as annotation methods.
5--RETURN: Execution is immediate and complete and can be used to exit from a process, batch, or statement block at any time. RETURN
The following statement is not executed. Usually used for stored procedures or functions to return a value.
DECLARE @count AS INT=0 DECLARE @count2 AS INT=10 WHILE @count2>0 BEGIN WHILE @count<10 BEGIN IF @count=4 BEGIN SET @count+=1 CONTINUE--Break this cycle, ignore it CONTINUE The following statement continues //Start the next cycle END PRINT 'count='+CAST(@count AS CHAR); SET @count+=1 IF @count=6 --BREAK;--End this cycle RETURN;--End the batch statement and execute the stream so far that none of the following statements will persist //That's ok END PRINT 'ok' SET @count2-=1 END GO --Be careful, RETURN Statements are used to end batch statements, execute the stream to this end, everything OVER Now. here RETURN Default return //Back to 0, but you can't get it and use it. --usually RETURN Returns a value for a function or stored procedure
6--WAITFOR prevents execution of batches, stored procedures, or transactions until a specified time or interval has passed
SELECT GETDATE(); WAITFOR --DELAY '00:00:05.001'--Fixed time interval, maximum not more than 24 hours TIME '16:18'--Fixed time point, 16:18 p.m. SELECT GETDATE();
7 - throw, try.. Catch to be continued