March 7, 2013 at 12:15 pm
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!
March 7, 2013 at 12:23 pm
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
March 11, 2013 at 8:56 am
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy