关于存储过程

 

删除存储过程

Drop Procedure 存储过程名

 

创建一个简单的有添加作用的存储过程

CREATE PROCEDURE ProInsertStu    存储过程名

@Name nvarchar(20),     存储过程中的参数前要加@后要加类型,多个参数之间用逗号隔开,这些参数就是要付给(直接执行或其他应用程序调用)该存储过程的值

@Age smallint

AS                AS后面跟SQL语句,声明变量,给变量赋值,给参数赋值等

insert into student ([Name],Age) values (@Name,@Age);

执行存储过程

exec ProInsertStu '李刚',32

 

创建一个简单的有修改作用的存储过程

CREATE PROCEDURE ProUpdateStu 

@Name nvarchar(20),

@Age smallint,

@ID int    最后一个参数不用加逗号

AS

update student set [Name]=@Name,Age=@Age where ID=@ID; 

执行存储过程

exec ProUpdateStu '王刚',34,1

 

IF...ELSE IF...

CREATE PROCEDURE ProIFELSE

@Name nvarchar(20)   只有一个参数,后不用加逗号

AS

IF @Name='黄刚'

BEGIN

insert into student ([Name],Age) values ('黄刚',35);  SQL语句后要加分号

END

ELSE IF @Name='基冈'

BEGIN

insert into student ([Name],Age) values ('曼联',36);

END

ELSE IF @Name='桂刚'

BEGIN

update student set [Name]=@Name where [Name]='曼联';

END

 

声明变量及给变量赋值

CREATE PROCEDURE ProVariable 

@Name nvarchar(20)

AS                  在AS后声明及赋值变量

declare @TheAge smallint     声明变量关键字declare且变量前要加@后要加类型

set @TheAge=51    赋值变量关键字set且前要加@     声明与赋值变量后都不用加逗号

if @TheAge>40

begin

print '年龄不允许大于40'

end

else if @TheAge<40

begin

insert into student ([name],age) values (@Name,@TheAge);

end

 

 

CREATE PROCEDURE ProVariable2 

@Name nvarchar(20),

@Age int OUTPUT        这种output参数能返回其值

AS

declare @TheAge smallint

set @TheAge=(select Age from student where Name=@Name)  可以通过SQL的select语句给变量赋值

set @Age=(select Age from student where Name=@Name)  可以通过SQL的select语句给参数赋值

if @TheAge>40

begin

print '年龄不允许大于40'

end

else if @TheAge<40

begin

set @TheAge=@TheAge+1

insert into student ([name],age) values ('黑刚',@TheAge);

end

 

WHILE

CREATE PROCEDURE ProWhile 

@Name nvarchar(20)

AS

declare @TheAge smallint

set @TheAge=(select Age from student where Name=@Name)

while @TheAge>8

begin

set @TheAge=@TheAge-1

end

insert into student ([Name],Age) values (@Name,@TheAge);

 

CASE...WHEN...THEN...(ELSE)...END    这里没有写在存储过程里

SELECT

当查age字段时根据age字段的值的不同进行不同的操作,但then,else后面的数据类型要与age字段的数据类型相同

当age大于34时将其减1再显示(但数据库里的值并没有改,只是显示的是这样)...age=33时...其他的按正常显示

 

select [name],age=case when age>34 then age-1 when age=33 then age+1 else age end from student

当name字段的值为王刚时按正常显示,其余的在name字段的值前加一个'我叫'

select [name]=case when [name]='王刚' then [name] else '我叫'+[name] end,age from student

then后面跟的就是满足条件的字段的值(如果直接写字段名就是正常显示),这时只能操作值如+-等,不能用= 赋值,但可以直接将要赋的值写上,如查到name字段的值为王刚时,不让其显示,直接写null

 

select [name]=case when [name]='王刚' then null else '我叫'+[name] end,age from student

UPDATE

将年龄小于36岁的年龄改成35岁,其余的年龄加8,这次可是真改了

update student set age=case when age<36 then 35 else age+8 end

这里如果不加else,或when没有涉及到的情况,这些记录的值将被设置为null(如果字段可空)或默认值(如果字段不可空)

OUTPUT参数返回值与Return返回值

被定义为OUTPUT的参数可以向使用该存储过程的应用程序返回其值

Return int or '123'  Return返回值只能是整型或整型形式的字符串,且只能返回一个值

CREATE PROCEDURE 

@name nvarchar(10),

@id int output

AS

select * from commanager where name=@name;

--set @id=4 直接给参数赋死值

set @id=(select commanagerid from commanager where name=@name) 用select方式动态给参数赋值

return @id   这里@id是int类型,所以可以用return形式返回

--return @@rowcount    返回受影响的记录数

--return @@identity       返回最后一次插入的记录的标识号

如

USE AdventureWorks2008R2;
GO
UPDATE HumanResources.Employee 
SET JobTitle = N'Executive'
WHERE NationalIDNumber = 123456789
IF @@ROWCOUNT = 0
PRINT 'Warning: No rows were updated';
GO

@@***为SQLServer内置的全局变量,可根据具体情况选择是直接使用还是作为返回值返回

更多请见:http://msdn.microsoft.com/zh-cn/library/bb510741.aspx

 

http://qsfwy.iteye.com/blog/251256|荐

http://www.cnblogs.com/junyistar/archive/2007/07/13/817315.html

http://www.cnblogs.com/junyistar/archive/2007/06/07/775296.html

http://www.cnblogs.com/chenxizhang/archive/2009/03/19/1416217.html(输出参数)

http://www.cnblogs.com/Nina-piaoye/archive/2006/09/18/507183.html

http://www.cppblog.com/Lee7/archive/2008/06/19/53962.html

http://www.cnblogs.com/hongyin163/archive/2008/10/27/1320389.html(Case)

http://kb.cnblogs.com/page/106721/