Selection variable or variable to select all records 1

[traveller4]

I there a way to pass a variable to limit the criteria and select all records when the user doesn't pick any criteria.

I have a variable called @strFiscal and I would like it to provide a limited criteria based on the parameter being passed to the variable or have all records selected if the user pick no criteria.

Here is part of my code:

ALTER PROCEDURE USP_SLATracking
(
@strFiscal varchar (10)
)

AS

DECLARE @tblSLA_Tracking TABLE
(
[T_InvoiceID] [varchar] (20),
[FileID] [varchar] (20),
[DateTrans] [Datetime] NULL ,
[Role] [varchar] (50),
[MinistryID] [varchar] (20),
[MinistryName] [varchar] (100),
[FiscalYear] [char] (10),
[AnnualHours] [numeric](10, 4)
)


INSERT INTO @tblSLA_Tracking
(
[T_InvoiceID],
[DateTrans],
[Role],
[MinistryID],
[MinistryName],
[FiscalYear],
[AnnualHours]
)
SELECT
C_InvoiceID,
DateTrans,
Role,
MinistryID,
MinistryName,
FiscalYear,
AnnualHours
FROM tblD_Invoice
WHERE (TypeSlipID = 'T')
AND (AnnualHours <> 0)
AND (FiscalYear = @strFiscal)


Do I have to declare the variable:

@strFiscal varchar (10)

a different way??

--Any help would be appreciated
--Thanks in advance

 

[nigelrivett]

leave it as it is and to cater for no criteria

WHERE TypeSlipID = 'T'
AND AnnualHours <> 0
AND (FiscalYear = @strFiscal or @strFiscal is null)

(get rid of the unnecessaryt brackets - makes it difficult to read).

For something other than null
WHERE TypeSlipID = 'T'
AND AnnualHours <> 0
AND (FiscalYear = @strFiscal or @strFiscal = 'ALL')


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[traveller4]

Thanks for the help works great