sub query with parameter?

Question

Hi Pros, 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

Answer 1

at the very top of the query

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

Answer 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

Answer 3

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 - isissuenonissue) as [NonIssue]
from vw_Tickets 
where LastTicketStatusID = @LastTicketStatusID