EpicurealDogMan EpicurealDogMan - 4 months ago 9
SQL Question

IF EXISTS SQL Statement won't work

I'm getting this error from the below T-SQL query. Msg 102, Level 15, State 1, Line 13
Incorrect syntax near '101' Can anyone spot where the error here is? Using Management Studio / SQLServerExpress The desired result is a new record inserted or an existing one update. Thank you.

IF EXISTS (SELECT * FROM Product WHERE ProductID = 101)
UPDATE Product SET
ProductID = 101 , InsurerID = 1, CategoryID =1, Name = 'Landlord',
[description] ='Allianz Landlord', label = 'NULL', AssumptionRef ='NULL',
QuoteProviderKey ='A75',AccidentalDamageCover =0, ProductBenefitGroupID = 11,
IsAvailableToBuy =1,IsAvalableToDisplay =0,PercentageContentsCover ='NULL',
ProductPolicyView ='NULL', ProductFee =NULL
WHERE ProductID = 101
ELSE
INSERT INTO Product
VALUES 101,1,1,'Landlord','Allianz Landlord','NULL','NULL','A75',0,11,1,0,'NULL','NULL',NULL
WHERE ProductID = 101

Answer

Your INSERT statement has a WHERE clause with VALUES set, which isn't valid:

INSERT INTO Product
VALUES 101
    ,1
    ,1
    ,'Landlord'
    ,'Allianz Landlord'
    ,'NULL'
    ,'NULL'
    ,'A75'
    ,0
    ,11
    ,1
    ,0
    ,'NULL'
    ,'NULL'
    ,NULL
WHERE ProductID = 101

You are also missing () around the VALUES section, as well as the columns listed out (though, that won't give an error, but you should get in the habit of explicitly listing the columns)

Your statement should look like this:

INSERT INTO Product
(
    ProductID
    ,InsurerID
    ,CategoryID
    ,NAME
    ,[description]
    ,label 
    ,AssumptionRef 
    ,QuoteProviderKey 
    ,AccidentalDamageCover 
    ,ProductBenefitGroupID 
    ,IsAvailableToBuy 
    ,IsAvalableToDisplay 
    ,PercentageContentsCover 
    ,ProductPolicyView 
    ,ProductFee 
)
VALUES 
(
    101
    ,1
    ,1
    ,'Landlord'
    ,'Allianz Landlord'
    ,'NULL'
    ,'NULL'
    ,'A75'
    ,0
    ,11
    ,1
    ,0
    ,'NULL'
    ,'NULL'
    ,NULL
)

Another thing to question is why you're using string 'NULL' values. If you're intending these fields to be NULL, they should be NULL and not 'NULL'

Comments