MSSQL存儲(chǔ)過(guò)程學(xué)習(xí)筆記一 關(guān)于存儲(chǔ)過(guò)程

Create procedure procedue_name
　　[@parameter data_type][output]
　　[with]{recompile|encryption}
　　as
　　sql_statement

select table_name,column_name from INFORMATION_SCHEMA.COLUMNSwhere COLUMN_NAME like '%A%'; --查看那些表含有包含A的列

select routine_name, routine_definition from information_schema.routines
where routine_definition like '%B%'
and routine_type='procedure'

select * from syscomments; --查看標(biāo)注
select * from sysobjects; --查看數(shù)據(jù)庫(kù)對(duì)象
select * from sysdepends; --查看依賴關(guān)系

Create Procedure Procedure-name ( Input parameters , Output Parameters (If required))AsBegin Sql statement used in the stored procedureEnd

/* Getstudentname is the name of the stored procedure*/
Create PROCEDURE Getstudentname(
@studentid INT --Input parameter , Studentid of the student
)
AS
BEGIN
SELECT Firstname+' '+Lastname FROM tbl_Students WHERE studentid=@studentid
END

/*
GetstudentnameInOutputVariable is the name of the stored procedure which
uses output variable @Studentname to collect the student name returns by the
stored procedure
*/
Create PROCEDURE GetstudentnameInOutputVariable
(
@studentid INT, --Input parameter , Studentid of the student
@studentname VARCHAR(200) OUT -- Out parameter declared with the help of OUT keyword
)
AS
BEGIN
SELECT @studentname= Firstname+' '+Lastname FROM tbl_Students WHERE studentid=@studentid
END

Alter PROCEDURE GetstudentnameInOutputVariable
(
@studentid INT, --Input parameter , Studentid of the student
@studentname VARCHAR (200) OUT, -- Output parameter to collect the student name
@StudentEmail VARCHAR (200)OUT -- Output Parameter to collect the student email
)
AS
BEGIN
SELECT @studentname= Firstname+' '+Lastname,
@StudentEmail=email FROM tbl_Students WHERE studentid=@studentid
END

Declare @Studentname as nvarchar(200) -- 申明第一個(gè)輸出參數(shù)
Declare @Studentemail as nvarchar(50) -- 申明第二個(gè)輸出參數(shù)
Execute GetstudentnameInOutputVariable 1 , @Studentname output, @Studentemail output
Select @Studentname,@Studentemail --“select”語(yǔ)句可以查看結(jié)果