Dynamic stored procedure

[emozley]

Hi,

I have a stored procedure that allows me to page through my database - at the moment it accepts the page number you want to view and the number of results to show:

USE [Tenders]
GO
/****** Object:  StoredProcedure [dbo].[GetTenders]    Script Date: 12/09/2010 13:52:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetTenders] 
@PageNum INT, @PageSize INT

AS

WITH TendersRN AS
(
    SELECT ROW_NUMBER() OVER(ORDER BY Title) AS RowNum,
          Tenders.TenderID, 
          Tenders.Title, 
          bwbnet.dbo.bwbnet_Users.FirstName, 
          bwbnet.dbo.bwbnet_Users.Surname, 
    	  Tenders.TenderDate,
          Tenders.TenderResult
      FROM Tenders
LEFT JOIN bwbnet.dbo.bwbnet_Users ON Tenders.AuthorID=bwbnet.dbo.bwbnet_Users.UserID
)

SELECT * 
  FROM TendersRN
 WHERE RowNum BETWEEN (@PageNum - 1) * @PageSize + 1 
                  AND @PageNum * @PageSize
 ORDER BY Title;

The 'Tenders' table contains two other fields SectorID and SubSectorID. I would like to modify the stored procedure so that if it gets a value for SectorID passed to it that isn't 0 (zero) then it will add WHERE Tenders.SectorID = ... and if you pass a value for SubSectorID to it that isn't value then it would use that as well.

e.g. EXEC GetTenders '1', '10', '3', '18'

would make the procedure execute a query

SELECT .... FROM Tenders WHERE Tenders.SectorID=3 AND Tenders.SubSectorID=18

but EXCE GetTenders '1', '10', '3', '0' would give

SELECT .... FROM Tenders WHERE Tenders.SectorID=3

Am a bit stuck so any help would be very grateful for some pointers!

Thanks very much

Ed

 

[gmmastros]

ALTER PROCEDURE [dbo].[GetTenders] 
@PageNum INT, @PageSize INT, [!]@SectorId INT = 0, @SubSectorId INT = 0[/!]

AS

WITH TendersRN AS
(
    SELECT ROW_NUMBER() OVER(ORDER BY Title) AS RowNum,
           Tenders.TenderID, 
           Tenders.Title, 
           bwbnet.dbo.bwbnet_Users.FirstName, 
           bwbnet.dbo.bwbnet_Users.Surname, 
           Tenders.TenderDate,
           Tenders.TenderResult
      FROM Tenders
           LEFT JOIN bwbnet.dbo.bwbnet_Users 
              ON Tenders.AuthorID=bwbnet.dbo.bwbnet_Users.UserID
[!]     WHERE (Tenders.SectorID = @SectorID Or @SectorId = 0)
           And (Tenders.SubSectorId = @SubSectorId Or @SubSectorId = 0)[/!]
)

SELECT * 
  FROM TendersRN
 WHERE RowNum BETWEEN (@PageNum - 1) * @PageSize + 1 
                  AND @PageNum * @PageSize
 ORDER BY Title;

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom