Dynamic SQL Strange Error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Dynamic SQL Strange Error

Author

Topic

superhero
Yak Posting Veteran

52 Posts

Posted - 2007-08-14 : 03:19:26

Hi Everyone

I have this stored procedure:
CREATE PROCEDURE QuerySearchAnyIndexField

(@userid as int, @subcoid as int,
@textboxa as nvarchar(100),
@textboxb as nvarchar(100),
@textboxc as nvarchar(100),
@textboxd as nvarchar(100),
@textboxe as nvarchar(100),
@textboxf as nvarchar(100),
@textboxg as nvarchar(100),

@comboboxa as nvarchar(100),
@comboboxb as nvarchar(100),
@comboboxc as nvarchar(100),
@comboboxd as nvarchar(100),
@comboboxe as nvarchar(100),
@comboboxf as nvarchar(100),
@comboboxg as nvarchar(100)
)
as

declare @sql nvarchar(4000)
set @sql = 'SELECT containers.containercode AS ContainerCode, indexdata.indexfield1 AS IndexField1, indexdata.indexfield2 AS IndexField2,
indexdata.indexfield3 AS IndexField3, indexdata.fileno AS FileNumber, subco.SubcoName, filestatus.filestatusname AS ItemStatus,
departments.deptname AS Department, indexdata.indexdate AS IndexDate
FROM indexdata INNER JOIN
containers ON indexdata.containerid = containers.containerid INNER JOIN
subco ON subco.subcoid = indexdata.subcoid INNER JOIN
filestatus ON filestatus.filestatusid = indexdata.filestatus INNER JOIN
departments ON departments.deptid = indexdata.deptid
WHERE ('+@comboboxa+' like ' + '%'+@textboxa+'%' + ') AND (indexdata.subcoid = '+@subcoid+')'

EXEC (@sql)
GO

SQL 2000 Enterprise Manager says "Syntax Check Successful" - no problems.

When I call the store from VB I get a runtime error. The error is very vague and concatenated but goes something like this:

"Server was unable to process request. ---> System.Data.SqlClient.SqlException: Syntax error converting the nvarchar value 'SELECT containers.containercode AS ContainerCode, indexdata.indexfield1 AS IndexField1, indexdata.indexfield2 AS IndexField2,
indexdata.indexfield3 AS IndexField3, indexdata.fileno AS FileNumber, subco.SubcoName, filestatus.filestatusname AS ItemStatus,
departments.deptname AS Department, indexdata...."

What am I doing wrong? I'm simply passing a field the user selects from the comboboxA (a valid field in the database table) and a value to search for (textboxa). All items are passed correctly to the store, I don't know why I'm getting this error. I have a suspicion that my ''''''' are not balanced or something, but surely SQL should report the error when I do a syntax check.

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2007-08-14 : 03:29:33

Howmany rows do your combo boxes carry?
What if no values were selected in the combo box?

Dynamic sql can really become messy and out of control, this case here is a good example.

If Those combo boxes carry only a few rows rather build a normal sql statement without the dynamic stuff.

Duane.

superhero
Yak Posting Veteran

52 Posts

Posted - 2007-08-14 : 03:36:50

Hi Duane

In this case the combobox only contains about 15 different fields. All the fields are nvarchar so I don't have to worry about the user selecting a date field or numeric field.

It is really important that I get this to work. My report builder for my application will be so much more powerful if I can let database administrators write queries using dynamic sql in the stored procedure.

Any ideas?

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2007-08-14 : 03:46:17

Ooooooohhhh - Itry to avoid dynamic sql at all costs.
The reason why I asked howmany rows are in the combo boxes is because from the dynamic sql syntax i saw, i can see that they represent columns in a database table.

That's why I was thinking that the query should be written with filtering using case statements based on what values were selected in the combo's rather than dynamically building the query.

also if no values were selected in one of the comboboxes and you aren't handling it correctly in your dynamic sql then it is going to fail too.

I still say a big NO to dynamic sql - it can be done in a much better way.

Oh and It's hard to beleive that there are dba's happy with an app to be using dynamic sql

Duane.

superhero
Yak Posting Veteran

52 Posts

Posted - 2007-08-14 : 03:48:47

Hi Duane

I hear what you're saying - I suppose I'll explore alternatives. Thanks for your response.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources