CODEWITHSUNDEEP
sqlsql-serverstored-proceduresdatabase-administration
user2274204
user2274204

Reputation: 345

How to return all values if Param is null

I want to customize this stored procedure, That in case That param value @TownId is null returns all the data in the table. otherwise returns the correct data. In my table there is no nulls in the TownId column. there is nvarchar values. i didn't success to get all the data if i send @TownId=null from the client side 

ALTER PROCEDURE dbo.GetCustomersPageWiseWithTown
@PageIndex INT = 1
  ,@PageSize INT = 10
  ,@PageCount INT OUTPUT
  ,@TownId int
AS
   SET NOCOUNT ON;
  SELECT ROW_NUMBER() OVER
        (
              order by IsPaid desc
        )AS RowNumber
  ,b.Id
  ,b.Name
  ,b.Phone1
  ,b.Town
  ,b.Addess
  ,b.IsPaid
  ,b.DefaultImage
  ,t.TownName


INTO #Results
  FROM BusinessFullData b


  Left JOIN Towns t ON b.Town = t.Id 
   where  ((b.IsVisable=1) and (b.Town=@TownId))
  DECLARE @RecordCount INT
  SELECT @RecordCount = COUNT(*) FROM #Results

  SET @PageCount = CEILING(CAST(@RecordCount AS DECIMAL(10, 2)) / CAST(@PageSize AS DECIMAL(10, 2)))
  PRINT       @PageCount

  SELECT * FROM #Results
  WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1

  DROP TABLE #Results
RETURN

Thanks a lot for advise,

Upvotes: 0

Views: 120

Answers (2)

M.Ali
M.Ali

Reputation: 69524

A better way of dealing with optional parameters is something like this...

ALTER PROCEDURE dbo.GetCustomersPageWiseWithTown
 @PageIndex INT = 1
,@PageSize  INT = 10
,@PageCount INT OUTPUT
,@TownId    INT
AS
BEGIN
   SET NOCOUNT ON;

DECLARE @Sql NVARCHAR(MAX);   
DECLARE @RecordCount INT;

SET @Sql = N'   
  SELECT 
    ROW_NUMBER() OVER (order by IsPaid desc)AS RowNumber
  ,b.Id
  ,b.Name
  ,b.Phone1
  ,b.Town
  ,b.Addess
  ,b.IsPaid
  ,b.DefaultImage
  ,t.TownName
               INTO #Results
  FROM BusinessFullData b
  Left JOIN Towns t ON b.Town = t.Id 
  where  b.IsVisable = 1 '
  + CASE WHEN @TownId IS NOT NULL 
   THEN N' and  b.Town = @TownId ' ELSE N'' END

  + N' SELECT @RecordCount = COUNT(*) FROM #Results

  SET @PageCount = CEILING(CAST(@RecordCount AS DECIMAL(10, 2)) / CAST(@PageSize AS DECIMAL(10, 2)))

  SELECT * FROM #Results
  WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1'

Exec sp_executesql @Sql 
                  ,N'@PageIndex INT , @PageSize INT, @TownId int, 
                     @RecordCount INT, @PageCount INT OUTPUT'
                  ,@PageIndex
                  ,@PageSize
                  ,@TownId
                  ,@PageCount OUTPUT

END

Upvotes: 0

D Stanley
D Stanley

Reputation: 152566

Just add that condition to your WHERE clause:

                             V-------------V
where  ((b.IsVisable=1) and (@TownId IS NULL OR b.Town=@TownId))

Also note that IsVisible is spelled wrong.

Upvotes: 2

Related Questions