Solved

SQL If..End If - need help with syntax

Posted on 2013-01-18
6
467 Views
Last Modified: 2013-01-18
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
0
Comment
Question by:Lorna70
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
6 Comments
 
LVL 25

Assisted Solution

by:Lee Savidge
Lee Savidge earned 50 total points
ID: 38792616
If @catId <> 0
    set @sql = @sql + ' WHERE Cat_ID = '+@catId
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 200 total points
ID: 38792665
simplest version, no else, 1 statement:
IF <condition> 
   <statement 

Open in new window


with a simple else
IF <condition> 
   <statement
  ELSE [statement] 

Open in new window


and if you need to have more than 1 statement:
IF <condition> 
  BEGIN
   <statement >
   <statement >
   <statement >
  END 
 ELSE
 BEGIN
   <statement >
   <statement >
   <statement >
 END 

Open in new window

0
 

Author Comment

by:Lorna70
ID: 38792667
OK thanks - I removed 'then' but still get:

Incorrect syntax near the keyword set
Incorrect syntax near GO
0
Database Solutions Engineer FAQs

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller single-server environments.

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38792674
please see my comment. in t-sql, there is no "end if" as such.
0
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 38792685
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
0
 

Author Comment

by:Lorna70
ID: 38793340
Thanks - I had to remove the GO aswell and now it works fine :-)
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

626 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question