CODEWITHSUNDEEP
sql-server-2008stored-procedures
angelogogo
angelogogo

Reputation: 723

stored procedure returns result with 2 params only

hi guys below is my code

USE [arrestedpersonsdb]
GO
/****** Object:  StoredProcedure [dbo].[stnencodedtodisplay]    Script Date: 08/11/2013 11:18:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[stnencodedtodisplay]
(
      @PageIndex INT = 1
      ,@PageSize INT = 10
      ,@RecordCount INT OUTPUT 
      ,@id int
      ,@fname varchar
      ,@lname varchar
    )
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here

      SELECT ROW_NUMBER() OVER
      (
            ORDER BY [fname] ASC
      )AS RowNumber
      ,[pid]
      ,[fname]
      ,[mname]
      ,[lname]
      ,[qualifier]
      ,[alias]

      INTO #Results
      FROM [todisplay]
      where (stnid = @id) and (type = 'STN')  and (fname = @fname or @fname = '') and (          lname = @lname or @lname = '')
      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

whats odd is that when the @fname and @lname is null it returns the expected result but when i try to pass a parameter on the @fname or @lname it returns nothing

sample below

USE [arrestedpersonsdb]
GO

DECLARE @return_value int,
        @RecordCount int

EXEC    @return_value = [dbo].[stnencodedtodisplay]
        @PageIndex = 1,
        @PageSize = 10,
        @RecordCount = @RecordCount OUTPUT,
        @id = 1599,
        @fname = 'ALDRIN',
        @lname = ''

SELECT  @RecordCount as N'@RecordCount'

SELECT  'Return Value' = @return_value

GO
enter code here

but when i pass the 'ALDRIN' as a parameter for the @fname it returns zero is there something wrong with my syntax?

Upvotes: 0

Views: 94

Answers (3)

Sithelo Ngwenya
Sithelo Ngwenya

Reputation: 501

I might be late, but another optionto solve the issue is to try this:

SELECT ROW_NUMBER() OVER
  (
        ORDER BY [fname] ASC
  )AS RowNumber
  ,[pid]
  ,[fname]
  ,[mname]
  ,[lname]
  ,[qualifier]
  ,[alias]

  INTO #Results
  FROM [todisplay]
  where stnid = @id 
    and type = 'STN'  
    AND CASE WHEN  @fname =''
          THEN 'True' 
          ELSE fname 
            END  = CASE WHEN   @fname ='' 
                    THEN 'True' 
                    ELSE @fname 
            END 
    AND CASE WHEN  @lname =''
          THEN 'True' 
          ELSE lname 
            END  = CASE WHEN   @lname ='' 
                    THEN 'True' 
                    ELSE @lname 
            END

In your current try setting SET ANSI_NULLS OFF.

Hope it helps.

Upvotes: 0

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

You have not specified the size of the varchar parameters.

  ,@fname varchar
  ,@lname varchar

Not doing so will give you a size of one.

  ,@fname varchar(1)
  ,@lname varchar(1)

Change to whatever is appropriate in your case.

  ,@fname varchar(100)
  ,@lname varchar(100)

Upvotes: 2

Hogan
Hogan

Reputation: 70523

This might be a caps issue. If so then the following would fix it:

(upper(fname) = upper(@fname) or @fname = '') and 
(upper(lname) = upper(@lname) or @lname = '')

or it could be a white space issue in which case the following would fix it

(rtrim(ltrim(fname)) = rtrim(ltrim(@fname)) or @fname = '') and 
(rtrim(ltrim(lname)) = rtrim(ltrim(@lname)) or @lname = '')

or both.

Upvotes: 0

Related Questions