Avatar of gtrapp
gtrapp asked on

Help with T-SQL

I am trying to concatenate strings in my WHERE clause where the string is based upon some condition (IF statements). However, I get a "Incorrect syntax near the keyword 'ORDER'." error.
Any suggestions on how to correct this?


set @strWhere = '(@BondNum is null or BondNum = @BondNum) AND
      (@BondLocation is null or BondLocation = @BondLocation) AND       
      (@BondDesc is null or BondDesc = @BondDesc) AND
      (@VolNum is null or HistoryVolume = @VolNum)  AND
      (@Active = 3 or Active = @Active) AND
      (@NeedsAttention = 3 or NeedsAttention = @NeedsAttention) AND
      (@BondReduced  = 3 or BondReduced = @BondReduced) AND
      (@Development = 0 or DevelopmentID = @Development)'

if @BondDate <> null
      set @strWhere = @strWhere + 'AND (convert(smalldatetime,OrigBondDate) = @BondDate)';

if @BondTicklerDate <> null
      set @strWhere = @strWhere + 'AND (convert(smalldatetime,TickerDate) = @BondTicklerDate)';

SELECT ' <All>' As BondNum
UNION
SELECT BOND.BondNum As BondNum
FROM   dbo.BOND LEFT OUTER JOIN
       dbo.luBondType ON dbo.BOND.BondTypeID = dbo.luBondType.BondTypeID LEFT OUTER JOIN
       dbo.luBondStatus ON dbo.BOND.BondStatusID = dbo.luBondStatus.BondStatusID
WHERE @strWhere
ORDER BY BondNum
Microsoft SQL Server 2005Microsoft SQL Server

Avatar of undefined
Last Comment
gtrapp

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
HetAlps

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
gtrapp

The script compiled correctly, but only if I did not include the MAX.
DECLARE @strWhere varchar (max) generates errors. Why is this?
ASKER
gtrapp

I am having problems with the

@BondNum varchar(150) = null,
@BondLocation varchar(150)=null,
I am having problems with the parameters that are passed into the procedure that are in the @strWhere.
It appears that @BondDesc, for example, is executed as @BondDesc and not a real value. So, when the procedure executes, SQL says the @BondDesc is invalid.

Here are my parameters:

@BondDesc varchar(255)=null,
@BondType varchar(255)=null,
@BondDate datetime = null,
@BondTicklerDate datetime = null,
@VolNum varchar(50) = null,
@Active int = 3,
@NeedsAttention int = 3,
@BondReduced int = 3,
@Development int = 0
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck