Table variable subquery as column

  • 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!

  • 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
  • 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! 🙂

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply