CODEWITHSUNDEEP
sqlparameterssubquery
nhoyti
nhoyti

Reputation:

sub query with parameter?

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

Upvotes: 1

Views: 2514

Answers (3)

Shannon Severance
Shannon Severance

Reputation: 18410

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

Upvotes: 1

Casey Williams
Casey Williams

Reputation: 4073

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

Upvotes: 2

JuniorFlip
JuniorFlip

Reputation: 417

at the very top of the query

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

Upvotes: 0

Related Questions