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
Loading
Dharmesh SharmaPosted Jan 23, 2012, 12:54 AM
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 TrivediPosted Jan 22, 2012, 11:05 PM
your dbo.split function has an int column value and when you pass 'dharmesh,sharma', it convert from 'dharmesh' to int so this error comes.
you must change defination of dbo.split function else your input.
hope this help.
VulpesPosted Jan 22, 2012, 7:16 PM
http://stackoverflow.com/questions/2647/split-string-in-sql
Dharmesh SharmaPosted Jan 21, 2012, 10:54 PM
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('aa,bb',',')
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
VulpesPosted Jan 21, 2012, 8:29 AM