November 29, 2011 at 10:22 am
I have a stored procedure like:
Decalare @WebServiceGetResults XML = NULL
DECLARE @BroadViewData TABLE
(
FundingBedId INT,
EpisodeId INT,
EpisodePackageFormatId INT,
EpisodePackageId INT
)
:With StagingData AS
(
SELECT
NULLIF(T.Item.value('TABID[1]', 'VARCHAR(MAX)'), '') AS TABID,
NULLIF(T.Item.value('VER_ID[1]', 'VARCHAR(MAX)'), '') AS
VER_ID,
NULLIF(T.Item.value('VER_ASS_ID[1]', 'VARCHAR(MAX)'), '') AS
VER_ASS_ID,
NULLIF(T.Item.value('PACKAGE_TYPE[1]', 'VARCHAR(MAX)'), '')
AS PACKAGE_TYPE
FROM
WebServiceGetResults.nodes('*') AS T(Item)
)
INSERT INTO @BroadViewData
(
FundingBedId,
EpisodeId,
EpisodePackageFormatId,
EpisodePackageId
)
SELECT
fb.FundingBedId,
e.EpisodeId,
epf.EpisodePackageFormatId,
pkg.EpisodePackageId
FROM
StagingData stag
JOIN PackagingForms.dbo.FundingBed fb
ON fb.BroadViewId = stag.tabid
JOIN PackagingForms.dbo.Episode e
ON e.BroadViewId = stag.ver_ass_id
JOIN PackagingForms.dbo.lkEpisodePackageFormat epf
ON epf.BroadViewId = stag.package_type
LEFT JOIN PackagingForms.dbo.EpisodePackage pkg
ON pkg.BroadViewId = stag.ver_id
I expect the staging data not to have any records.
Also
fb.BroadViewId = null
Would the above statement still fill the @broadview Table with data from other tables?
November 29, 2011 at 10:40 am
Faye Fouladi (11/29/2011)
I have a stored procedure like:Decalare @WebServiceGetResults XML = NULL
DECLARE @BroadViewData TABLE
(
FundingBedId INT,
EpisodeId INT,
EpisodePackageFormatId INT,
EpisodePackageId INT
)
:With StagingData AS
(
SELECT
NULLIF(T.Item.value('TABID[1]', 'VARCHAR(MAX)'), '') AS TABID,
NULLIF(T.Item.value('VER_ID[1]', 'VARCHAR(MAX)'), '') AS
VER_ID,
NULLIF(T.Item.value('VER_ASS_ID[1]', 'VARCHAR(MAX)'), '') AS
VER_ASS_ID,
NULLIF(T.Item.value('PACKAGE_TYPE[1]', 'VARCHAR(MAX)'), '')
AS PACKAGE_TYPE
FROM
WebServiceGetResults.nodes('*') AS T(Item)
)
INSERT INTO @BroadViewData
(
FundingBedId,
EpisodeId,
EpisodePackageFormatId,
EpisodePackageId
)
SELECT
fb.FundingBedId,
e.EpisodeId,
epf.EpisodePackageFormatId,
pkg.EpisodePackageId
FROM
StagingData stag
JOIN PackagingForms.dbo.FundingBed fb
ON fb.BroadViewId = stag.tabid
JOIN PackagingForms.dbo.Episode e
ON e.BroadViewId = stag.ver_ass_id
JOIN PackagingForms.dbo.lkEpisodePackageFormat epf
ON epf.BroadViewId = stag.package_type
LEFT JOIN PackagingForms.dbo.EpisodePackage pkg
ON pkg.BroadViewId = stag.ver_id
I expect the staging data not to have any records.
Also
fb.BroadViewId = null
Would the above statement still fill the @broadview Table with data from other tables?
Not exactly sure what your question is here but I think you are asking if the table will be filled with data from other tables when the first inner join returns nothing? You have coded your first join as an inner join. That means that it will ONLY return records where there is a match. If in doubt, try running it.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 29, 2011 at 10:46 am
No, this should not put any records in the table variable, the way it's written, if the CTE returns zero rows.
If you want it to return data from other tables, you'll need to conver to Outer Joins, instead of the default Inner Joins that you're using.
If it's returning data and you aren't expecting it to, then test the query in the CTE and see if it really is returning zero rows.
I can't tell if you're getting rows and don't want them, or not getting rows and do want them, so I can't get more specific than that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 29, 2011 at 12:01 pm
Thanks
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply