shelbyinfotech
asked on
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'.
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)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
yes I missed the missing @
Change this part of the query
IF @bdistinct = 1
BEGIN
SET @sSql = @sSql + ' DISTINCT'
IF @bdistinct = 1
BEGIN
SET @sSql = @sSql + ' DISTINCT'
ASKER
Thank you
Thanks
IF bdistinct
must be:
IF @bdistinct = 1