SQL Server stored Process language method

--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

--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 a temporary table
Create TABLE #temp (
UID int Identity (1, 1) PRIMARY KEY,
UserName varchar (16),
PWD varchar (50),
Age smallint,
Sex varchar (6)
)
--Open Temp table
Select * from #temp

--Stored procedures
--the database to create the stored procedure
Use Test
--Determine if the stored procedure name to be created 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 (16),
@Pwd varchar (50),
@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 '

A question about the target.

Declare DB cursor for//sound a target (db for its name)

SELECT unit_name from UNIT WHERE left (unit_code,2) =left (@strTO, 2)//recording set

Open DB//hit the tour target

FETCH NEXT from DB into @strUNIT_NAME//put the first value in one of the parameters

While @ @fetch_status = 0---There is a downward loop of this pen value

(0);-1: failure, or the information column exceeds the set of results;-2: Captured the information column has been leaked)

Begin----Start the loop

Personal operation

End----Loop

Close DB---closed cursor

DEALLOCATE db//removal reference

SQL Server stored Process language method