May 14, 2008 at 4:18 am
Hello all
when i am executing following query i am getting following error.
my query 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 (cast(@qid as int))
order by sub.subcatid,ques.qid asc
where @qid contain='39,40,1,4,35,41,52,54,55,56'
I need to convert string value int because my dolumn data type is int.
Any Help?
May 14, 2008 at 4:26 am
The value has the commas in the wrong place, strip out the commas and convert it.
you may also have to use the FLOAT data type as the result may be too large for int > 2,147,483,647
May 14, 2008 at 4:31 am
I didn't understand what you are trying to say. comma is in wrong place.
.i need those comma because these separate the question id. I need information for every question id.
May 14, 2008 at 4:37 am
you are trying to convert a string that contains commas to an Int,
so it can not convert to an int.
strip them out.
Unless you are trying to covert each comma delimitted value?
which is a different story
May 14, 2008 at 4:48 am
Yes i am trying to convert a string to int.
If i strip all comma
then in that case @qid contain=39401435415254556
how could i separate each question id and find the record for each?
May 14, 2008 at 4:57 am
hi
How i do this is like ...
Create a user defined function that returns a table to break down your string to rows. Then use the resultset of the function in your query.
you will find many such function here or on the net.
"Keep Trying"
May 14, 2008 at 5:01 am
Or put the values that you need into a table and select them from there, will run more efficently and will be more scalable.
post here if you need any help with the query
May 14, 2008 at 5:02 am
Hi
Ok i am trying to search the function which fulfill my criteria?
Can you provide me some link from where i can get the function?
Thanks
May 14, 2008 at 5:11 am
That means i have to store all the values to different table and retrieve values from that table?
May 14, 2008 at 5:13 am
rahul (5/14/2008)
That means i have to store all the values to different table and retrieve values from that table?
That would be the best way of doing this.
instead of
and ques.qid in (cast(@qid as int))
you could have
and ques.qid in (Select qid from MyTable)
Ideally you would point to the table that you already get your values from.
May 14, 2008 at 5:22 am
ok thanks I will try it out.I f will have some problem then I will get back to u.
May 15, 2008 at 2:24 am
rahul
here are 2 links to the function that will split your string to a recorset , i found on SSC.
http://www.sqlservercentral.com/articles/Advanced+Querying/2547/
This one uses a number table to do this.
http://www.sqlservercentral.com/scripts/Miscellaneous/31913/
"Keep Trying"
May 15, 2008 at 2:36 am
Thanks a lot
May 15, 2008 at 5:55 am
This is also an excellent link: http://www.sommarskog.se/arrays-in-sql-2005.html#CSV
May 15, 2008 at 11:59 am
If I understand your question, you are trying to query for records in which the ques.qid is found in this list of itegers: 39,40,1,4,35,41,52,54,55,56
If so, then try using dynamic SQL. That is, construct your T-SQL in a string, and execute the string.
The following is an example. (You need to replace my 'SELECT ID FROM Company where ID in ' with your query.)
declare @qid varchar(100) SET @qid = '(39,40,1,4,35,41,52,54,55,56)'
declare @sql varchar(2000)
SET @sql =
'SELECT ID FROM Company where ID in '+@qid
exec (@Sql)
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply