1.判断系统中有没有此名称的存储过程
if exists (select * from sysobjects where name= 'PRocname' and sysstat & 7 =4)drop procedure procnamego
2.常用创建存储过程
Create procedure procname @pMode tinyint,@guid uniqueidentifier,@companyname nvarchar(50)='',@metel_type_id INTwith encryptionAsdeclare @tModeName nvarchar(10),@tRet int,@tCurDate date--暂时只调用 1 '增加' 这个方法select @tModeName=case @pMode when 1 then '增加' when 2 then '编辑' else '删除' endset nocount onbegin transactionif @pMode=1
begin
end
else if @pMode=2
begin
end .....
3.获取 3-18中的3与18
set @metel_size=‘3-18’
SUBSTRING(@metel_size, 0, CHARINDEX('-', @metel_size))
SUBSTRING(@metel_size, CHARINDEX('-', @metel_size)+1, len(@metel_size)-CHARINDEX('-',@metel_size))
4.游标使用
declare @id
declare cur cursor for
select @id=id from table
open cur
fetch next from cur into @id
while @@fetch_status=0
begin
fetch next from cur into @id
end
colse cur
go
5.需要查询 同时支持这两个区间的数 , 1-18 ,8-20
select * from table where a between 1 and 18 and a between 8 and 20
新闻热点
疑难解答