SQL Server 2008 存储过程示例
程序员文章站
2023-02-19 23:40:02
--有输入参数的存储过程--
create proc getcomment
(@commentid int)
as
select * from com...
--有输入参数的存储过程-- create proc getcomment (@commentid int) as select * from comment where commentid=@commentid --有输入与输出参数的存储过程-- create proc getcommentcount @newsid int, @count int output as select @count=count(*) from comment where newsid=@newsid --返回单个值的函数-- create function myfunction (@newsid int) returns int as begin declare @count int select @count=count(*) from comment where newsid=@newsid return @count end --调用方法-- declare @count int exec @count=myfunction 2 print @count --返回值为表的函数-- create function getfunctiontable (@newsid int) returns table as return (select * from comment where newsid=@newsid) --返回值为表的函数的调用-- select * from getfunctiontable(2)
sqlserver 存储过程中不拼接sql字符串实现多条件查询
--以前拼接的写法 set @sql=' select * from table where 1=1 ' if (@adddate is not null) set @sql = @sql+' and adddate = '+ @adddate + ' ' if (@name <>'' and is not null) set @sql = @sql+ ' and name = ' + @name + ' ' exec(@sql)
下面是 不采用拼接sql字符串实现多条件查询的解决方案
--第一种写法是 感觉代码有些冗余 if (@adddate is not null) and (@name <> '') select * from table where adddate = @adddate and name = @name else if (@adddate is not null) and (@name ='') select * from table where adddate = @adddate else if(@adddate is null) and (@name <> '') select * from table where and name = @name else if(@adddate is null) and (@name = '') select * from table --第二种写法是 select * from table where (adddate = @adddate or @adddate is null) and (name = @name or @name = '') --第三种写法是 select * from table where adddate = case @adddate is null then adddate else @adddate end, name = case @name when '' then name else @name end
sqlserver存储过程基本语法
一、定义变量
--简单赋值 declare @a int set @a=5 print @a --使用select语句赋值 declare @user1 nvarchar(50) select @user1= '张三' print @user1 declare @user2 nvarchar(50) select @user2 = name from st_user where id=1 print @user2 --使用update语句赋值 declare @user3 nvarchar(50) update st_user set @user3 = name where id=1 print @user3
二、表、临时表、表变量
--创建临时表1 create table #du_user1 ( [id] [ int ] not null , [oid] [ int ] not null , [login] [nvarchar](50) not null , [rtx] [nvarchar](4) not null , [ name ] [nvarchar](5) not null , [ password ] [nvarchar]( max ) null , [state] [nvarchar](8) not null ); --向临时表1插入一条记录 insert into #du_user1 (id,oid,[login],rtx, name ,[ password ],state) values (100,2, 'ls' , '0000' , '临时' , '321' , '特殊' ); --从st_user查询数据,填充至新生成的临时表 select * into #du_user2 from st_user where id<8 --查询并联合两临时表 select * from #du_user2 where id<3 union select * from #du_user1 --删除两临时表 drop table #du_user1 drop table #du_user2 --创建临时表 create table #t ( [id] [ int ] not null , [oid] [ int ] not null , [login] [nvarchar](50) not null , [rtx] [nvarchar](4) not null , [ name ] [nvarchar](5) not null , [ password ] [nvarchar]( max ) null , [state] [nvarchar](8) not null , ) --将查询结果集(多条数据)插入临时表 insert into #t select * from st_user --不能这样插入 --select * into #t from dbo.st_user --添加一列,为int型自增长子段 alter table #t add [myid] int not null identity(1,1) --添加一列,默认填充全球唯一标识 alter table #t add [myid1] uniqueidentifier not null default (newid()) select * from #t drop table #t --给查询结果集增加自增长列 --无主键时: select identity( int ,1,1) as id, name ,[login],[ password ] into #t from st_user select * from #t --有主键时: select ( select sum (1) from st_user where id<= a.id) as myid,* from st_user a order by myid --定义表变量 declare @t table ( id int not null , msg nvarchar(50) null ) insert into @t values (1, '1' ) insert into @t values (2, '2' ) select * from @t
三、循环
--while循环计算1到100的和 declare @a int declare @ sum int set @a=1 set @ sum =0 while @a<=100 begin set @ sum +=@a set @a+=1 end print @ sum
四、条件语句
--if,else条件分支 if(1+1=2) begin print '对' end else begin print '错' end --when then条件分支 declare @today int declare @week nvarchar(3) set @today=3 set @week= case when @today=1 then '星期一' when @today=2 then '星期二' when @today=3 then '星期三' when @today=4 then '星期四' when @today=5 then '星期五' when @today=6 then '星期六' when @today=7 then '星期日' else '值错误' end print @week
五、游标
declare @id int declare @oid int declare @login varchar (50) --定义一个游标 declare user_cur cursor for select id,oid,[login] from st_user --打开游标 open user_cur while @@fetch_status=0 begin --读取游标 fetch next from user_cur into @id,@oid,@login print @id --print @login end close user_cur --摧毁游标 deallocate user_cur
五、游标
declare @id int declare @oid int declare @login varchar (50) --定义一个游标 declare user_cur cursor for select id,oid,[login] from st_user --打开游标 open user_cur while @@fetch_status=0 begin --读取游标 fetch next from user_cur into @id,@oid,@login print @id --print @login end close user_cur --摧毁游标 deallocate user_cur
六、触发器
触发器中的临时表:
inserted
存放进行insert和update 操作后的数据
deleted
存放进行delete 和update操作前的数据
--创建触发器 create trigger user_onupdate on st_user for update as declare @msg nvarchar(50) --@msg记录修改情况 select @msg = n '姓名从“' + deleted. name + n '”修改为“' + inserted. name + '”' from inserted,deleted --插入日志表 insert into [log](msg) values (@msg) --删除触发器 drop trigger user_onupdate
七、存储过程
--创建带output参数的存储过程 create procedure pr_sum @a int , @b int , @ sum int output as begin set @ sum =@a+@b end --创建return返回值存储过程 create procedure pr_sum2 @a int , @b int as begin return @a+@b end --执行存储过程获取output型返回值 declare @mysum int execute pr_sum 1,2,@mysum output print @mysum --执行存储过程获取return型返回值 declare @mysum2 int execute @mysum2= pr_sum2 1,2 print @mysum2
八、自定义函数
函数的分类:
1)标量值函数
2)表值函数
a:内联表值函数
b:多语句表值函数
3)系统函数
--新建标量值函数 create function func_sum1 ( @a int , @b int ) returns int as begin return @a+@b end --新建内联表值函数 create function func_usertab_1 ( @myid int ) returns table as return ( select * from st_user where id<@myid) --新建多语句表值函数 create function func_usertab_2 ( @myid int ) returns @t table ( [id] [ int ] not null , [oid] [ int ] not null , [login] [nvarchar](50) not null , [rtx] [nvarchar](4) not null , [ name ] [nvarchar](5) not null , [ password ] [nvarchar]( max ) null , [state] [nvarchar](8) not null ) as begin insert into @t select * from st_user where id<@myid return end --调用表值函数 select * from dbo.func_usertab_1(15) --调用标量值函数 declare @s int set @s=dbo.func_sum1(100,50) print @s --删除标量值函数 drop function func_sum1
推荐阅读
-
SQL Server数据库的存储机制讲解
-
sql server 2008 压缩备份数据库(20g)
-
在sqlserver2005中安装sql server 2000的示例数据库northwind的方法
-
sql2005数据导出方法(使用存储过程导出数据为脚本)
-
如何在SQL SERVER 2005存储过程中,使用循环语句
-
SQL Server 2005通用分页存储过程及多表联接应用
-
SQL Server 2005/2008 导入导出数据常见报错解决方法
-
SQL Server 2008服务器角色和数据库角色
-
如何在SQL Server 2005数据库中导入SQL Server 2008的数据
-
sqlserver2005利用临时表和@@RowCount提高分页查询存储过程性能示例分享