Solved

Converting SQL to Dynamic SQL: Invalid Operator for datatype

Posted on 2007-03-19
5
343 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 10

Accepted Solution

by:
lahousden earned 500 total points
ID: 18748928
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
ID: 18749027
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
ID: 18749106
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
ID: 18749186
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
ID: 18749639
lahousden, thanks for you help bud. Worked a treat!

Rit
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

623 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