SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Table variable subquery as column


Table variable subquery as column

Author
Message
daniness
daniness
SSC-Enthusiastic
SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)

Group: General Forum Members
Points: 172 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!
GilaMonster
GilaMonster
SSC Guru
SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)

Group: General Forum Members
Points: 224332 Visits: 46312
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, MVP, M.Sc (Comp Sci)
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


daniness
daniness
SSC-Enthusiastic
SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)

Group: General Forum Members
Points: 172 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! :-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search