Posted by DA Morgan on 08/22/05 17:57

Damroo wrote:
> I have a table as below
> ---------------------------
> name linkcode level
> ---------------------------
> brando 1,3,8 1
> damroo 1,5 2
> rogers 2,7 1
> shane 1,7 1
>
>
> Now I want a query where I pass a level and linkcode (one of the codes
> in Comma Seperated values in the record) and it returns me a recordset
> with matching criteria like below
>
> I have a variable called lcode (int) and a variable called ulevel (int)
> if I pass lcode = 7 and level = 1 it should return rogers, shane in the
> record set
>
>
> My sql is as below
>
> "Select name from tblnames where level =" & ulevel & " and " & lcode &
> " in (linkcode)"
>
> I am unable to run this query as it returns an error that
> Syntax error converting the varchar value '1,3,8' to a column of data
> type int
>
> I even tried by updating the query with a UDF Split function, which
> works fine for a specific string CSV as params but does not take the
> CSV as a Fieldvalue
> "Select name from tblnames where level =" & ulevel & " and " & lcode &
> " in (select value from dbo.split(linkcode))"
>
> returns
> linkcode is not a recognized OPTIMIZER LOCK HINTS option
>
> can anyone help with this.. or suggest an alternative SQL for my
> problem..
> I am using MS Sql Server 2000
>
> thanks

If your linkcode is truly holding multiple separate values what you have
here, by definition, is a bad design: Fix the design.

Trying to find a work-around is the same as trying trying to sing "la la
la" really loud to drown out the sound of someone telling you your house
is on fire.
--
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)

[Back to original message]