Query issue

Question

Post reply

Query issue

Ahmad Osama

SSCertifiable

Points: 7676
More actions
July 9, 2008 at 11:33 pm

#72355

Hi All,
How to write this correctly :-
CREATE proc usp_strsrchtemp
@userstr varchar(100),
@strcmp varchar(200),
@iCityid int,
@Phone varchar(200)
AS
Set nocount on
Declare @count int,@i int,@flag int,@tmpstr varchar(100),@tmpcustid varchar(100)
Declare @sSql varchar(7000)
set @sSql='select iCustomerid,sCustomercompname from CustomerMaster C inner join Citymaster Ci
On C.iCityid=Ci.iCityid where Ci.iCityid=@iCityid and sCustomercompname like '% @strcmp %'
and (sPhoneno like '%@Phone%' or sMobilno like '%@Phone%') '
print @sSql
Error:
Server: Msg 403, Level 16, State 1, Procedure usp_strsrchtemp, Line 10
Invalid operator for data type. Operator equals modulo, type equals varchar.
Thanks
Regards,
[font="Verdana"]Sqlfrenzy[/font]

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

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

ChiragNS One Orange Chip Points: 26137 More actions · Answer 1

Hi

I have corrected a portion of your proc. u can continue in the same manner.

set @sSql='select iCustomerid,sCustomercompname from CustomerMaster C inner join Citymaster Ci

On C.iCityid=Ci.iCityid where Ci.iCityid=@iCityid and sCustomercompname like ''%' + @strcmp + '%'''

"Keep Trying"

Dugi SSCoach Points: 17998 More actions · Answer 2

Ok as I can see at the varchar parameter is incorrect you should write like this ''%' + @parameter(varchar)+'%''

CREATE proc usp_strsrchtemp

@userstr varchar(100),

@strcmp varchar(200),

@iCityid int,

@Phone varchar(200)

AS

Set nocount on

Declare @count int,@i int,@flag int,@tmpstr varchar(100),@tmpcustid varchar(100)

Declare @sSql varchar(7000)

Correct this and try:

set @sSql='select iCustomerid,sCustomercompname from CustomerMaster C inner join Citymaster Ci

On C.iCityid=Ci.iCityid where Ci.iCityid=@iCityid and sCustomercompname like ''%'+@strcmp+'%''

and (sPhoneno like ''%'+@Phone+'%'' or sMobilno like ''%'+@Phone+'%'')'

print @sSql

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

Ahmad Osama SSCertifiable Points: 7676 More actions · Answer 3

Thank u all 🙂

if am not using sql injection...is this correct

select iCustomerid,sCustomercompname from CustomerMaster C inner join Citymaster Ci

On C.iCityid=Ci.iCityid where Ci.iCityid=@iCityid and sCustomercompname like '%' + @strcmp + '%'

and (sPhoneno like '%'+ @Phone + '%' or sMobilno like '%'+ @Phone + '%')

Regards,
[font="Verdana"]Sqlfrenzy[/font]