谈谈sqlserver自定义函数与存储过程的区别(2)
实例6:if...else 存储过程,其中@case作为执行update的选择依据,用if...else实现执行时根据传入的参数执行不同的修改. --下面是if……else的存储过程:if exi
实例6:if...else
存储过程,其中@case作为执行update的选择依据,用if...else实现执行时根据传入的参数执行不同的修改.
--下面是if……else的存储过程: if exists (select 1 from sysobjects where name = 'Student' and type ='u' ) drop table Student go if exists (select 1 from sysobjects where name = 'spUpdateStudent' and type ='p' ) drop proc spUpdateStudent go create table Student ( fName nvarchar (10), fAge smallint , fDiqu varchar (50), fTel int ) go insert into Student values ('X.X.Y' , 28, 'Tesing' , 888888) go create proc spUpdateStudent ( @fCase int , @fName nvarchar (10), @fAge smallint , @fDiqu varchar (50), @fTel int ) as update Student set fAge = @fAge, -- 传 1,2,3 都要更新 fAge 不需要用 case fDiqu = (case when @fCase = 2 or @fCase = 3 then @fDiqu else fDiqu end ), fTel = (case when @fCase = 3 then @fTel else fTel end ) where fName = @fName select * from Student go -- 只改 Age exec spUpdateStudent @fCase = 1, @fName = N'X.X.Y' , @fAge = 80, @fDiqu = N'Update' , @fTel = 1010101 -- 改 Age 和 Diqu exec 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
精彩图集
精彩文章