May 15, 2008 at 12:26 pm
You can also use the CHARINDEX function. Like so:
select ques.catid,ques.question,ques.qid,ques.subcatid,sub.subcategory from ada_question ques,ata_subcat sub where
ques.subcatid=sub.subcatid and ques.catid=1 and ques.status=1 and CHARINDEX(cast(ques.qid as varchar(50)), @qid) > 0
order by sub.subcatid,ques.qid asc
May 15, 2008 at 10:46 pm
Hi
Its not working it gives me same error.
Syntax error converting the varchar value '(39,40,1,4,35,41,52,54,55,56)' to a column of data type int.
May 16, 2008 at 1:09 am
Hi Rahul,
I think the solution of your problem lies in the following function :
----------- STARTING OF FUNCTION
CREATE FUNCTION dbo.fn_aviSplit(@vNumber varchar(50))
RETURNS @MyTable TABLE
(
Number int
)
AS
BEGIN
DECLARE @vNumberDump varchar(50),
@vChar varchar(50),
@iCommaPos int,
@iPos int,
@iEndPos INT
SET @iPos = 1
WHILE (len(@vNumber)>0)
BEGIN
SET @iCommaPos = CHARINDEX(',',@vNumber )
IF @iCommaPos>0
BEGIN
SET @vNumberDump = SUBSTRING(@vNumber,@iPos,@iCommaPos-1)
SET @iEndPos = len(@vNumber)-@iCommaPos
SET @vNumber = SUBSTRING(@vNumber,@iCommaPos+1,@iEndPos)
INSERT into @MyTable values(CAST(@vNumberDump as INT))
END
ELSE
BEGIN
INSERT into @MyTable values(CAST(@vNumber as INT))
SET @vNumber = ''
END
END
RETURN
END
-------- END OF FUNCTION
YOU CAN CALL THE ABOVE FUCNTION AS FOLLOWS:
select * from YOURTABLE where ID in (select * from dbo.fn_avisplit ('1,2,3,4'))
May 16, 2008 at 1:15 am
HI RAHUL FOLLOWING FUNCTION SHOULD SOLVE YOUR PROBLEM
---------------FUNCTION START
CREATE FUNCTION dbo.fn_aviSplit(@vNumber varchar(50))
RETURNS @MyTable TABLE
(
Number int
)
AS
BEGIN
DECLARE @vNumberDump varchar(50),
@vChar varchar(50),
@iCommaPos int,
@iPos int,
@iEndPos INT
SET @iPos = 1
WHILE (len(@vNumber)>0)
BEGIN
SET @iCommaPos = CHARINDEX(',',@vNumber )
IF @iCommaPos>0
BEGIN
SET @vNumberDump = SUBSTRING(@vNumber,@iPos,@iCommaPos-1)
SET @iEndPos = len(@vNumber)-@iCommaPos
SET @vNumber = SUBSTRING(@vNumber,@iCommaPos+1,@iEndPos)
INSERT into @MyTable values(CAST(@vNumberDump as INT))
END
ELSE
BEGIN
INSERT into @MyTable values(CAST(@vNumber as INT))
SET @vNumber = ''
END
END
RETURN
END
-----------------END OF FUNCTION
NOW YOUR QUERY CAN GO AS FOLLOWS
select ques.catid,ques.question,ques.qid,ques.subcatid,sub.subcategory from ada_question ques,ata_subcat sub where
ques.subcatid=sub.subcatid and ques.catid=1 and ques.status=1 and ques.qid in (select * from dbo.fn_avisplit (@QID))
order by sub.subcatid,ques.qid asc
ALSO IN THE ABOVE QUERY PLEASE NOT THAT YOUR VARIABLE @QID HAS TO BE DECLARED WITH THE LENGTH AS:
DECLARE @QID VARCHAR(50)
AND NOT AS
DECLARE @QID VARCHAR
hope that helps
Avaneesh.
May 16, 2008 at 11:33 pm
Hi Avaneesh,
Thanks a lot. It works for me and solve my problem.
Thanks again.
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply