Adding a Dynamic AND Clause to a Where Statement

Question

Post reply

Adding a Dynamic AND Clause to a Where Statement

alorenzini

SSCarpal Tunnel

Points: 4620
More actions
December 21, 2007 at 10:42 am

#180019

I have the following code:
DECLARE@ConsultantIDnVarChar(50)
,@PeriodDateDateTime
,@AchieveLevel int
,@Line varchar (10)
,@Status varchar(10)
,@GreaterThan Decimal
,@LessThanDecimal
,@EqualToDecimal
,@AndVARCHAR(200)
SET @ConsultantID = '0000344'
SET @PeriodDate = GETDATE()
SET @AchieveLevel = 20
SET @Line='1,2,3'
SET @status = 'Active' -- 'Inactive', 'All'
SET @AND = NULL
-- Set up Where clause for Sales amount filter.
IF (@GreaterThan IS NOT NULL)
SELECT @And = 'AND o.partOnetotal >= ' + @GreaterThan + ' '
ELSE IF (@LessThan IS NOT NULL)
SELECT @And = 'AND o.partOnetotal >= ' + @LessThan + ' '
ELSE IF (@EqualTo IS NOT NULL)
SELECT @And = 'AND o.partOnetotal >= ' + @EqualTo + ' '
-- Active Filter
IF (@Status = 'Active')
SELECT * FROM #DLFiltered WHERE Active = 1
AND CurrentLevelXID >= @AchieveLevel
AND DownlineLevel IN (SELECT * FROM dbo.ParseByComma(@Line))
+ @AND
ELSE IF (@Status = 'Inactive')
SELECT * FROM #DLfiltered WHERE Active = 0
AND CurrentLevelXID >= @AchieveLevel
AND DownlineLevel IN (SELECT * FROM dbo.ParseByComma(@Line))
ELSE IF (@Status = 'All')
SELECT * FROM #DLfiltered WHERE Active In (1,0)
AND CurrentLevelXID >= @AchieveLevel
AND DownlineLevel IN (SELECT * FROM dbo.ParseByComma(@Line))
When I do this I get the following error:
Msg 102, Level 15, State 1, Line 175
Incorrect syntax near '+'.
I need to pend the contents of @And to the Where clause:
IF (@Status = 'Active')
SELECT * FROM #DLFiltered WHERE Active = 1
AND CurrentLevelXID >= @AchieveLevel
AND DownlineLevel IN (SELECT * FROM dbo.ParseByComma(@Line))
+ @AND
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 1

Cant you use Dynamic SQL? Create the string with your Select statement based upon the criterea and the execute it?

Please check for SQL Injection if you plan to use dynamic SQL.

-Roy

alorenzini SSCarpal Tunnel Points: 4620 More actions · Answer 2

How would I do that? Can you show me an example?

Thanks,
Art
Database Analyst
Tastefully Simple, Inc.

alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 3

From BOL.

USE AdventureWorks;

GO

DECLARE @SQLString NVARCHAR(500);

/* Set column list. CHAR(13) is a carriage return, line feed.*/

SET @SQLString = N'SELECT FirstName, LastName, JobTitle' + CHAR(13);

/* Set FROM clause with carriage return, line feed. */

SET @SQLString = @SQLString + N'FROM HumanResources.vEmployee' + CHAR(13);

/* Set WHERE clause. */

SET @SQLString = @SQLString + N'WHERE LastName LIKE ''D%''';

EXEC sp_executesql @SQLString;

GO

-Roy

alorenzini SSCarpal Tunnel Points: 4620 More actions · Answer 4

Tam trying this:

IF (@Status = 'Active')

SET @SQLStr = 'SELECT * FROM #DLFiltered WHERE Active = 1'+ CHAR(13);

SET @SQLStr = @SQLStr + ' AND CurrentLevelXID >= ' + @AchieveLevel + ' '+ CHAR(13);

SET @SQlstr = @SQlstr + 'AND DownlineLevel IN (SELECT * FROM dbo.ParseByComma(' + @Line +'))'+ @AND

SELECT @SQLstr

EXEC sp_executesql @SQLstr;

And I am recieving this error:

Msg 245, Level 16, State 1, Line 174

Conversion failed when converting the nvarchar value 'SELECT * FROM #DLFiltered WHERE Active = 1

AND CurrentLevelXID >= ' to data type int.

Any ideas?

Thanks,
Art
Database Analyst
Tastefully Simple, Inc.

alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!

David McFarland SSChampion Points: 11815 More actions · Answer 5

Without the rest of your code, it's hard to say for sure what all problems you might have, but I'm guessing that your @AchieveLevel variable is an int. If so, cast it inline so that the string concatenation will be happy.

alorenzini SSCarpal Tunnel Points: 4620 More actions · Answer 6

That is what I figured out.

IF (@Status = 'Active')

SET @SQLStr = 'SELECT * FROM #DLFiltered WHERE Active = 1 '

SET @SQLStr = @SQLStr + ' AND CurrentLevelXID >= ' + CONVERT(Varchar(2),@AchieveLevel )

SET @SQlstr = @SQlstr + 'AND DownlineLevel IN (SELECT * FROM dbo.ParseByComma(' + @Line +'))'+ @AND

SELECT @SQLstr

EXEC sp_executesql @SQLstr;

Thanks,
Art
Database Analyst
Tastefully Simple, Inc.

alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!