Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Table variable subquery as column Expand / Collapse
Author
Message
Posted Thursday, March 7, 2013 12:15 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, August 14, 2014 2:30 PM
Points: 38, Visits: 173
Hi All,

I'm trying to use a value from a table variable's column that matches w/a passed in variable as a subquery for one of the main query's column. I'm trying the below, but the values for @Region and @Queue are showing up as blank in a SSRS report field which are pulling from it:

ALTER PROCEDURE [dbo].[udp_WorkQueuesReport]
-- Add the parameters for the stored procedure here
@FromDt as date,
@ThruDt as date,
@Region as varchar(max),
@Queue as varchar(max)
AS
BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

Declare @RegionTbl as table(RegionName varchar(50))
Declare @QueueTbl as table(QueueName varchar(50))

Insert @RegionTbl
select Value from hpexprpt.dbo.split(@Region,',')

Insert @QueueTbl
select Value from hpexprpt.dbo.split(@Queue,',')

select
users.last_name + ',' + users.first_name as [User ID]
, (Select RegionName from @RegionTbl where RegionName = @Region) as Region
, (Select QueueName from @QueueTbl where QueueName = @Queue) as Queue
...
from hpexpprod.dbo.work_items join
...
where
...

Any pointers would be greatly appreciated...Thanks in advance!
Post #1428186
Posted Thursday, March 7, 2013 12:23 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:05 AM
Points: 40,258, Visits: 36,681
where RegionName = @Region


Where the split up region name is the same as the original concatenated list? Don't think that's quite what you want....
How are the split up region and queue names supposed to be related to the work_items table?



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1428188
Posted Monday, March 11, 2013 8:56 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, August 14, 2014 2:30 PM
Points: 38, Visits: 173
Hi GilaMonster,

Thanks for your feedback. The Region and Queue are actually parameters getting passed to the query which will pull the report. Both have corresponding id fields in the work_items table which is being joined with the corresponding tables and others. I think for now, I may be okay, but will check back in if I need further assistance. Thanks again!
Post #1429316
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse