Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 214
  • Last Modified:

SQL Server Stored Procedure Syntax

I am attempting to write code to build a dynamic query based on a number of parameter and get and error in my IF block

Incorrect syntax near the keyword 'BEGIN'.


CREATE PROCEDURE Ewdataviewlink 
(@sTable     VARCHAR(100),
 @sLnkFld    VARCHAR(100),
 @sDispFld   VARCHAR(100),
 @sDispFld2  VARCHAR(100),
 @sFilterFld VARCHAR(100),
 @sOrderBy   VARCHAR(100),
 @sOrderType VARCHAR(100),
 @bDistinct  AS BIT,
 @sFilter    VARCHAR(100))
AS
  DECLARE @sSql VARCHAR(500)

  -- Construct@sQL@statement
  SET @sSql = 'SELECT'

  IF bdistinct
    BEGIN
        SET @sSql = @sSql + ' DISTINCT'
    END

  SET @sSql = @sSql + ' [' + @sLnkFld + '], [' + @sDispFld + ']'

  IF @sDispFld2 <> ''
    BEGIN
        SET @sSql = @sSql + ', [' + @sDispFld2 + ']'
    END

  IF @sFilterFld <> ''
    BEGIN
        SET @sSql = @sSql + ', [' + @sFilterFld + ']'
    END

  SET @sSql = @sSql + ' FROM [' + @sTable + ']'

  IF @sFilter <> ''
    BEGIN
        SET @sSql = @sSql + ' WHERE ' + @sFilter
    END

  IF @sOrderBy <> ''
    BEGIN
        SET @sSql = @sSql + ' ORDER BY [' + @sOrderBy + '] ' + @sOrderType
    END

  EXEC(@sSql)

Open in new window

0
shelbyinfotech
Asked:
shelbyinfotech
1 Solution
 
Philip PinnellCommented:
IF (bdistinct = 1)bit datatypes are not booleans just numerics that can be 0 or 1
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you have:

IF bdistinct

must be:

IF @bdistinct = 1
0
 
Philip PinnellCommented:
yes I missed the missing @
0
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
vdr1620Commented:
Change this part of the query

IF @bdistinct = 1
    BEGIN
        SET @sSql = @sSql + ' DISTINCT'
0
 
shelbyinfotechAuthor Commented:
Thank you
0
 
Philip PinnellCommented:
Thanks
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now