decoder
decoder

Reputation: 926

SQL Server Express stored procedure condition checking

I have written a stored procedure:

ALTER PROCEDURE [dbo].[GetBRs_Pager]
    @PageIndex INT
   ,@PageSize INT
   ,@SupId INT
   ,@RecordCount INT OUTPUT
AS
BEGIN

SET NOCOUNT ON;

SELECT ROW_NUMBER() 
OVER (
    ORDER BY [tblBR].[ID] ASC
) AS RowNumber

,[tblBR].[ID]
,[tblBR].[BRName]
,[tblBR].[SupervisorId]
,[tblSupervisor].[SupervisorName]
,[tblBR].[BRCode]
,[tblBR].[BRMobile]
,[tblBR].[BRTypeId]
,[tblType].[TypeName]
,[tblBR].[BRImageUrl]

INTO #Results
FROM [tblBR]

   INNER JOIN [tblType] 
   ON [tblBR].[BRTypeId] = [tblType].[ID] 

   INNER JOIN [tblSupervisor] 
   ON [tblBR].[SupervisorId] = [tblSupervisor].[ID]


where [tblBR].[Active] = 1
and [tblBR].[SupervisorId]=@SupId

SELECT @RecordCount = COUNT(*)

FROM #Results

SELECT * FROM #Results

WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1

DROP TABLE #Results

END

Now I want to modify the query as follows

if(@supId != 0) then where [tblBR].[Active] = 1
and [tblBR].[SupervisorId] = @SupId else [tblBR].[Active] = 1 

How to do it? Anyone helps me is greatly appreciated. Thanks in advance.

Upvotes: 0

Views: 74

Answers (2)

Raphael
Raphael

Reputation: 1687

What I would do is the following thing, adding the following code after your where clauses

AND [tblBR].[Active] = 1
AND [tblBR].[SupervisorId] = CASE WHEN @supId != 0 THEN @supId ELSE [tblBR].[SupervisorId] END

the second and check if the variable if different than 0, in that case it filter the supervisorid with your variable, if equal 0 it filter the supervisorid field by his row value.

Upvotes: 0

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239664

Just basic logic, I think:

where [tblBR].[Active] = 1

and (
       [tblBR].[SupervisorId]=@SupId or
       @SupId = 0
    )

You don't need control-flow statements or the like here. You just need to express the alternatives and link them together using boolean operators.

Upvotes: 1

Related Questions