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