Invalid SQL Statement Error

[Greg Snidow]

Greetings all. I use the following stored procedure as the record source of
a search form. Basically it is as outlined in MS help

ALTER Procedure spMDUSearch
@CO nvarchar(50), @property_name varchar(100)
As
Declare @SQLString varchar(1000)
Declare @SelectList varchar(100)

SET NOCOUNT ON

SELECT @SelectList = 'SELECT * from tblmdu_tracking'
IF @CO Is NOT NULL
BEGIN
SELECT @SQLString = 'WHERE CO = ''' + @CO + ''''
END
IF @property_name Is NOT NULL
BEGIN
IF @SQLSTRING Is NOT NULL
BEGIN
SELECT @SQLSTRING = @SQLSTRING +
' AND property_name = ''' + @property_name + ''''
END
ELSE
BEGIN
SELECT @SQLSTRING = 'WHERE property_name = ''' +
@property_name + ''''
END
END
SELECT @SelectList = @SelectList + ' ' + @SQLString
EXECUTE(@SELECTLIST)

This works beautifully most of the time. I use combo boxes on a search form
to enter the parameter values to be passed to the procedure. The record
source of the combo boxes are fields in tblmdu_tracking. Some of the
property_name records have parentheses in them, like this 'FOUR WINDS AT
OAKTON CONDOS - ( FORMERLY: SUMMIT). When I choose these records for the
@property parameter I get an error that says 'Invalid SQL Statement. Check
the server filter on the form record source.' Is there something I can do to
allow the parentheses? Thank you

 

[Sylvain Lafontaine]

The presence of parenthesis shouldn't have any effect inside a string value
delimited by quotes. Maybe I'm missing something obvious but in my opinion,
the error is probably somewhere else than because of the presence of
parenthesis.

Maybe the length of 100 characters is to short for @SelectList? With
dynamic sql string, you should always use the maximum length of varchar
(8000) to eliminate for the possibility of truncated statement.

Try storing or returning the value of @SELECTLIST to make sure that it is
OK. You can also display it here or run it in Query Analyser to test for
the presence of any error.

Are you setting any server filter on the form by any chance?

 

[Robert Morley]

Further to Sylvain's message, it is DEFINITELY the 100 character length
that's the problem. Even assuming @CO is null, you come up with:

SELECT * from tblmdu_tracking WHERE property_name = 'FOUR WINDS AT OAKTON
CONDOS - ( FORMERLY: SUMMIT)'

....which is 103 characters, and too long for @SelectList. Had you meant it
to be 1000, like the @SQLString parameter? As Sylvain said, your best bet
is to use varchar(8000).



Rob

 

[Greg Snidow]

Sylvain, thanks to you once again. I upped the character length to 8000 and
all records came up fine. Thank you to you too Robert, both of you have been
so helpful to me.

 

[Sylvain Lafontaine]

You're welcome but you're also mixing nvarchar and varchar in your SP. If
you need to use nvarchar because of a language/localization issue, then
everything should be nvarchar. The maximum length for nvarchar is 4000.
You can increase this length by splitting the sql string inside the call to
the execute statement:

EXECUTE (@SelectList + ' ' + @SQLString)

instead of

SELECT @SelectList = @SelectList + ' ' + @SQLString
EXECUTE(@SELECTLIST)