TECHNOLOGIES
FORUMS
JOBS
BOOKS
EVENTS
INTERVIEWS
Live
MORE
LEARN
Training
CAREER
MEMBERS
VIDEOS
NEWS
BLOGS
Sign Up
Login
No unread comment.
View All Comments
No unread message.
View All Messages
No unread notification.
View All Notifications
Answers
Post
An Article
A Blog
A News
A Video
An EBook
An Interview Question
Ask Question
Forums
Monthly Leaders
Forum guidelines
Dharmesh Sharma
NA
905
928.3k
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_id
int
DECLARE
@tagid
CURSOR
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
Reply
Answers (
5
)
Parameter Substitution
materialized View