Solved

Converting SQL to Dynamic SQL: Invalid Operator for datatype

Posted on 2007-03-19
5
316 Views
Last Modified: 2007-03-19
Hi All

I am trying to change my SQL into dynamic SQL so that I can be more flexible with the WHERE statement but seem to be having a problem with this... its more like my basic understanding of Dynamic SQL that is causing the issue... I seem to be getting the error:

Error 403: Invalid Operator for datatype. Operator equals modulo, type equals varchar.

Here is an example of my Stored Procedure SQL that I want to convert:

**************************************

CREATE PROCEDURE dp_NEWbrowseSELECT

      @CountyID Int = NULL,
      @countryID int = NULL,
      @genre int = NULL,
      @Gender Int = NULL,
      @searchstring varchar(2000) = ''

AS

SELECT

      tblUsers.uname
      
FROM

      tblUsers INNER JOIN
                     
                      tblGender ON tblUsers.Gender = tblGender.Gender INNER JOIN
                      LinkGenre ON tblUsers.UserID = LinkGenre.UserID

WHERE

      tblUsers.CountyID = COALESCE(@CountyID, tblUsers.CountyID) AND
      tblUsers.countryID = COALESCE(@countryID, tblUsers.countryID)  AND
      tblUsers.Gender = COALESCE(@Gender, tblUsers.Gender)  AND
      LinkGenre.genreID = COALESCE(@genre, LinkGenre.genreID)  AND
      tblUsers.hide = 0 AND
      tblUsers.biog LIKE '%'+@searchstring+'%'

GO

**************************************

... And here is my attempt at the Dynamic SQL, please feel free to scrutinise my attempt as I am on a huge learning curve at the moment:

**************************************

CREATE PROCEDURE [dbo].[DP_NEWbrowseSELECTTEMP]

      @countryID int = NULL,
      @genre int = NULL,
      @Gender Int = NULL,
      @searchstring varchar(2000) = ''

AS
declare @cmd nvarchar(4000)
declare @County varchar(100)
Set @County = '(tblUsers.CountyID = 57 and tblUsers.CountyID = 58) AND'

Set @cmd = '

SELECT

      tblUsers.UserID,

FROM

      tblUsers INNER JOIN
                      tblGender ON tblUsers.Gender = tblGender.Gender INNER JOIN
                      LinkGenre ON tblUsers.UserID = LinkGenre.UserID

WHERE @County

      tblUsers.countryID = COALESCE(@countryID, tblUsers.countryID)  AND
      tblUsers.Gender = COALESCE(@Gender, tblUsers.Gender)  AND
      LinkGenre.genreID = COALESCE(@genre, LinkGenre.genreID)  AND
      tblUsers.hide = 0 AND
      tblUsers.biog LIKE '%''+@searchstring+''%''

exec(@cmd)


**************************************
0
Comment
Question by:rito1
  • 3
  • 2
5 Comments
 
LVL 10

Accepted Solution

by:
lahousden earned 500 total points
Comment Utility
You are missing a quote on the last line before the "exec"...

you have it as:

 tblUsers.biog LIKE '%''+@searchstring+''%''

you should double up the first single quote like this:

 tblUsers.biog LIKE ''%''+@searchstring+''%''
0
 
LVL 1

Author Comment

by:rito1
Comment Utility
thanks lahousden, this now passes the syntax check.

But when I run the stored procedure I get the error message:

Must declare the variable '@County'

.. which it appears as though I have... any ideas at all?

Thanks again fro you help so far.

Rit
0
 
LVL 10

Assisted Solution

by:lahousden
lahousden earned 500 total points
Comment Utility
Oh, yeah... you'll need to do some converting to text for the numerics etc.  E.g. for this one you will need to change the first line of your WHERE clause from:

tblUsers.countryID = COALESCE(@countryID, tblUsers.countryID)  AND

to:

tblUsers.countryID = COALESCE(' + cast (@countryID as nvarchar) + ', tblUsers.countryID)  AND

... and do similar things where you are including other local variables in your query.  If you hit other probs post back here...
0
 
LVL 1

Author Comment

by:rito1
Comment Utility
Excellent, I see what you mean. I will fix this now and let you know how I get on.

Many thanks
Rit
0
 
LVL 1

Author Comment

by:rito1
Comment Utility
lahousden, thanks for you help bud. Worked a treat!

Rit
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now