How to execute stored procedure in SQL Server2008

Question

2.00/5 (2 votes)

See more:

Dear All,
I have created following StoredProcedure in SQLServer2008

SQL

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..

Posted 7-Mar-12 3:49am

SIVA RAMAMOORTHY

Updated 7-Mar-12 3:53am

Dylan Morley

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Dylan Morley · Answer 1 · 2012-03-07T03:55:00

You need commas to separate your variables and string variables need to be quoted.

SQL

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

ahmed_soft3 · Answer 2 · 2013-11-17T11:31:00

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

How to execute stored procedure in SQL Server2008

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0