A general stored procedure definition includes the following parts:
1. Stored procedure name
CREATE PROCEDURE procedure_name
Or:Create proc procedure_name
2. Parameters, input parameters, output parameters
@parameter_name1 int, //Input parameter definition, including name, type and length
@parameter_name2= defual_value varchar(80) //Input parameter with default value
@parameter_out int output //Output parameters
AS //The part after as is the specific content of the stored procedure
3. Internal parameter definition, the definition method is as in 2, you need to define the name, type, and length
Declare
@Parameter_inter1 int,
@Parameter_inter2 varchar(30)
4. Initialize internal parameters:
Set @Parameter_inter1 =5,
//The required values can be obtained and stored in internal parameters:
SELECT @parameter_inter2=table.column FROM table WHERE …….
5. Specific operation statements generally include the following process control statements (if else | select case | while):
===============Select ... CASE (multiple conditions) Example: ============
DECLARE @iRet INT, @PKDisp VARCHAR(20)
SET @iRet = '1'
Select @iRet =
CASE
WHEN @PKDisp = '一' THEN 1
WHEN @PKDisp = '二' THEN 2
WHEN @PKDisp = '三' THEN 3
WHEN @PKDisp = '四' THEN 4
WHEN @PKDisp = '五' THEN 5
ELSE 100
END
========== While (Loop) Example: ====================
DECLARE @i INT
SET @i = 1
WHILE @i<1000000
BEGIN
set @i=@i+1 //Change conditions, compare
END
PRINT @i
============= If (single condition) processing example: ================
IF @strTO<>'' //Condition
BEGIN
UPDATE UNIT SET UNIT_NAME=REPLACE(UNIT_NAME,'*','')
WHERE UNIT_CODE=@strTO
END
ELSE BEGIN
UPDATE UNIT SET UNIT_NAME=UNIT_NAME+'*' WHERE UNIT_CODE='011'
END
6.Finally:
Go
Use stored procedure:
Execute procedure_name
With parameters:
Execute procedure_name 'parameter1_value','paramerter2_ value'
or:
Exec procedure_name paramerter1='parameter1_value',
parameter2='paramerter2_value'
This article comes from the CSDN blog. Please indicate the source when reprinting: http://blog.csdn.net/bestxulei/archive/2009/12/22/5053562.aspx