if exists(select 1 from sysobjects where id=object_id('所有者.存储过程名') and xtype='P')
print '存在'
else
print '不存在'
print '存在'
else
print '不存在'
解决方案 »
- 如何批量判断是否存在呀? 求助 泪奔~~o(>_<)o ~~
- 三种条件写在同一个存储过程,如何写?case?switch?
- 大家帮我看看这个sql怎么写
- 如何进入sql 桌面版的查询分析器?
- 【新手初到】今天刚到一个公司上班,经理给了个sql语句查询的问题,要考验我 急!如果3天解决不出来就走人~~~~~~~~~~~~
- 如何使SQL管理下面的“SQL Server代理”随开机自动启动???
- 在SELECT语句中,将在查询出来的结果增加一个字段并创建为一个新表,怎么实现?
- 请问字段类型为DateTime的数据类型,怎么进行统计?
- 远程访问数据库问题!!
- 请教一个数据库安全设置问题,需要帮助
- sql重复记录查找问题,请高手进(在线等..解决就结贴)
- 错误:17803,可用内存不够!
if exists(select 1 from sysobjects where id=object_id('所有者.存储过程名') and xtype='P')
begin
exec('drop procedure 所有者.存储过程名')
end
--******************************************************************************************************************************
if exists(select 1 from sysobjects where id=object_id('saiken.KJ_Tokuisaki_Koujibetu_DL_Proc') and xtype='P')
begin
exec('drop procedure saiken.KJ_Tokuisaki_Koujibetu_DL_Proc')
end
--*******************************************************************************************************************************
CREATE Procedure KJ_Tokuisaki_Koujibetu_DL_Proc
(
@Condition nvarchar(1000)
)
ASDECLARE @ExecStr varchar(4000)Select @ExecStr=" SELECT B1.*"
Select @ExecStr=@ExecStr+ ",convert(float,isnull(B1.B1_KAISHU_GIMUDAKA,0))+convert(float,isnull(B1.B1_JIGETUIKOU_KAISHU_YOTEI,0)) AS B1_KONKAI_SEIKYU"
Select @ExecStr=@ExecStr+ " FROM saiken.B1_KAISHU_TOKUISAKI AS B1"
Select @ExecStr=@ExecStr+ " WHERE "
Select @ExecStr=@ExecStr+ " B1.B1_DATA_KAKUTEI_KBN = '1' "
Select @ExecStr=@ExecStr+ " AND B1.B1_SEIKYU_YYYYMM IN (SELECT M7_YYYYMM FROM saiken.M7_HANYO2 WHERE "
Select @ExecStr=@ExecStr+ " M7_CODE = '02' OR M7_CODE = '03')"
Select @ExecStr=@ExecStr+ @Condition
Select @ExecStr=@ExecStr+" ORDER BY B1.B1_SEIKYU_YYYYMM, B1.B1_NAYOSESAKI, B1.B1_TOKUISAKI "EXECUTE(@ExecStr)
如上的存储过程,先判断它是否存在,然后再建存储过程。在sql 7.0 下执行,提示'CREATE PROCEDURE' must be the first statement in a query batch.'
如何更改,谢谢!
begin
exec('drop procedure saiken.KJ_Tokuisaki_Koujibetu_DL_Proc')
endgo --加GOCREATE Procedure KJ_Tokuisaki_Koujibetu_DL_Proc
(
@Condition nvarchar(1000)
)
ASDECLARE @ExecStr varchar(4000)Select @ExecStr=" SELECT B1.*"
Select @ExecStr=@ExecStr+ ",convert(float,isnull(B1.B1_KAISHU_GIMUDAKA,0))+convert(float,isnull(B1.B1_JIGETUIKOU_KAISHU_YOTEI,0)) AS B1_KONKAI_SEIKYU"
Select @ExecStr=@ExecStr+ " FROM saiken.B1_KAISHU_TOKUISAKI AS B1"
Select @ExecStr=@ExecStr+ " WHERE "
Select @ExecStr=@ExecStr+ " B1.B1_DATA_KAKUTEI_KBN = '1' "
Select @ExecStr=@ExecStr+ " AND B1.B1_SEIKYU_YYYYMM IN (SELECT M7_YYYYMM FROM saiken.M7_HANYO2 WHERE "
Select @ExecStr=@ExecStr+ " M7_CODE = '02' OR M7_CODE = '03')"
Select @ExecStr=@ExecStr+ @Condition
Select @ExecStr=@ExecStr+" ORDER BY B1.B1_SEIKYU_YYYYMM, B1.B1_NAYOSESAKI, B1.B1_TOKUISAKI "EXECUTE(@ExecStr)
begin
exec('drop procedure saiken.KJ_Tokuisaki_Koujibetu_DL_Proc')
end
go
---此处加 go
--*******************************************************************************************************************************
CREATE Procedure KJ_Tokuisaki_Koujibetu_DL_Proc
(
@Condition nvarchar(1000)
)
ASDECLARE @ExecStr varchar(4000)Select @ExecStr=" SELECT B1.*"
Select @ExecStr=@ExecStr+ ",convert(float,isnull(B1.B1_KAISHU_GIMUDAKA,0))+convert(float,isnull(B1.B1_JIGETUIKOU_KAISHU_YOTEI,0)) AS B1_KONKAI_SEIKYU"
Select @ExecStr=@ExecStr+ " FROM saiken.B1_KAISHU_TOKUISAKI AS B1"
Select @ExecStr=@ExecStr+ " WHERE "
Select @ExecStr=@ExecStr+ " B1.B1_DATA_KAKUTEI_KBN = '1' "
Select @ExecStr=@ExecStr+ " AND B1.B1_SEIKYU_YYYYMM IN (SELECT M7_YYYYMM FROM saiken.M7_HANYO2 WHERE "
Select @ExecStr=@ExecStr+ " M7_CODE = '02' OR M7_CODE = '03')"
Select @ExecStr=@ExecStr+ @Condition
Select @ExecStr=@ExecStr+" ORDER BY B1.B1_SEIKYU_YYYYMM, B1.B1_NAYOSESAKI, B1.B1_TOKUISAKI "EXECUTE(@ExecStr)