创建存储过程时判断是否存在问题
伱似渥嘚 2016-04-05 05:41:34 在创建存储过程时,在SQL上写判断是否存在该注意哪些问题?现在有一个存储过程写法如下,在microsoft SQL Server Management Studio 2008 R2版本执行可以正常执行,用程序执行该语句时会出现需定义变量问题。去除if not exists (select * from sys.procedures where object_id = OBJECT_ID(N'[dbo].[Excel_Inport_Store]')) begin ..... end之后可在程序上执行,但这样不能判断存储过程是否已存在,不符合需求。
PS:需求是通过程序执行批量创建存储过程,不通过工具或其他方式创建。
if exists (select * from sys.procedures where object_id = OBJECT_ID(N'[dbo].[Excel_Inport_Store]'))
drop proc [dbo].Excel_Inport_Store
go
create proc [dbo].Excel_Inport_Store(
@FID varchar(36),
@name nvarchar(400) output,
@code varchar(36),
@typecode varchar(36),
@proname char(6),
@cityname char(6),
@addr nvarchar(400),
@dpname nvarchar(100) output,
@realname nvarchar(100) output,
@telphone nvarchar(36),
--@manager varchar(36),
@mimo nvarchar(255),
--@pid varchar(36),
@fmem varchar(36),
@modfmem varchar(36),
@addtime datetime,
@updatetime datetime,
@stat smallint,
--@dydata nvarchar(255),
@flow int,
@flowpos int
)
as
declare
@unicode varchar(36)=(select FID from ST_Store where CODE=@code and stat=1),
@sttype varchar(36)=(select name from TB_DIC where CODE=@typecode and TYPEID='20'),
@procode char(6)=(select procode from DM_Province where proname=@proname),
@citycode char(6)=(select a.citycode from DM_City a left join DM_Province b on a.procode=b.procode where b.proname=@proname and a.cityname=@cityname),
@dpartid varchar(36)=(select departid from TB_DEPARTINFO where departname=@dpname and stat=1),
@managercap varchar(36)=(select a.USERID from TB_USERINFO a left join BD_USER_DEPART b on a.USERID=b.USERID left join TB_DEPARTINFO c on b.DEPARTID=c.DEPARTID where a.REALNAME=@realname and a.STAT=1 and c.DEPARTNAME=@dpname),
@tel nvarchar(36)=(select PHONENUM from TB_USERINFO where REALNAME=@realname),
@manager varchar(36),
@result smallint
--set @manager=@managercap
set @result=
case
when @name='' then -1 --未输入仓库名称
when @code='' then -2 --未输入仓库编号
when @code!='' and @unicode!='' then -3 --已输入仓库编号但数据表已存在相同编号
when @typecode!='' and (@sttype=null or @sttype='') then -4 --已输入仓库类型信息但与数据表数据不匹配
when @proname!='' and (@procode=null or @procode='') then -5 --已输入省份但与数据表数据不匹配
when @cityname!='' and (@citycode=null or @citycode='') then -6 --已输入城市但与数据表数据不匹配
when @dpname!='' and (@dpartid=null or @dpartid='') then -7 --已输入部门但与数据表数据不匹配
when @realname!='' and (@managercap=null or @managercap='') then -8 --已输入仓库管理员但与数据表数据不匹配
when @realname!='' and (@tel=null or @tel='') then -9 --已输入管理员电话但与数据表数据不匹配
else 1
end
if (@result!=1)
begin
return @result
end
else
begin
set @manager=@managercap
insert into ST_Store(FID,name,code,typecode,procode,citycode,addr,manager,bumen,telphone,mimo,pid,fmem,modfmem,addtime,updatetime,stat,dydata,flow,flowpos)values(@FID,@name,@code,@typecode,@procode,@citycode,@addr,@manager,@dpname,@telphone,@mimo,'',@fmem,@modfmem,@addtime,@updatetime,@stat,'',@flow,@flowpos)
return @result
end