function in where clause

[ayazhoda]

Hi All

I am writing a stored procedure which return rows

here's stored procedure

CREATE PROCEDURE [DBO].[SP_XYZ]
AS
SELECT * FROM TABLENAME WHERE FIELDNAME IN ([DBO].[FN_ABC])

FUNCTION [DBO].[FN_ABC] return something like that

'xx', 'AB','BC'

Now

I can't able to get query right stored procedure doesn't like function in where clause but i write some thing similar with out having function in where clause
SELECT * FROM TABLENAME WHERE FIELDNAME IN ('xx', 'AB','BC') it works fine.

let me explain what function is doing function get field in table which has value like xx;ab;bc;
function replaces ";" and rearrage value like 'xx','ab','bc'

Any advice or suggestion

Thanks

Ayaz

[skhanal]

What type of function is fn_abc? Does it return string or table?

[ayazhoda]

it return string

[ayazhoda]

I did somthing like this and it work
SET @STRING = [DBO].[FUNCTIONAME](P1,P2)
EXEC ('SELECT * FROM TABLENAME WHERE (' + @STRING +')')

[skhanal]

That should work or you can return table from function and use IN select functionname.