what is listtotable? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

what is listtotable?

Author

Topic

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2011-10-18 : 14:41:03

Hi - in the following sp, what does listtotable do? it is the last line here/

ALTER PROCEDURE [dbo].[GET_MONTHLY_SCHEDULES]
@FACILITYKEY varchar(1000)
AS
BEGIN
SET NOCOUNT ON;
SELECT COUNT(*) AS [COUNT] FROM (
(SELECT S.FACILITY_KEY, S.PAT_NUMBER, S.A3A_DATE_USER, M.REFERENCE_DATE FROM [OPTC].MDS_M_SCHEDULE S, OPTC.MD3_M_MAST M
WHERE S.PAT_NUMBER = M.PAT_NUMBER
AND M.REFERENCE_DATE < GETDATE()) --Started (late schedule)
UNION ALL
(SELECT S.FACILITY_KEY, S.PAT_NUMBER, S.A3A_DATE_USER, NULL AS REFERENCE_DATE FROM TC.DS_M_SCHEDULE S
WHERE S.PAT_NUMBER NOT IN (SELECT M.PAT_NUMBER FROM [TC].M3_M_MAST M)
AND S.A3A_DATE_USER < GETDATE() ) --Not Started (late schedule)
) LATE WHERE
FACILITY_KEY IN (SELECT Value FROM dbo.ListToTable(@FACILITYKEY,','))
END

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-10-18 : 14:45:20

It's a user-defined function that returns a table from a CSV value.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-19 : 05:07:33

it parses the values passed as comma seperated in @FACILITYKEY parameter and returns it as a table of values

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sqlmaster555112
Starting Member

13 Posts

Posted - 2011-10-19 : 13:11:16

It returns values with ',' in the values of @facilitykey..........thats it nothing more nothing less utility to this ListToTable ,alos it is valid for both the tables in unionall query.Hope its helpful.thanks
Do Reply

@sqlmaster

http://www.intelcs.com/SQL_Server_Consultant.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources