Declaring a scalar variable that its a table valued parameter?

Question

I get error 137 that I must declare a scalar variable (@facilities), although I'm declaring it in the Create stored procedure as a readonly variable, so I wasn't sure what I'm missing here. any advice will be appreciated. I created a table valued parameter to hold multipple integer values, that I can call later in my stored procedure. the goal is to get all the documents implementing in each facility for the one division (6) for a specific date. Please see a section of the code below:

use DevDocMgt_new
CREATE TYPE dbo.Facilities AS TABLE
(FacilityID TINYINT PRIMARY KEY);
go
declare @F dbo.Facilities;
Insert @F Values (1), (2), (3), (4), (5);
go
Create PROCEDURE dbo.GetImpDocsTwo
(
@facilities dbo.Facilities ReadOnly,
@facilitydivisionID tinyint = 6)
AS
BEGIN
SET NOCOUNT ON; 
SELECT di.DocumentNumber, di.DocumentVersion, di.DocumentTitle, 
 ts.TransmittalNumber, ts.TransmittalVersion,
 CASE WHEN TransmittalSiteLeadImpDate IS NOT NULL 
 AND (TransmittalSiteLeadFacility = @facilities OR TransmittalSiteLeadFacility = @facilityDivisionID) 
 THEN TransmittalSiteLeadImpDate ELSE TransmittalImpDate END AS FacilityImpDate
FROM dbo.DocumentInfo di
 INNER JOIN dbo.TransmittalSheet ts ON di.DocumentTransmittalImp = ts.TransmittalID
 LEFT OUTER JOIN dbo.TransmittalSiteLead tsl ON ts.TransmittalID = tsl.TransmittalSiteLeadTSID
 Inner Join @facilities as F on f.facilityID = tsl.TransmittalSiteLeadFacility
-- Select documents implementing in the facility
WHERE (tsl.TransmittalSiteLeadFacility = @facilities OR 
 tsl.TransmittalSiteLeadFacility = @facilitydivisionID)
end

• Aaron Bertrand: The two places where you're comparing a column to a table aside, this query seems a lot more complex than it needs to be. Also do realize that when you say LEFT OUTER JOIN then you use a column from that outer join in an INNER JOIN, you turn that OUTER JOIN to an INNER JOIN. I would offer to re-write the query for you but you will need to clarify the actual requirements. Not possible for me to understand those from the code you've written.
• Shayma Ahmad: I would really appreciate it as I'm staring at the old working stored procedure and compare it to the changes I made so far, no luck yet as far as why I'm not getting data back. The goal is to be able to generate the report that has all the docs implemented for all facilities at once (not by choosing only one facility at a time), I mean facilityID should returen results for 1+2+3+4+5 during a specific date range, do you mean you need to know the type of the relationships for these associated tables?
• Aaron Bertrand: Please create some sample tables with sample data (including data you don't want in the results) on SQLfiddle, and describe the results you expect. I can't troubleshoot code I can't see with requirements I don't understand, and neither can anyone else on this site.
• Aaron Bertrand: Also, why don't you just put 6 into @f as well, instead of complicating things and treating that separately? When all it really is is an additional facility to filter on.
• Shayma Ahmad: Because I have only 5 facilities in the Facility table, the 6th record holds the Divitional information thats in the same table.

Answer 1

The problem is here:

WHERE (tsl.TransmittalSiteLeadFacility = @facilities

You are trying to compare a column on the left to a table on the right. This is like saying:

FROM sys.objects
WHERE object_id = sys.columns

...which obviously makes no sense.

That where clause doesn't belong there at all. You've already ensured that the facilities listed in the incoming table-valued parameter are matched to TransmittalSheet by using it in the JOIN clause. There is no need to filter on that match again.

My guess after several rounds of teeth-pulling is you want this:

CREATE PROCEDURE dbo.GetImpDocsThree
 @facilities dbo.Facilities ReadOnly,
 @facilitydivisionID tinyint = 6
AS
BEGIN
 SET NOCOUNT ON; 
 ;WITH f AS
 (
 SELECT FacilityID FROM @faclities
 UNION ALL SELECT @facilitydivisionID
 ) 
 SELECT di.DocumentNumber, di.DocumentVersion, di.DocumentTitle, 
 ts.TransmittalNumber, ts.TransmittalVersion,
 FacilityImpDate = COALESCE(ts.TransmittalSiteLeadImpDate, ts.TransmittalImpDate)
 FROM dbo.DocumentInfo AS di
 INNER JOIN dbo.TransmittalSheet AS ts 
 ON di.DocumentTransmittalImp = ts.TransmittalID
 INNER JOIN f 
 ON tsl.TransmittalSiteLeadFacility = f.FacilityID;
END
GO

Or just insert 6 into the TVP up front too, and ditch the additional single facility variable altogether.

• Shayma Ahmad: Now the stored procedure is compiling without any error but I dont get any data back. The actual working sp was producing data from joining an existing Facility table, TransmittalSheet table, DocumentInfo and TransmittalSiteLead tables with date range variables, that had also one variable facilityID that points to one facility at a time. So now that I created the TVP dbo.Facilities, it doesnt have other information inserted other than the facility Id numbers (1,2,3,4,5).
• Shayma Ahmad: Ok i will do that
• Shayma Ahmad: Awsome, your third stored procedure above (GetImpDocsThree) returns rows for me now but I just have to get it to work with date range variables so I can tell its pulling the correct rows I need. For some reason when I add the "Start and End" variables, still doesn't like it but I'm working on it.
• Shayma Ahmad: It seems SQLfiddle doesnt like my Schema building, so no luck yet creating my sample data there. Thanks for everything again.
• Shayma Ahmad: Can you please check my link for a sample database sqlfiddle.com/#!3/b2643/5/1 , as I entered few rows for each table, hopefully it's helpful. I will submit a new question for this too. Thanks