Dharmesh Sharma

Dharmesh Sharma

  • NA
  • 905
  • 926.6k

My function not working for string

Jan 21 2012 7:42 AM
this is my sql store producer that working for number as quots but when i pass string not working some error coem


ALTER


PROCEDURE [dbo].[Usp_Search_Criminal]

@name
varchar(max)




AS

BEGIN


CREATE


TABLE #temp(

[id] [int] 
NULL,

[criminalname] [varchar]
(max) NULL,

[descr] [varchar]
(max) NULL,

[image] [varchar]
(max) NULL,

[category] [int]
NULL,

[location] [varchar]
(max) NULL,

[address] [varchar]
(max) NULL,

[city] [varchar]
(max) NULL,

[country] [varchar]
(max) NULL



)











DECLARE


@tagname varchar(max),@tag_idint


DECLARE


@tagidCURSOR

SET


@tagid=CURSOR FOR

SELECT


id from dbo.Split(@name,',')

OPEN


@tagid

FETCH


NEXT

FROM


@tagid INTO @tag_id WHILE @@FETCH_STATUS = 0

BEGIN


select


@tagname=data from dbo.Split(@name,',') where id=@tag_id


INSERT


INTO #temp

SELECT


* from criminal where criminalname =@tagname OR location =@tagname OR city= @tagname or country =@tagname  or category =@tagname

UNION

select


cr.* from criminal cr left join tags tg on cr.id=tg.criminalid where tg.tags =@tagname



FETCH NEXT

FROM


@tagid INTO @tag_id

END

CLOSE


@tagid

DEALLOCATE @tagid

SELECT * FROm #temp
END
when i execute this


exec Usp_Search_Criminal 'dharmesh,sharma'


error come
Msg 245, Level 16, State 1, Procedure Usp_Search_Criminal, Line 39

Conversion failed when converting the varchar value 'dharmesh' to data type int.




and i exec this like


exec Usp_Search_Criminal '1212,212121'
then it run


i dont get this why i amconverting to int


thanks


i dont get this error
and when i pass this







Answers (5)