Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
    
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. –  Aaron Bertrand Apr 28 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? –  Shayma Ahmad Apr 28 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. –  Aaron Bertrand Apr 28 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. –  Aaron Bertrand Apr 28 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. –  Shayma Ahmad Apr 28 at 18:41

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer
    
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 Apr 28 at 18:20
    
Ok i will do that –  Shayma Ahmad Apr 28 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. –  Shayma Ahmad Apr 30 at 19:00
    
It seems SQLfiddle doesnt like my Schema building, so no luck yet creating my sample data there. Thanks for everything again. –  Shayma Ahmad Apr 30 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 –  Shayma Ahmad May 7 at 0:01

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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