[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Converting SQL to Dynamic SQL: Invalid Operator for datatype

Posted on 2007-03-19
5
Medium Priority
?
366 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 2000 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 2000 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.
Suggested Courses

612 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