sql server存储过程

--http://www.cnblogs.com/hoojo/archive/2011/07/19/2110862.html
--http://www.cnblogs.com/selene/p/4483612.html
--学习记录   20160118
use Test2;
--创建测试books表
create table books (
    book_id int identity(1,1) primary key,
    book_name varchar(20),
    book_price float,
    book_auth varchar(10)
);
--插入测试数据
insert into books (book_name,book_price,book_auth)
                    values
                        ('红楼梦',254,'曹雪芹');
insert into books (book_name,book_price,book_auth)
                    values
                        ('水浒传',246,'施耐庵');
insert into books (book_name,book_price,book_auth)
                    values
                        ('三国演义',327,'罗贯中');
insert into books (book_name,book_price,book_auth)
                    values
                        ('西游记',358,'吴承恩');
insert into books (book_name,book_price,book_auth)
                    values('孙子兵法',448,'孙武');

--1.创建无参存储过程
if (exists (select * from sys.objects where name = 'getAllBooks'))
    drop proc proc_get_student
go
create procedure getAllBooks
as
select * from books;
--调用存储过程
execute getAllBooks
--重命名存储过程
sp_rename getAllBooks ,getBooks;

--2 存储过程 一个参数
if (exists (select * from sys.objects where name = 'searchBooks'))
    drop proc searchBooks
go
create proc searchBooks(@bookID int)
as
    --要求book_id列与输入参数相等
    select * from books where book_id=@bookID;
go
--执行searchBooks
execute searchBooks 5;

--3带两个参数的存储过程
if (exists (select * from sys.objects where name = 'searchBooks1'))
    drop proc searchBooks1
go
create proc searchBooks1(
    @bookID int,
    @bookAuth varchar(20)
)
as
    --要求book_id和book_Auth列与输入参数相等
    select * from books where book_id=@bookID and book_auth=@bookAuth;
go
exec searchBooks1 9,'罗贯中';

--4 创建有返回值的存储过程

if (exists (select * from sys.objects where name = 'getBookId'))
    drop proc getBookId
go
create proc getBookId(
    @bookAuth varchar(20),--输入参数,无默认值
    @bookId int output --输入/输出参数 无默认值
)
as
    select @bookId=book_id from books where book_auth=@bookAuth;
--执行getBookId这个带返回值的存储过程
declare @id int --声明一个变量用来接收执行存储过程后的返回值
exec getBookId '孔子',@id output;
select @id as bookId;--as是给返回的列值起一个名字

--5 创建带通配符的存储过程 %
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    '孔%','论%';
exec  charBooks ;
exec sp_helptext 'charBooks';--查看存储过程的创建

--6 加密存储过程
if (object_id('books_encryption', 'P') is not null)
    drop proc books_encryption
go
create proc books_encryption 
with encryption --加密
as 
    select * from books;
--执行此过程books_encryption
exec books_encryption;
exec sp_helptext 'books_encryption';--控制台会显示"对象 'books_encryption' 的文本已加密。"

--7 不缓存存错过程
if (object_id('book_temp', 'P') is not null)
    drop proc book_temp
go
create proc book_temp
with recompile
as
    select * from books;
go

exec book_temp;
exec sp_helptext 'book_temp';

</pre><pre code_snippet_id="1559067" snippet_file_name="blog_20160118_1_2065868" name="code" class="sql"><pre name="code" class="sql">---------------动态sql---sqlserver -----------------------------------------------------------------------------------------
select top 5*from t_user

--方法1查询表改为动态
select * from t_user
exec('select name,dept,address from t_user')
exec sp_executesql N'select name,dept,address from t_user'--多了一个N为unicode

--方法2:字段名，表名，数据库名之类作为变量时,用动态SQL 
--字段名
declare @FName varchar(20)
set @FName='dept'
exec('select name,address,'+@FName+' from t_user where '+@FName+'=5' )

--表名
declare @FName varchar(20)
set @FName='t_user'
declare @sql nvarchar(1000)--为nvarchar
set @sql=N'select * from '+@FName
exec sp_executesql @sql

--数据库名
declare @FName varchar(20)
set @FName='[Test2]'
declare @sql nvarchar(1000)--为nvarchar
set @sql=N'select * from '+@FName+'.[dbo].[fruits]'
exec sp_executesql @sql


--方法3:输入参数
declare @i int,@s nvarchar(1000)
set @i=5
exec('select name,address from t_user where dept='+@i)
----输入参数
declare @i int,@sql nvarchar(1000)
set @sql='select name,address from t_user where dept=@i'
exec sp_executesql @sql,N'@i int',@i--此处输入参数要加上N

--方法4:输出参数
declare @i int,@sql nvarchar(1000)
set @sql='select @i=count(1) from t_user'

--用exec
-- exec('declare @i int '+@sql+' select @i')--把整个语句用字符串加起来执行

--用sp_executesql
exec sp_executesql @sql,N'@i int output',@i output--此处输出参数要加上N 输出参数 output
select @i  as '列数' --查看输出参数



--方法5:输入输出

--用sp_executesql
declare @i int,@con int,@sql nvarchar(1000)
set @i=5
select @sql='select @con=count(1) from t_user where userId>@i'
exec sp_executesql @sql,N'@con int output,@i int',@con output ,@i 
select @con '列数'

--用exec
declare @i int,@sql nvarchar(1000)
set @i=5
select @sql='declare @con int select @con=count(1) from t_user where userId>'+rtrim(@i)+' select @con'
exec(@sql)
---------------------

<pre name="code" class="sql">---动态sql 一个参数  表名作为参数
alter proc proc_list30(@table varchar(20),@userId int ) 
as	
	begin
		declare @dept int
		set @dept=1
		declare @sql nvarchar(1000)--为nvarchar
		set @sql=N'select * from '+QUOTENAME(@table)+'where dept>'+cast(@dept as             varchar(20))+' and	userId>@userId'
		exec sp_executesql @sql,N'@userId int output',@userId
	end
--传入参数 表变量table  条件变量 userId   dept为局部变量

exec proc_list30 't_user','105'