6、MSSQL存储过程
http://www.cnblogs.com/hoojo/archive/2011/07/19/2110862.html
语法
create proc | procedure pro_name
[{@参数数据类型} [=默认值] [output],
{@参数数据类型} [=默认值] [output],
....
]
as
SQL_statements
简单的存储过程
--创建存储过程
if (exists (select * from sys.objects where name = 'proc_get_datatype'))
drop proc proc_get_datatype
go
create proc proc_get_datatype
as
select * from dbo.DataType;
--调用、执行存储过程
exec proc_get_datatype;
--查询所有存储过程
select * from sys.objects where type = 'P';
sys.objects中name是存储过程名称，object_id 存储过程的id，type=P 自定义函数type=F 
带输入输出参数的过程
--创建存储过程
if (exists (select * from sys.objects where name = 'proc_ouput'))
drop proc proc_ouput
go
create proc proc_ouput
(
@name VARCHAR(20) ='' OUT,--输出参数,默认值为空
@age INT =0 OUTPUT --输入输出参数，默认值0
)
as
select @age = @age+28,@name=@name+'666';
go
--调用、执行存储过程
declare @age int,@name varchar(20);
SELECT @age=20,@name='qing' ; --设置值
exec proc_ouput @name OUT,@age OUTPUT; --调用存储过程
SELECT @age AS 'age',@name AS 'name' --查询返回值
拼接SQL执行
sp_executesql
DECLARE @SQL NVARCHAR(1000)
SELECT @SQL = 'SELECT * FROM dbo.DataType'
EXECUTE sys.sp_executesql @SQL
--EXECUTE(@SQL) --没参数的话用这个更方便
带输入输出参数
DECLARE @SQL NVARCHAR(1000),@OldIdNewTableNumber INT,@OldIdUpdateTimes INT,@OldTableNumber INT
SELECT @OldTableNumber=4
SELECT @SQL=N'SELECT @OldIdNewTableNumber=NewTableNumber,@OldIdUpdateTimes=UpdateTimes FROM TB_Form WHERE Id='+CONVERT(VARCHAR(100),@OldTableNumber)
EXECUTE sys.sp_executesql @SQL,N'@OldIdNewTableNumber INT OUTPUT,@OldIdUpdateTimes INT OUTPUT',@OldIdNewTableNumber OUTPUT,@OldIdUpdateTimes OUTPUT
SELECT @OldIdNewTableNumber,@OldIdUpdateTimes
存储过程模板
IF OBJECT_ID('存储过程名','P') IS NOT NULL
BEGIN
DROP PROCEDURE 存储过程名
END
GO
CREATE PROCEDURE 存储过程名 (with encryption 可选加密)
AS
BEGIN
BEGIN TRANSACTION;
BEGIN TRY;
--DECLARE @SQL NVARCHAR(4000)
-- SET @SQL=' INSERT INTO dbo.T_Test(LogDate) VALUES(GETDATE())'
-- EXECUTE(@SQL)
-- SET @SQL=' INSERT INTO dbo.T_Test(LogDate) VALUES(GETDATE())'
-- EXECUTE(@SQL)
COMMIT;
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT @ErrorMessage = ERROR_MESSAGE(),@ErrorSeverity = ERROR_SEVERITY(),@ErrorState = ERROR_STATE();
RAISERROR(@ErrorMessage,@ErrorSeverity,@ErrorState);
ROLLBACK TRANSACTION
END CATCH
END
GO
处理xml参数
sp_xml_preparedocument hdoc(INT) OUTPUT
[, xmltext]
[, xpath_namespaces]
DECLARE @Xml VARCHAR(1000);
DECLARE @Pointer INT;
SET @Xml='<body><Student name="qing" /></body>'
EXECUTE sp_xml_preparedocument @Pointer OUTPUT,@Xml;
IF OBJECT_ID('tempdb..#Tem') IS NOT NULL
DROP TABLE #Tem
create table #Tem
(
Name varchar(30)
)
;
insert into #Tem
(
Name
)
select name
FROM
OPENXML(@Pointer, '/body/Student')
with
(
name varchar(30)
)
;
EXECUTE sp_xml_removedocument @Pointer;
SELECT * FROM #Tem
其他例子
--创建数据库
create table books(
book_id int identity(1,1) primary key,
book_name varchar(20),
book_price float,
book_auth varchar(10)
);
--
select * from books
--
--插入测试数据
insert into books(book_name,book_price,book_auth) values('论语2',25.6,'孔子')
insert into books(book_name,book_price,book_auth) values('天龙八部',25.6,'金庸')
insert into books(book_name,book_price,book_auth) values('雪山飞狐',32.7,'金庸')
insert into books(book_name,book_price,book_auth) values('平凡的世界',35.8,'路遥')
insert into books(book_name,book_price,book_auth) values('史记',54.8,'司马迁');
----------创建无参数存储过程 ----------
if(exists(select * from sys.objects where name='getAllBooks'))
drop proc getAllBooks --删除
GO
create procedure getAllBooks
AS
select * from books
-- ----------执行 ----------
EXEC getAllBooks
-- ----------修改 ----------
alter proc getAllBooks
as
select book_name from books
-- ----------删除 ----------
drop proc getAllBooks
--(1)带一个参数的存储过程
if(exists(select * from sys.objects where name='serchBooks'))
drop proc serchBooks
go
create proc serchBooks(@bookID int)
as
select * from books where book_id=@bookID;
--执行带参数
exec serchBooks 1;
--(2)两个参数
if(exists(select * from sys.objects where name='serchBooks1'))
drop proc serchBooks1
go
create proc serchBooks1(@bookID int,@book_Auth varchar(20))
as
select * from books where book_id=@bookID and book_auth=@book_Auth;
--执行两个参数 select * from books
exec serchBooks1 2,'金庸';
--(3)创建有返回值的存储过程
if(exists(select * from sys.objects where name='getBookId'))
drop proc getBookId
go
--@bookAuth输入参数 无默认值
--@bookId输入输出参数 无默认值
create proc getBookId(@bookAuth varchar(20),@bookId int output)
as
select @bookId=book_id from books where book_auth=@bookAuth
--测试执行 update books set book_auth='孔子' where book_id=1
declare @id int
exec getBookId '孔子',@id output --声明变量接收存储过程返回的值
select @id as bookId
--(4)创建带通配符的存储过程
if (exists (select * from sys.objects where name = 'charBooks'))
drop proc charBooks
go
create proc charBooks(
@bookAuth varchar(20)='金%',
@bookName varchar(20)='%'
)
as
select * from books where book_auth like @bookAuth and book_name like @bookName;
--执行存储过程charBooks
exec charBooks '金%','%';
--(5)加密存储过程
if(object_id('books_encryption','p') is not null)
drop proc books_encryption
go
create proc books_encryption
with encryption
as
select * from books
--执行
exec books_encryption
--执行下面的语句显示;对象 'books_encryption' 的文本已加密。
exec sp_helptext 'books_encryption'
--(6)不缓存存储过程
if(object_id('books_temp','p') is not null)
drop proc books_temp
go
create proc books_temp
with recompile
as
select * from books
--执行
exec books_temp
exec sp_helptext 'books_temp'
--(7)创建带游标参数的存储过程
if (object_id('book_cursor', 'P') is not null)
drop proc book_cursor
go
create proc book_cursor
@bookCursor cursor varying output
as
set @bookCursor=cursor forward_only static for
select book_id,book_name,book_auth from books
open @bookCursor;
go
--调用book_cursor存储过程
declare @cur cursor,
@bookID int,
@bookName varchar(20),
@bookAuth varchar(20);
exec book_cursor @bookCursor=@cur output;
fetch next from @cur into @bookID,@bookName,@bookAuth;
while(@@FETCH_STATUS=0)
begin
fetch next from @cur into @bookID,@bookName,@bookAuth;
print 'bookID:'+convert(varchar,@bookID)+' , bookName: '+ @bookName +' ,bookAuth: '+@bookAuth;
end
close @cur --关闭游标
DEALLOCATE @cur; --释放游标
浙公网安备 33010602011771号