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

declare @F dbo.Facilities;
Insert @F Values (1), (2), (3), (4), (5);

Create PROCEDURE dbo.GetImpDocsTwo
@facilities dbo.Facilities ReadOnly,
@facilitydivisionID tinyint = 6)
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)
  • 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. Apr 28, 2014 at 18:07
  • 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? Apr 28, 2014 at 18:28
  • 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. Apr 28, 2014 at 18:29
  • 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. Apr 28, 2014 at 18:37
  • Because I have only 5 facilities in the Facility table, the 6th record holds the Divitional information thats in the same table. Apr 28, 2014 at 18:41

1 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:

  @facilities dbo.Facilities ReadOnly,
  @facilitydivisionID tinyint = 6

  ;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
     ON tsl.TransmittalSiteLeadFacility = f.FacilityID;

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

  • 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). Apr 28, 2014 at 18:20
  • Ok i will do that Apr 28, 2014 at 18:29
  • 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. Apr 30, 2014 at 19:00
  • It seems SQLfiddle doesnt like my Schema building, so no luck yet creating my sample data there. Thanks for everything again. Apr 30, 2014 at 19:02
  • 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 May 7, 2014 at 0:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.