asked on

SQL If..End If - need help with syntax

Hi Please help me :-)

I can't get this to sproc to work, it keeps saying:

Incorrect syntax near the keyword then
Incorrect syntax near the keyword set
Incorrect syntax near GO

[spListProductsByCatOrAll]
(
@catId int
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @sql nvarchar(max)

-- Insert statements for procedure here
      set @sql = 'SELECT Product_ID, Description, Cat_ID, Product_Title,Created_By FROM Products'
If @catId <> 0 then
set @sql = @sql + ' WHERE Cat_ID = '+@catId
      End If
set @sql = @sql +' ORDER BY Sequence'
      Execute(@sql)

END
GO

SOLUTION

Lee

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Lorna70

ASKER

OK thanks - I removed 'then' but still get:

Incorrect syntax near the keyword set
Incorrect syntax near GO

Guy Hengel [angelIII / a3]

please see my comment. in t-sql, there is no "end if" as such.

Lee

Remove the END IF.

[spListProductsByCatOrAll]
(
@catId int
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @sql nvarchar(max)

-- Insert statements for procedure here
set @sql = 'SELECT Product_ID, Description, Cat_ID, Product_Title,Created_By FROM Products'
If @catId <> 0
set @sql = @sql + ' WHERE Cat_ID = '+@catId

set @sql = @sql +' ORDER BY Sequence'
Execute(@sql)

END
GO

Lorna70

ASKER

Thanks - I had to remove the GO aswell and now it works fine :-)