如何在SQL Server2008中执行存储过程 [英] How to execute stored procedure in SQL Server2008
问题描述
亲爱的所有人,
我在SQLServer2008中创建了StoredProcedure
创建 过程 tbl_Master_InsertOrUpdate( @ MasterID nvarchar ( 20 ), @ Category_Name nvarchar ( 20 ), @ Category_Image image ,< span class =code-sdkkeyword> @ Operating_Hours time , @ NonOperating_Hours 时间)
作为
开始
如果 不 存在 ( Selec t MasterID 来自 tbl_Master 其中 MasterID = @ MasterID)
将 插入 tbl_Master(MasterID,Category_Name,Category_Image,Operating_Hours,NonOperating_Hours) values ( @ MasterID , @ Category_Name , @ Category_Image , @ Operating_Hours , @ NonOperating_Hours )
其他
更新 tbl_Master 设置 Category_Name = @ Category_Name,Category_Image = @ Category_Image ,Operating_Hours = @ Operating_Hours,NonOperating_Hours = @ NonOperating_Hours 其中 MasterID = @ MasterID
< span class =code-key单词>结束
我编译了这个&我正在尝试按如下方式执行此过程
Exec tbl_Master_InsertOrUpdate 1三明治null null null
但我收到以下错误
附近的语法不正确关键字null
但声明似乎是正确的..
任何人都可以解决这个问题..
您需要使用逗号分隔变量,并且需要引用字符串变量。
Exec tbl_Master_InsertOrUpdate ' 1',' 三明治', null , null , null
在SSMS中,右键单击存储过程并选择脚本存储过程为 - 执行到 - 新查询编辑窗口'
将为您显示正确的执行语法
您可以将proc修改为
CREATE PROCEDURE tbl_Master_InsertOrUpdate
(
@MasterID NVARCHAR(20)= null,
@Category_Name NVARCHAR(20)= null,
@Category_Image IMAGE = null,
@Operating_Hours TIME = null,
@NonOperating_Hours TIME = null
)
AS
BEGIN
IF NOT NOT EXISTS(SELECT MasterID
FROM tbl_Master
WHERE MasterID = @MasterID)
INSERT INTO tbl_Master
(MasterID,
Category_Name ,
Category_Image,
Operating_Hours,
NonOperating_Hours
)
VALUES(@MasterID,
@Category_Name,
@Category_Image,
@Operating_Hours,
@NonOperating_Hours
)
ELSE
UPDATE tbl_Master
SET Category_Name = ISNULL(@ Category_Name,Category_Name),
Category_Image = ISNULL(@Category_Image ,Category_Image),
Operating_Hours = ISNULL(@Operating_Hours,Operating_Ho urs),
NonOperating_Hours = ISNULL(@ NonOperating_Hours,NonOperating_Hours)
WHERE MasterID = @MasterID
END
和Excite代码尝试
tbl_Master_InsertOrUpdate'1','Sandwich',null,null,null
Dear All,
I have created following StoredProcedure in SQLServer2008
Create Procedure tbl_Master_InsertOrUpdate(@MasterID nvarchar(20),@Category_Name nvarchar(20),@Category_Image image,@Operating_Hours time,@NonOperating_Hours time)
As
Begin
If Not Exists(Select MasterID from tbl_Master where MasterID=@MasterID)
Insert into tbl_Master(MasterID,Category_Name,Category_Image,Operating_Hours, NonOperating_Hours) values(@MasterID,@Category_Name,@Category_Image, @Operating_Hours,@NonOperating_Hours)
Else
Update tbl_Master Set Category_Name=@Category_Name,Category_Image= @Category_Image,Operating_Hours=@Operating_Hours,NonOperating_Hours=@NonOperating_Hours Where MasterID=@MasterID
End
I have compiled this & I am trying to execute this Procedure as follows
Exec tbl_Master_InsertOrUpdate 1 Sandwich null null null
But i got the following error
Incorrect Syntax near the keyword null
But the statement seems to be correct..
Can anybody resolve this issue..
You need commas to separate your variables and string variables need to be quoted.
Exec tbl_Master_InsertOrUpdate '1', 'Sandwich', null, null, null
In SSMS, right mouse on your stored procedure and choose 'Script Stored Procedure as' - 'Execute To' - 'New Query Editor Window'
The correct execution syntax will be displayed for you
You Can To Modify your proc to
CREATE PROCEDURE tbl_Master_InsertOrUpdate ( @MasterID NVARCHAR(20) = null , @Category_Name NVARCHAR(20)= null , @Category_Image IMAGE = null, @Operating_Hours TIME = null, @NonOperating_Hours TIME = null ) AS BEGIN IF NOT EXISTS ( SELECT MasterID FROM tbl_Master WHERE MasterID = @MasterID ) INSERT INTO tbl_Master ( MasterID , Category_Name , Category_Image , Operating_Hours , NonOperating_Hours ) VALUES ( @MasterID , @Category_Name , @Category_Image , @Operating_Hours , @NonOperating_Hours ) ELSE UPDATE tbl_Master SET Category_Name =ISNULL( @Category_Name, Category_Name), Category_Image =ISNULL( @Category_Image ,Category_Image), Operating_Hours = ISNULL(@Operating_Hours ,Operating_Hours), NonOperating_Hours = ISNULL(@NonOperating_Hours,NonOperating_Hours) WHERE MasterID = @MasterID END
and to Excite Code Try
tbl_Master_InsertOrUpdate '1', 'Sandwich', null, null, null
这篇关于如何在SQL Server2008中执行存储过程的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!