SQLSERVER存储过程实例（下）

来源：互联网发布：linux daemon fork 编辑：程序博客网时间：2024/06/05 18:16

实例3:使用带有复杂 SELECT 语句的简单过程

下面的存储过程从四个表的联接中返回所有作者姓名/出版的书籍/出版社。该存储过程不使用任何参数。

USE pubsIF EXISTS (SELECT name FROM sysobjects<span style="font-family:Times New Roman;"> </span>WHERE name = 'au_info_all' AND type = 'P' )DROP PROCEDURE au_info_allGOCREATE PROCEDURE au_info_allASSELECT au_lname , au_fname , title , pub_nameFROM authors a INNER JOIN titleauthor ta      ON a .au_id = ta .au_id INNER JOIN titles t      ON t .title_id = ta .title_id INNER JOIN publishers p      ON t .pub_id = p .pub_idGO<span style="font-family:Times New Roman;">--</span>au_info_all 存储过程可以通过以下方法执行 ：EXECUTE au_info_all-- OrEXEC au_info_all

实例 4：使用带有参数的简单过程

CREATE PROCEDURE au_info   @lastname varchar(40 ),   @firstname varchar(20 )ASSELECT au_lname , au_fname , title , pub_nameFROM authors a INNER JOIN titleauthor ta      ON a .au_id = ta .au_id INNER JOIN titles t      ON t .title_id = ta .title_id INNER JOIN publishers p      ON t .pub_id = p .pub_idWHERE  au_fname = @firstnameAND au_lname = @lastnameGO<span style="font-family:Times New Roman;">--</span>au_info 存储过程可以通过以下方法执行 ：EXECUTE au_info 'Dull', 'Ann'-- OrEXECUTE au_info @lastname = 'Dull' , @firstname = 'Ann'-- OrEXECUTE au_info @firstname = 'Ann' , @lastname = 'Dull'-- OrEXEC au_info 'Dull', 'Ann'-- OrEXEC au_info @lastname = 'Dull' , @firstname = 'Ann'-- OrEXEC au_info @firstname = 'Ann' , @lastname = 'Dull'<span style="font-family:Times New Roman;">--</span>如果该过程是批处理中的第一条语句 ，则可使用：au_info 'Dull' , 'Ann'-- Orau_info @lastname = 'Dull', @firstname = 'Ann'-- Orau_info @firstname = 'Ann', @lastname = 'Dull'

实例5：使用带有通配符参数的简单过程

CREATE PROCEDURE au_info2@lastname varchar (30) = 'D%',@firstname varchar (18) = '%'ASSELECT au_lname , au_fname , title , pub_nameFROM authors a INNER JOIN titleauthor ta   ON a.au_id = ta.au_id INNER JOIN titles t   ON t.title_id = ta.title_id INNER JOIN publishers p   ON t.pub_id = p.pub_idWHERE au_fname LIKE @firstnameAND au_lname LIKE @lastnameGO<span style="font-family:Times New Roman;">--</span>au_info2 存储过程可以用多种组合执行 。下面只列出了部分组合： EXECUTE au_info2-- OrEXECUTE au_info2 'Wh%'-- OrEXECUTE au_info2 @firstname = 'A%'-- OrEXECUTE au_info2 '[CK]ars[OE]n'-- OrEXECUTE au_info2 'Hunter', 'Sheryl'-- OrEXECUTE au_info2 'H%', 'S%'= 'proc2'

实例6：if...else
存储过程，其中@case作为执行update的选择依据，用if...else实现执行时根据传入的参数执行不同的修改

--下面是if ……else的存储过程：if exists (select 1 from sysobjects where name = 'Student' and type = 'u' )drop table Studentgoif exists (select 1 from sysobjects where name = 'spUpdateStudent' and type = 'p' )drop proc spUpdateStudentgocreate table Student(fName nvarchar (10),fAge<span style="font-family:Courier New;">,</span>smallint ,fDiqu varchar (50),fTel  int)goinsert into Student values ('X.X.Y' , 28 , 'Tesing' , 888888 )gocreate proc spUpdateStudent(@fCase int ,@fName nvarchar (10),@fAge smallint ,@fDiqu varchar (50),@fTel  int)asupdate Studentset fAge = @fAge, -- 传1,2,3 都要更新fAge 不需要用 casefDiqu = (case when @fCase = 2 or @fCase = 3 then @fDiqu else fDiqu end ),fTel  = (case when @fCase = 3 then @fTel else fTel end )where fName = @fNameselect * from Studentgo-- 只改Ageexec spUpdateStudent@fCase = 1,@fName = N'X.X.Y' ,@fAge = 80,@fDiqu = N'Update' ,@fTel  = 1010101-- 改Age 和Diquexec spUpdateStudent@fCase = 2,@fName = N'X.X.Y' ,@fAge = 80,@fDiqu = N'Update' ,@fTel  = 1010101-- 全改exec spUpdateStudent@fCase = 3,@fName = N'X.X.Y' ,@fAge = 80,@fDiqu = N'Update' ,@fTel  = 1010101

0 0