Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 230
  • Last Modified:

optional Parameter

The Stored Procedure works OK but I want to change the @ItemType to Optional.  If @ItemType is not specified or blank then All items are loaded regardless of type.

CREATE PROCEDURE sp_VCW_Load_All_Items
(
     @ItemType    varchar(20),
     @Admin         bit = false,
     @ShowOnHomePage    bit = False
)

with recompile    
AS

Declare @today char(20)
set @today=convert(char(20),getdate(),112)

SELECT [ID], Title, [Description], ImageFile
  FROM VCW_Items
 WHERE ItemType = @ItemType
  and  (  
     (@admin = 0 AND ShowUntil > getdate() AND Accepted = 1 AND Archive = 0 And ShowonHomePage = @ShowonHomePage) or (@admin = 1) or (@ShowonHomePage = 0))  


Return
GO
0
Kevin Robinson
Asked:
Kevin Robinson
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
CREATE PROCEDURE sp_VCW_Load_All_Items
(
     @ItemType    varchar(20) = NULL,
     @Admin         bit = false,
     @ShowOnHomePage    bit = False
)

with recompile    
AS

Declare @today char(20)
set @today=convert(char(20),getdate(),112)

SELECT [ID], Title, [Description], ImageFile
  FROM VCW_Items
 WHERE ( ItemType = @ItemType  OR @ItemType is null)
  and  (  
     (@admin = 0 AND ShowUntil > getdate() AND Accepted = 1 AND Archive = 0 And ShowonHomePage = @ShowonHomePage) or (@admin = 1) or (@ShowonHomePage = 0))  


Return
GO
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
CREATE PROCEDURE sp_VCW_Load_All_Items
(
     @ItemType    varchar(20) = NULL,
     @Admin         bit = false,
     @ShowOnHomePage    bit = False
)

with recompile    
AS

Declare @today char(20)
set @today=convert(char(20),getdate(),112)

SELECT [ID], Title, [Description], ImageFile
  FROM VCW_Items
 WHERE ItemType = ISNULL(@ItemType ,ItemType )   --- changed this line
  and  (  
     (@admin = 0 AND ShowUntil > getdate() AND Accepted = 1 AND Archive = 0 And ShowonHomePage = @ShowonHomePage) or (@admin = 1) or (@ShowonHomePage = 0))  


Return
GO

0
 
Kevin RobinsonPrivate VB.NET ContractorAuthor Commented:
Worked Great Thanks :))

CREATE PROCEDURE sp_VCW_Load_All_Items
(
     @ItemType    varchar(20) = NULL,
     @Admin         bit = false,
     @ShowOnHomePage    bit = False
)

with recompile    
AS

Declare @today char(20)
set @today=convert(char(20),getdate(),112)

SELECT [ID], Title, [Description], ImageFile
  FROM VCW_Items
WHERE ( ItemType = @ItemType  OR @ItemType ='')

  and  (  
     (@admin = 0 AND ShowUntil > getdate() AND Accepted = 1 AND Archive = 0 And ShowonHomePage = @ShowonHomePage) or (@admin = 1) or (@ShowonHomePage = 0))  


Return
GO
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Did u tried mine ?
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now