sub query with parameter?

Question

I'd like to ask help on this small query of mine. Im doing a query and a sub query on my sub query i want to have it parameterized. Is there a way to do it? Please see my query script.

select sum(issue) as [Issue], sum(nonissue) as [NonIssue]
from
(
AS
    select
        case when isissue = 1 then 1 else 0 end as 'issue',
        case when isissue = 0 then 1 else 0 end as 'nonissue',
        LastTicketStatusID
    from
        vw_Tickets
    where
        LastTicketStatusID = @LastTicketStatusID
) 
as Tickets

I always got an error Must declare the table variable "@LastTicketStatusID". where should i declare the parameter?

Thanks, Nhoyti

Please clarify - do you want @LastTicketStatusID to come from the outer query or from the code that is executing your command batch? — Christian Hayter
– Christian Hayter, Commented Jul 8, 2009 at 16:12
What's the AS after the open parenthesis after the first FROM? — Jonathan Leffler
– Jonathan Leffler, Commented Jul 8, 2009 at 16:35
I'm interested to know if there's an answer how to do this with a value from the outer query, one of the possibilities @Christian posed. — zanlok
– zanlok, Commented Dec 13, 2010 at 18:50

Casey Williams · Accepted Answer · 2009-07-08 16:07:24Z

2

If this is for a stored procedure...

CREATE PROCEDURE [dbo].[ProcedureName]
    @LastTicketStatusID INT
AS 

select
sum(issue) as [Issue], 
sum(nonissue) as [NonIssue]
from (
    select
    case when isissue = 1
        then 1 else 0 end as 'issue',
    case when isissue = 0
        then 1 else 0 end as 'nonissue',
    LastTicketStatusID
from vw_Tickets 
where LastTicketStatusID = @LastTicketStatusID ) as Tickets

Otherwise

DECLARE @LastTicketStatusID INT
SELECT @LastTicketStatusID = yourDesiredID

answered Jul 8, 2009 at 16:07

Casey Williams

4,0912 gold badges27 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Shannon Severance · Accepted Answer · 2010-12-13 22:09:45Z

1

Not pertinant to your question, but assuming that vw_Tickets.isissue is a bit field (or otherwise constrained to values of zero or one.) the inline query can be removed to simplify Launchy's answer:

select sum(isissue) as [Issue], 
    sum(1 - isissue) as [NonIssue]
from vw_Tickets 
where LastTicketStatusID = @LastTicketStatusID

edited Dec 13, 2010 at 22:09

answered Jul 8, 2009 at 17:29

Shannon Severance

18.5k4 gold badges49 silver badges67 bronze badges

1 Comment

zanlok Over a year ago

exactly; I'm not a fan of unnecessary subqueries

JuniorFlip · Accepted Answer · 2009-07-08 16:01:07Z

0

at the very top of the query

Declare @LastTicketStatusID int
set @lastTicketStatusID = ####

answered Jul 8, 2009 at 16:01

JuniorFlip

4172 gold badges7 silver badges17 bronze badges

Collectives™ on Stack Overflow

sub query with parameter?

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related