存储过程
来源:互联网 发布:telnet开启端口命令 编辑:程序博客网 时间:2024/05/29 15:04
//exist函数的使用
declare @xm varchar(20)
declare @result int
set @xm='龚志辉'
exec isExist @xm,@result output
print @result //返回1
create proc [dbo].[isExist]
@xm varchar(20),@result int output
as
begin
if exists(select * from chengji where xm=@xm)
set @result=1
else
set @result=0
end
//计算两数值的和,out型返回
declare @a int,@b int,@c int
set @a=10
set @b=20
exec PR_Sum @a,@b,@c output
print @c
create proc PR_Sum
@a int,
@b int,
@c int out
as
begin
set @c= @a+@b
end
//计算两数值的和,return返回
declare @a int,@b int,@c int
set @a=10
set @b=20
exec @c=PR_Sum @a,@b
print @c
create proc PR_Sum
@a int,
@b int
as
begin
return @a+@b
end
//执行动态sql语句
declare @sql nvarchar(2000)
declare @cou int
declare @id varchar(20)
set @sql='select @count=count(*) from chengji where id>@id'
set @id='1'
exec sp_executesql @sql,N'@count int out,@id varchar(20)',@cou out,@id
print @cou
SQLSERVER:
变量的声明:声明变量时必须在变量前加@符号
DECLARE @I INT
变量的赋值:
变量赋值时变量前必须加set
SET @I = 30
声明多个变量:
DECLARE @s varchar(10),@a INT
if语句:
- if ..
- begin
- ...
- end
- else if ..
- begin
- ...
- end
- else
- begin
- ...
- end
Example:
- DECLARE @d INT
- set @d = 1
- IF @d = 1 BEGIN
- PRINT '正确'
- END
- ELSE BEGIN
- PRINT '错误'
- END
多条件选择语句:
Example:
- 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
循环语句:
- WHILE 条件 BEGIN
- 执行语句
- END
Example:
- DECLARE @i INT
- SET @i = 1
- WHILE @i<1000000 BEGIN
- set @i=@i+1
- END
定义游标:
- DECLARE @cur1 CURSOR FOR SELECT .........
- OPEN @cur1
- FETCH NEXT FROM @cur1 INTO 变量
- WHILE(@@FETCH_STATUS=0)
- BEGIN
- 处理.....
- FETCH NEXT FROM @cur1 INTO 变量
- END
- CLOSE @cur1
- DEALLOCATE @cur1
- AS
- declare @CATEGORY_CI_TABLENAME VARCHAR(50) =''
- declare @result VARCHAR(2000) = ''
- declare @CI_ID DECIMAL = 0
- declare @num int = 1
- declare @countnum int = 1
- BEGIN
- select @countnum = count(ATTRIBUTE_CONFIG_ID) from T_ATTRIBUTE_CONFIG where CMDB_UPDATE_FLAG= 'Y' and CATEGORY_CODE =@CATEGORY_CODE
- IF (@ATTRIBUTE2='A')
- begin
- DECLARE MyCursor CURSOR for select ATTRIBUTE_CONFIG_CODE from T_ATTRIBUTE_CONFIG where CMDB_UPDATE_FLAG= 'Y' and CATEGORY_CODE =@CATEGORY_CODE
- OPEN MyCursor FETCH NEXT FROM MyCursor INTO @CONFIG_CODE
- set @result = @result+@CONFIG_CODE+','
- WHILE @@FETCH_STATUS = 0
- BEGIN
- FETCH NEXT FROM MyCursor INTO @CONFIG_CODE
- set @num = @num+ 1
- if(@num<@countnum)
- begin
- set @result = @result+@CONFIG_CODE+','
- end
- else if(@num=@countnum)
- begin
- set @result = @result +@CONFIG_CODE
- end
- END
- CLOSE MyCursor
- DEALLOCATE MyCursor
- set @result = 'insert into ' + @ATTRIBUTE1 + '(' + @result +') select '+ @result +' from '+@CATEGORY_CI_TABLENAME +' where CI_ORDER_LINE_ID='+@KEY_ID
- end
- else if((@ATTRIBUTE2='U'))
sp_executesql介绍和使用:http://www.cnblogs.com/wanyuan8/archive/2011/11/09/2243483.html
- 存储过程
- 存储过程
- 存储过程
- 存储过程
- 存储过程
- 存储过程
- 存储过程
- 存储过程
- 存储过程
- 存储过程
- 存储过程
- 存储过程
- 存储过程
- 存储过程
- 存储过程
- 存储过程
- 存储过程
- 存储过程
- linux kernel文件系统
- 程序员招聘 -- 实力才是一切
- ctags 使用
- 99. Recover Binary Search Tree
- 内外层ViewPager嵌套[内层ViewPager不加载问题],设置DrawableTop不起效
- 存储过程
- RDD内幕解密
- python使用matplotlib画图
- 如何使用命令行生成高强度密码
- 企业级用户管理系统数据融合方案
- 编写自定义控件构造函数的正确姿势 - defStyleAttr/defStyleRes
- Android 代码技巧
- handler机制
- Linux Kernel 4.7.2版本发布!