Solved

SQL If..End If - need help with syntax

Posted on 2013-01-18
6
444 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
  • 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 142

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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 142

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

773 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