SQL Stored Procedure (reprint excerpt)

Article One:

Create a stored procedure
Create Proc dbo. Stored Procedure Name

Stored Procedure Parameters
As
EXECUTE statement
RETURN
Executing stored procedures
GO
*********************************************************/

--the declaration of the variable, which must be added with the @ symbol before the variable is declared in SQL
DECLARE @i INT

--the assignment of a variable, which must be set before the variable is assigned
SET @i = 30

--Declaring multiple variables
DECLARE @s varchar (ten), @a INT

--An If statement in SQL
IF condition BEGIN
EXECUTE statement
END
ELSE BEGIN
EXECUTE statement
END

DECLARE @d INT
Set @d = 1

IF @d = 1 BEGIN

--Print
PRINT ' correct '
END
ELSE BEGIN
PRINT ' ERROR '
END

--A multi-conditional selection statement in SQL.
DECLARE @iRet INT, @PKDisp VARCHAR (20)
SET @iRet = 1
Select @iRet =
Case
When @PKDisp = ' one ' then 1
When @PKDisp = ' two ' then 2
When @PKDisp = ' three ' then 3
When @PKDisp = ' Four ' then 4
When @PKDisp = ' Five ' then 5
ELSE 100
END

--Looping statements
While condition BEGIN
EXECUTE statement
END

DECLARE @i INT
SET @i = 1
While @i<1000000 BEGIN
Set @[email protected]+1
END
--Print
PRINT @i

---TRUNCATE Delete all rows in the table without logging a single row delete operation, not with a condition

   /*
    TRUNCATE table is functionally the same as a DELETE statement without a Where clause: Both delete all rows in the table. However, TRUNCATE TABLE is faster than Delete and uses less system and transaction log resources. The
    DELETE statement deletes one row at a time and records an entry in the transaction log for each row that is deleted. TRUNCATE table deletes data by releasing the data page used to store the table data, and records the release of the page only in the transaction log.
    TRUNCATE table deletes all rows in the table, but the table structure and its columns, constraints, indexes, and so on, remain unchanged. The count value used for the new row identity is reset to the seed of the column. If you want to preserve the identity count value, use Delete instead. If you want to delete the table definition and its data, use the Drop table statement.
    for a table referenced by the FOREIGN KEY constraint, you cannot use TRUNCATE table, and you should use a Delete statement without a Where clause. Because TRUNCATE TABLE is not recorded in the log, it cannot activate the trigger.
    TRUNCATE table cannot be used for tables that participate in indexed views.
    Example
        The following example deletes all data from the authors table. */
       
        TRUNCATE TABLE Authors
               

--Select into creates a new table from the calculation results of a query. The data is not returned to the client, and this is a common
--Select is different. The field for the new table has the name and data type associated with the output field of the Select (same).

SELECT * Into newtable
From Uname

--Insert into Select
--Table ABC must exist
--Copy the field username inside the table uname to the table ABC
Insert into ABC Select Username from Uname

--Create temporary table
        CREATE TABLE #temp (
             UID int Identity (1, 1) PRIMARY KEY,
             UserName varchar (+),
             Pwd varchar (+),
            age smallint,
            Sex varchar (6)
        )
       --Open temp table
         Select * from #temp

-Stored Procedure
       -database to create a stored procedure
         Use Test
       --determine if the stored procedure name you want to create exists
             if Exists (Select name from sysobjects Where name= ' csp_addinfo ' and

Type= ' P ')
           -delete stored procedures
             Drop Procedure dbo.csp_addinfo
         Go
               
               
        --Create a stored procedure
        Create Proc Dbo.csp_addinfo
       -Stored procedure parameters
        @UserName varchar (+),
        @Pwd varchar (),

@Age smallint,
@Sex varchar (6)
As
--Stored Procedure statement body
Insert into Uname (username,pwd,age,sex)
VALUES (@UserName, @Pwd, @Age, @Sex)
RETURN
-Execution
GO

--Execute Stored procedure
EXEC csp_addinfo ' Junn.a ', ' 123456 ', 20, ' Male '

Chapter Two:  

3 return values for stored procedures:

1. return integers as returns

2. Returning parameters in output format

3.Recordset

The difference between return values:

output and return can be received in the batch program with variables, and the recordset is passed back to the client that executed the batch

Article Three:

1. Determine if the insertion value is repeated:

transaction and stored procedure optimization

2. Conditional query Statements:

conditional queries are placed directly in SQL to determine

SQL Stored Procedure (reprint excerpt)