SQL Summary 4. SQL Summary

SQL Summary 4. SQL Summary

Speaking of SQL, the stored procedure is undoubtedly very important. This article will discuss the stored procedure with you.

1: storage process

A: stored without Parameters

USE testGOCREATE procedure wly -- stored PROCEDURE without the ASSELECT * FROM roleGO Parameter

B: stored with Parameters

USE testGOCREATE PROCEDURE WLY1 @A INTAS DECLARE @B INTSET @B=1SET @A=@BPRINT @AGO

C: storage process with output parameters

USE testGOCREATE PROCEDURE WLY2 @ a int output -- OUTPUT parameter as declare @ B intset @ B = 1 select @ A = @ BPRINT @ AGO

D: some parameters of the stored procedure

SP_HELP WLY1 -- return the name, time, and parameter of the stored procedure SP_HELPTEXT WLY1 -- return the creation statement SP_RENAME WLY and WLY2 -- rename the stored procedure GO

E: Execute the Stored Procedure

Execute dbo. WLYEXECUTE WLY1 -- stored procedure with parameters DECLARE @ c int -- output with parameters EXECUTE WLY2 @ C OUTPUTGO

2: cursor

DECLARE C_ROLE CURSOR SCROLLFOR SELECT RID,RNAME FROM ROLEFOR READ ONLYDECLARE @COUNT INT,@VRID INT,@VRNAME VARCHAR(1)OPEN C_ROLEFETCH NEXT FROM C_ROLEINTO @VRID,@VRNAMESET @COUNT=0WHILE @@FETCH_STATUS=0BEGIN     PRINT @VRID    PRINT @VRNAME    PRINT @COUNT    SET @COUNT=@COUNT+1    FETCH NEXT FROM C_ROLE INTO @VRID,@VRNAMEENDCLOSE C_ROLEDEALLOCATE C_ROLEGO 

I feel that the use of cursors should be avoided as much as possible, because the data is retrieved and stored again, and it is not good if the data volume is large.