Sql qry not returning the desired reuslt

Select * from his.dbo.PREG P where  P.pg_regno in('02693150','02120832','01509522')  returns 3 rows

where as  the qry,  Select * from his.dbo.PREG P where  P.pg_regno in (Select hospnolist from [dbo].fnGetHospitalnolist('02693150'))returns 0 rows .



But Select  hospnolist from [dbo].fnGetHospitalnolist('02693150') returns below mentioned output

          '02693150','02120832','01509522'

Where am i going wrong?
vensaliAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anoo S PillaiCommented:
Is the function fnGetHospitalnolist a table valued function / scalar function ?
Could you please post the funtion code ?
PortletPaulEE Topic AdvisorCommented:
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
Mike EghtebasDatabase and Application DeveloperCommented:
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

vensaliAuthor Commented:
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.
Anoo S PillaiCommented:
Reason:- the function returns a SINGE string with value "'02693150','02120832','01509522'"

Select * from tableA where column1 IN ( 'A', 'B', 'C' )

is different from

DECLARE @Param VARCHAR(100)
SET     @Param = '''A'', ''B'', ''C'''
SELECT @Param
Select * from tableA where column1 IN ( @Param )  (  this is equivalent to Select * from tableA where column1 = @Param )


if that function returns three rows instead, the equivalent select would be
Select *
from   tableA
where column1 IN ( SELECT  'A'
                                     union
                                     SELECT  'B'
                                     union
                                     SELECT  'C  )

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulEE Topic AdvisorCommented:
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.
vensaliAuthor Commented:
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.
Anoo S PillaiCommented:
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

Open in new window

Mike EghtebasDatabase and Application DeveloperCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.