The data types varchar and varchar are incompatible in the modulo operator.

Question

The data types varchar and varchar are incompatible in the modulo operator.

Minnu

SSCertifiable

Points: 6451
More actions
August 31, 2012 at 2:40 am

#280914

As per requirement, i need to stored procedure like below....
CREATE procedure tsp_projname_text
(
@project_name varchar(max) = '12%Category%'
)
as
declare @strg1 varchar(max), @strg2 varchar(max), @finalstring varchar(max)
begin
set @strg1='select
--app.id,
edit.project_name
from tracker app inner join edit edit on app.id=edit.id
where'
set @strg2=' edit.project_name like ('%'+ REPLACE(@project_name,'*','%')+ '%')'
set @finalstring=@strg1+@strg2
EXEC (@finalstring)
END
while executing............getting below error
The data types varchar and varchar are incompatible in the modulo operator.
Please help

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

HowardW One Orange Chip Points: 29109 More actions · Answer 1

You need to have some extra quotes (two single quotes escape to a double quote) otherwise you're applying the modulo operator to your two strings (a percentage sign) rather than using it as a wildcard in the dynamic SQL:

set @strg2=' edit.project_name like (''%''+ REPLACE(@project_name,''*'',''%'')+ ''%'')'

I'd be remiss if I didn't point out that there is a huge risk of SQL Injection vulnerability with this kind of dynamic SQL, so you should make sure you understand the problem and properly parameterise...

There's a good article here with an explanation and some options:

http://blogs.msdn.com/b/raulga/archive/2007/01/04/dynamic-sql-sql-injection.aspx

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

You don't appear to need dynamic sql for this:

SELECT

--app.id,

edit.project_name

FROM tracker app

INNER JOIN edit edit

ON app.id=edit.id

WHERE edit.project_name LIKE ('%' + REPLACE(@project_name,'*','%') + '%')

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

I agree with Chris. There is no need for dynamic sql for this at all. In fact, you should run away from the implementation you put together quickly. It is wide open to sql injection.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

kak.mca Valued Member Points: 71 More actions · Answer 4

Thank you very much. Very big clue for me. It has saved my time.