Converting SQL to Dynamic SQL: Invalid Operator for datatype

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)


**************************************
LVL 1
rito1Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lahousdenCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rito1Author Commented:
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
lahousdenCommented:
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
rito1Author Commented:
Excellent, I see what you mean. I will fix this now and let you know how I get on.

Many thanks
Rit
0
rito1Author Commented:
lahousden, thanks for you help bud. Worked a treat!

Rit
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.