CODEWITHSUNDEEP
sqlsql-server-2008stored-procedures
Murali Uppangala
Murali Uppangala

Reputation: 904

Passing params to Stored Procedure-Special case

ALTER  PROCEDURE [dbo].[usp_SaveUserCustomerXRef]
    @Username varchar(48),
    @LinkID varchar(48)
AS
DELETE FROM UserCustomerX WHERE UserName=@UserName AND LinkID=@LID
INSERT INTO UserCustomerX(Username, LID)
VALUES (@Username, @LID)

is executed As-

  1. EXEC usp_SaveUserCustomerXRef '10026:ttmm19',2H0;//Syntax error
  2. EXEC usp_SaveUserCustomerXRef '10026:ttmm19',A1;//Executed+Inserted
  3. EXEC usp_SaveUserCustomerXRef '10026:ttmm19',2;//Executed+Inserted

  4. EXEC usp_SaveUserCustomerXRef '10026:ttmm19','2H0';//Executed+Inserted

    The columns in the table UserCustomerX are of type Varchar.My problem is with case no:2 why does it insert values though there is no single quotes.But Case No:1 gave syntax error.Case 3&4 are normal conditions. Why does case 2 work? without quotes?

Upvotes: 0

Views: 42

Answers (1)

Bedouin
Bedouin

Reputation: 490

Your second paramter @LinkID is a varchar, so you better force and generalize the use of quotes when executing your stored procedure.

And as Nicarus said, it's probably an implicit conversion to number which provoke the syntax error.

Upvotes: 1

Related Questions