从存储过程例子的总结

1.

CREATE PROCEDURE sp_split

AS
declare @mg varchar(200)
declare @i int
set @mg='AA,BB,CCC'
set @i=charindex(',',@mg)
while @i>=1
begin
set @mg=substring(@mg,@i+1,len(@mg)-@i)
print(@mg)
set @i=charindex(',',@mg)

end

GO

2.

CREATE PROCEDURE sp_test --查找提取
as
declare @i int
declare @strLen int
declare @str varchar(512)
declare @str1 varchar(512)
declare @str2 varchar(512)
declare @position smallint

set @str='癌、恶性、肉瘤、淋巴瘤，排除：未见癌、不见癌'
set @strLen=LEN(@str)
set @i=1

set @position=charindex(',',@str)
print @position
set @str1=substring(@str,1,@position-1)
set @str=right(@str,(len(@str)-@position))

set @position=charindex(';',@str)
set @str2=substring(@str,1,@position-1)
set @str=right(@str,(len(@str)-@position))

print @str1
print @str2
print @str
while @i<@strLen+1
begin
print(Substring(@str,@i,1))
set @i=@i+1
end

GO

3.

/*
declare @msg as varchar(512)
exec sp_expExcle 15,'C:\Users\Administrator\Desktop\dkj\导出数据\12.xls',@msg output
select @msg
drop PROCEDURE sp_expExcle
go
*/

CREATE PROCEDURE sp_expExcle --导出excel文件
@itemid int=0,
@path VARCHAR(512)='',
@msg VARCHAR(512)='' output
AS

declare @str as varchar(8000)
set @str=''

set @str = 'bcp "select ''科室编号'' as DESKNO,''性别'' as SEX,''年龄'' as AGE ,''检查部位'' as CHECK_PART ,''影像描述'' as IMAGE_DEL ,''影像诊断'' as IMAGE_DIAGNOSE ,''登记日期'' as REGISTER_DATE ,''报告日期'' as REPOAT_DATE ,''审核日期'' as CHACK_DATE union all select DESKNO,SEX,AGE,CHECK_PART,IMAGE_DEL,IMAGE_DIAGNOSE,REGISTER_DATE,REPOAT_DATE,CHACK_DATE from Hospital.dbo.HOS_PICKUP where ITEM='+ str(@itemid)+'" queryout "' +@path+ '" -c -S127.0.0.1 -Usa -P111111'

print @str

Exec master..xp_cmdshell @str
--Exec master..xp_cmdshell 'bcp "select ''编号'' as id,''名称'' as NAME,''备注'' as REMARK union all select cast(id as varchar(8) ),NAME,REMARK from Hospital.dbo.HOS_ITEM" queryout "c:\aa.xls " -c -S127.0.0.1 -Usa -P111111 '

declare @ncount as int
set @ncount=0
select @ncount=count(*) from Hospital.dbo.HOS_PICKUP where ITEM=@itemid

set @msg = '成功导出'+cast(@ncount as varchar(30) )+'条数据。' --导出后,显示在导出excel按钮的旁边
print @msg
GO

总结：

1)将int 转换为 varchar :cast(@ncount as varchar(30) )

2)定义varchar: declare @str as varchar(8000)
int :declare @count as int

3)赋值:set @mg='AA,BB,CCC'
4)条件下：while @i>=1
begin

i++

end

5)打印输出:print @str1 .这个显示在消息里面，如果select @str1显示在结果里面

6)截取字符串:set @mg=substring(@mg,@i+1,len(@mg)-@i)

7)以特定字符分割得到特定字符串的位置 set @i=charindex(',',@mg)

8）得到字符串长度:set @strLen=LEN(@str)
9)定义存储过程参数：

CREATE PROCEDURE sp_expExcle

@itemid int=0,
@path VARCHAR(512)='',
@msg VARCHAR(512)='' output

（两个为输入参数，一个为输出参数）

定义两个输入参数，没有输出参数则：

CREATE PROCEDURE sp_expExcle

@itemid int=0,
@path VARCHAR(512)=''

定义输出参数为

CREATE PROCEDURE sp_expExcle

@msg VARCHAR(512)='' output

10）调用存储过程

exec sp_expExcle 15,'C:\Users\Administrator\Desktop\dkj\导出数据\12.xls',''

exec sp_expExcle 15,'C:\Users\Administrator\Desktop\dkj\导出数据\12.xls'

declare @msg

exec sp_expExcle @msg output
select @msg

11)删除存储过程

drop PROCEDURE sp_expExcle
go
12）执行sql

declare @sql as varchar(100)

set @sql='select * from table'

Exec @sql

13)sql传参数
set @ncount=0
select @ncount=count(*) from Hospital.dbo.HOS_PICKUP where ITEM=@itemid