Dynamic string comparision

[christer99]

I am trying to compare a string using the IN statement using a dynamic variable, such as WHERE (dbo.POLICY.STATUS IN (@v_statact))

DECLARE
@v_statact VARCHAR(90),
@v_actlostcd VARCHAR(80)

set @v_statact = (SELECT CURSTCODES
FROM dbo.ProgramConf)

set @v_statact=replace(@v_statact, ' ', ' ')
set @v_statact=''''+replace(@v_statact, ' ', ''', ''')+''''


SELECT dbo.INS.REC, dbo.INS.NAME, dbo.INS.ATTN, @v_statact as bbb
FROM dbo.POLICY RIGHT OUTER JOIN
dbo.INS ON LEFT(dbo.POLICY.POL_IDX, 7) = dbo.INS.REC LEFT OUTER JOIN
dbo.AccountInfo ON dbo.INS.REC = dbo.AccountInfo.ACCREC
WHERE (dbo.POLICY.STATUS IN (@v_statact))
GROUP BY dbo.INS.REC, dbo.INS.NAME, dbo.INS.ATTn

 

[JohnDTampaBay]

The easiest way I can think of is to create a function that splits the string on a character and returns a table of values. Then join to the table function on its value column. Luckily, the function has already been written for us.

Treat yourself to fn_Split()

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag01/html/TreatYourself.asp

You can specify any delimiter you like. This function has been invaluable to me for several years.

SELECT dbo.fn_Split('how now|brown cow','|'')
returns
idx value
0 how now
1 brown cow

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[christer99]

Not sure what I am doing wrong, but I am getting
invalid object name 'dbo.fn_split' Do you have a working example?

I am using SQL 2000

 

[vongrunt]

Are you doing

select dbo.fn_split()

or

select * from dbo.fn_split()

?

------
[small]<this is sig>
select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')
</this is sig>[/small]

 

[christer99]

I found this example online and it is givng me an error (invalid object name): SELECT s.Position , s.cValue
FROM dbo.fn_split( '1,2,3,4,5,' , default ) AS s
ORDER BY s.Position

 

[JohnDTampaBay]

This is not a function shipped with SQL Server, although IMHO it should be. Did you download the code for the function and execute it in the database?

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)