Solved

Converting SQL to Dynamic SQL: Invalid Operator for datatype

Posted on 2007-03-19
5
337 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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
In T-SQL cursor convert smallint to varchar 15 50
Begin Transaction 12 25
Report 8 24
Need split for SQL data 4 32
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

739 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