Sql qry not returning the desired reuslt

Is the function fnGetHospitalnolist a table valued function / scalar function ?
Could you please post the funtion code ?

Select  hospnolist from [dbo].fnGetHospitalnolist('02693150')

should return 3 rows, not 3 items combined into a single string

i.e. fnGetHospitalnolist should be rturning a table variable so that

Select * from his.dbo.PREG P
where  P.pg_regno in (
                     Select hospnolist from [dbo].fnGetHospitalnolist('02693150')
                     )

will work.

Otherwise you would have to use dynamic sql

Assuming your function does (change it to perform in a similar way if it doesn't):

Create Function dbo.fnGetHospitalnolist()
Returns Table
As
Return
Select '02693150' As regno
Union Select '02120832',
Union Select '01509522'

And now,
Select regno From dbo.fnGetHospitalnolist();
produces:
regno
'02693150'
'02120832'
'01509522'

The solution will be:
Select * from his.dbo.PREG P
Cross Apply
(Select regno From dbo.fnGetHospitalnolist()
Where regno = P.pg_regno) As D

Have in mind to add the parameter to the function as required.

Mike

Actually I wanted to use the output of the function directly in the "in clause" of sql. The function will return a table value with one row of  data in the format   '02693150','02120832','01509522'
I think that is the problem.  But why it does not work.  When I  hardcode the same o/p in the  in clause it works. but when I put as o/p of function in the "in clause" it does not work.

No, you cannot have a "table of values" in a single string,  even if that string has a hundred commas in it, it is still a SINGLE value

Quite literally your function should return 3 rows. One value per row.

Thanks I got it.  I have used split string to achieve the desired result. just for information  how to use dynamic sql for the same.

A sample using dynamic SQL follows

DECLARE @SQLString1	        VARCHAR(500);
DECLARE @FunctionResult		VARCHAR(500);
DECLARE @FinalSQLCommand	VARCHAR(1000);

SET	@SQLString1 = 'Select * from his.dbo.PREG P where  P.pg_regno in (' ;
SELECT	@FunctionResult =  hospnolist FROM [dbo].fnGetHospitalnolist('02693150') ;
SET	@FinalSQLCommand = @SQLString1 + @FunctionResult + ' ) ' ;

EXEC    SP_EXECUTESQL @FinalSQLCommand

Select allOpen in new window

vensali,

Cross Apply is very much like Where clause but it has lots to offer. After you work with Cross Apply, there is also Outer Apply both very wonderful and good syntax to work with.

Using Cross Apply, you do not need to hard code your criteria. You just pass a parameter to your function to produce a single column with filter items intended.

If you need help with the function, post a new question on how to build the function you need. The solution submitted earlier is tested with some mock data and it works well.

Mike