My function not working for string

Question

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

Dharmesh sharma · Answer

hello to all My split fuction perfectly working and i found my error is in my select commend SELECT * from criminal where criminalname = @tagname OR location = @tagname OR city = @tagname or country = @tagname or category = @tagname category is int and i compare to string so i just sort out this thanks

Jignesh Trivedi · Answer

hi your dbo.split function has an int column value and when you pass 'dharmeshsharma' it convert from 'dharmesh' to int so this error comes. you must change defination of dbo.split function else your input. hope this help.

Vulpes · Answer

I'd see if the error goes away if you use a different Split function. There are several to choose from in this thread: http://stackoverflow.com/questions/2647/split-string-in-sql

Dharmesh sharma · Answer

hello this is my fution and this is simple work as comma seprator and this is code this is return table and when exec this is working fine select * from dbo.Split('aabb''') then its retun table plz check ALTER FUNCTION [dbo] . [Split] ( @RowData nvarchar ( 2000 ) @SplitOn nvarchar ( 5 ) ) RETURNS @RtnValue table ( Id int identity ( 1  1 ) Data nvarchar ( 100 ) ) AS BEGIN Declare @Cnt int Set @Cnt = 1 While ( Charindex ( @SplitOn  @RowData )> 0 ) Begin Insert Into @RtnValue ( data ) Select Data = ltrim ( rtrim ( Substring ( @RowData  1  Charindex ( @SplitOn  @RowData )- 1 ))) Set @RowData = Substring ( @RowData  Charindex ( @SplitOn  @RowData )+ 1  len ( @RowData )) Set @Cnt = @Cnt + 1 End Insert Into @RtnValue ( data ) Select Data = ltrim ( rtrim ( @RowData )) Return END

Vulpes · Answer

My guess is that the problem lies here: SELECT id from dbo.Split(@name'') I imagine that 'Split' is a custom function as I don't think that T-SQL has a built-in function of that name. 'id' is defined to be an int but there are no integers embedded in @name when you pass the string 'dharmeshsharma' to the stored procedure. Your other string '1212212121' does have integers in it so runs OK.