T SQL Problem

what about this, assuming you use sql 2005

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[uspGetEmployee]
 @FilterType [nvarchar](50),
 @FilterValue[nvarchar](50),
 @startRowIndex int,
 @maxRows int,
 @totalRows int out
   
AS
BEGIN
    SET NOCOUNT ON;
 
    DECLARE @startRow2 int, @endrow2 int
    SET @startRow2 = ((@startRowIndex - 1) * @maxRows) + 1
    SET @endrow2 = @startRowIndex  * @maxRows
                  
     EXEC('
   SELECT * 
    FROM ( 
     SELECT TOP '  + cast (@startRowIndex * @maxRows as varchar(10) ) + ' EmployeeID, FirstName, LastName, Organization 
          , ROW_NUMBER() OVER (ORDER BY EmployeeID) R
     FROM Employee
     WHERE 1=1 AND ' + @FilterType + ' like ''%' +@FilterValue + '%''
     ORDER BY EmployeeID
         ) sq
    WHERE sq.r BETWEEN ' + cast(@startrow2 as varchar(10)) + ' AND ' + cast(@endrow2 as varchar(10)) + '
     ') 
             
END

Select allOpen in new window

you may face error in above solution, try this one.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[uspGetEmployee]
 @FilterType [nvarchar](50),
 @FilterValue[nvarchar](50),
 @startRowIndex int,
 @maxRows int,
 @totalRows int out
   
AS
BEGIN
    SET NOCOUNT ON;
 
    DECLARE @startRow2 VARCHAR(10), @endrow2 VARCHAR(10)
	DECLARE @Top varchar(10)
    SET @startRow2 = CONVERT(VARCHAR(10),((@startRowIndex - 1) * @maxRows) + 1)
    SET @endrow2 =CONVERT(VARCHAR(10), @startRowIndex  * @maxRows);
	SET @Top=convert(varchar(10),@startRowIndex * @maxRows)
	
                  
     EXEC('
   SELECT * 
    FROM ( 
     SELECT TOP '  + @TOP + ' EmployeeID, FirstName, LastName, Organization 
          , ROW_NUMBER() OVER (ORDER BY EmployeeID) R
     FROM Employee
     WHERE 1=1 AND ' + @FilterType + ' like ''%' +@FilterValue + '%''
     ORDER BY EmployeeID
         ) sq
    WHERE sq.r BETWEEN ' + @startrow2  + ' AND ' + @endrow2 + '
     ') 
             
END

Select allOpen in new window

Hi guys,
Thanks for the code.Yes i  am using sql server 2005 .you guys seems to changed the logic of my stored proc. but i  should pass back @totalRows to the application . My logic has no problem accept the syntax errors at run time. because my stored proc looks like below. the one within ' if ' condition is working perfectly fine.only the one within ' else ' is breaking. because when i  plug in your new code my application breaks.so i want to keep my old logic .wondering if you guys can tweak the syntax a littel bit ,but keep the same logic ...

my complete SP looks like below

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[uspGetEmployee]
 @FilterType [nvarchar](50),
 @FilterValue[nvarchar](50),
 @startRowIndex int,
 @maxRows int,
 @totalRows int out
   
AS
BEGIN
    SET NOCOUNT ON;

       
      if @FilterType = '' or @FilterValue = ''

        BEGIN  
               DECLARE @firstID1 int, @startRow1 int
                     SET @startRow1 = ((@startRowIndex - 1) * @maxRows) + 1
                   SET ROWCOUNT @startRow1
                        
                        SELECT @firstID1 = EmployeeID FROM Employee ORDER BY EmployeeID

                        SET ROWCOUNT @maxRows

                        SELECT  FirstName, LastName, Organization,Disabled FROM Employee
                        WHERE EmployeeID >= @firstID1

                        SET ROWCOUNT 0

                        SELECT @totalRows = COUNT(EmployeeID) FROM Employee
                  END    
                  
      else
              BEGIN      
                        DECLARE @firstID2 int, @startRow2 int
                        SET @startRow2 = ((@startRowIndex - 1) * @maxRows) + 1
                        SET ROWCOUNT @startRow2      
                  
                        EXEC('SELECT ' + @firstID2 + ' = EmployeeID FROM Employee where ' + @FilterType + ' like ''%' +@FilterValue + '%'' ORDER BY EmployeeID')

                        SET ROWCOUNT @maxRows

                        EXEC('SELECT EmployeeID, FirstName, LastName, Organization FROM Employee
                        WHERE EmployeeID >= ' + @firstID2 + ' and ' + @FilterType + ' like ''%' +@FilterValue + '%''')

                        SET ROWCOUNT 0

                         EXEC('SELECT ' + @totalRows + ' = COUNT(EmployeeID) FROM Products where ' + @FilterType + ' like ''%' +@FilterValue + '%''')
            END
                  
END

can you try one thing? store your dynamic SELECT statement in ELSE part in one variable and use that variable to execute from

EXEC SP_EXECUTESQL @YourDynamicVariableWithSELECTstatement

may i know how to write this EXEC statement please

EXEC('SELECT ' + @totalRows + ' = COUNT(EmployeeID) FROM Products where ' + @FilterType + ' like ''%' +@FilterValue + '%''')

(Note : @totalRows is declared as int which is my return value)

can you try this one?

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
alter PROCEDURE [dbo].[uspGetEmployee]
 @FilterType [nvarchar](50),
 @FilterValue[nvarchar](50),
 @startRowIndex int,
 @maxRows int,
 @totalRows int out
   
AS
BEGIN
    SET NOCOUNT ON;
 
       
      if @FilterType = '' or @FilterValue = ''
 
        BEGIN  
               DECLARE @firstID1 int, @startRow1 int
                     SET @startRow1 = ((@startRowIndex - 1) * @maxRows) + 1
                   SET ROWCOUNT @startRow1
                        
                        SELECT @firstID1 = EmployeeID FROM Employee ORDER BY EmployeeID
 
                        SET ROWCOUNT @maxRows
 
                        SELECT  FirstName, LastName, Organization,Disabled FROM Employee
                        WHERE EmployeeID >= @firstID1
 
                        SET ROWCOUNT 0
 
                        SELECT @totalRows = COUNT(EmployeeID) FROM Employee
                  END     
                  
      else
              BEGIN      
                        DECLARE @firstID2 int, @startRow2 int
                        SET @startRow2 = ((@startRowIndex - 1) * @maxRows) + 1
                        SET ROWCOUNT @startRow2      
                        declare @SQL varchar(max)
                  
						set @SQL='SELECT ' + @firstID2 + ' = EmployeeID FROM Employee where ' + @FilterType + ' like ''%' +@FilterValue + '%'' ORDER BY EmployeeID'
                        
                        exec sp_executesql @SQL
 
                        SET ROWCOUNT @maxRows
 
                        set @SQL ='SELECT EmployeeID, FirstName, LastName, Organization FROM Employee WHERE EmployeeID >= ' + @firstID2 + ' and ' + @FilterType + ' like ''%' +@FilterValue + '%'''
 
						exec sp_executesql @SQL
						
                        SET ROWCOUNT 0
 
                        set @SQL ='SELECT ' + @totalRows + ' = COUNT(EmployeeID) FROM Products where ' + @FilterType + ' like ''%' +@FilterValue + '%'''
                        
                        exec sp_executesql @SQL
                        
            END
                  
END

Select allOpen in new window

i got runtime error
"Conversion failed when converting the varchar value 'SELECT ' to data type int." . when i  run the above code. we have here "set @SQL ='SELECT ' + @totalRows + ' = COUNT(EmployeeID............".
@totalrows in integer which we are trying to attach using + sign to the strings.thats where i think it is breaking.wondering if there is any fix for it.

Please check all the comments posted. You need to use CONVERT function when conctinating integer values to strings.
AngelIII already suggested that for you in the first post.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
alter PROCEDURE [dbo].[uspGetEmployee]
 @FilterType [nvarchar](50),
 @FilterValue[nvarchar](50),
 @startRowIndex int,
 @maxRows int,
 @totalRows int out
   
AS
BEGIN
    SET NOCOUNT ON;
 
       
      if @FilterType = '' or @FilterValue = ''
 
        BEGIN  
               DECLARE @firstID1 int, @startRow1 int
                     SET @startRow1 = ((@startRowIndex - 1) * @maxRows) + 1
                   SET ROWCOUNT @startRow1
                        
                        SELECT @firstID1 = EmployeeID FROM Employee ORDER BY EmployeeID
 
                        SET ROWCOUNT @maxRows
 
                        SELECT  FirstName, LastName, Organization,Disabled FROM Employee
                        WHERE EmployeeID >= @firstID1
 
                        SET ROWCOUNT 0
 
                        SELECT @totalRows = COUNT(EmployeeID) FROM Employee
                  END     
                  
      else
              BEGIN      
                        DECLARE @firstID2 int, @startRow2 int
                        SET @startRow2 = ((@startRowIndex - 1) * @maxRows) + 1
                        SET ROWCOUNT @startRow2      
                        declare @SQL varchar(max)
                  
						set @SQL='SELECT ' + @firstID2 + ' = EmployeeID FROM Employee where ' + @FilterType + ' like ''%' +@FilterValue + '%'' ORDER BY EmployeeID'
                        
                        exec sp_executesql @SQL
 
                        SET ROWCOUNT @maxRows
 
                        set @SQL ='SELECT EmployeeID, FirstName, LastName, Organization FROM Employee WHERE EmployeeID >= ' + @firstID2 + ' and ' + @FilterType + ' like ''%' +@FilterValue + '%'''
 
						exec sp_executesql @SQL
						
                        SET ROWCOUNT 0
 
                        set @SQL ='SELECT ' + convert(varchar,@totalRows) + ' = COUNT(EmployeeID) FROM Products where ' + @FilterType + ' like ''%' +@FilterValue + '%'''
                        
                        exec sp_executesql @SQL
                        
            END
                  
END

Select allOpen in new window

Hi ,
I  still have no luck.i am still getting Run time error "Msg 214, Level 16, State 2, Procedure sp_executesql, Line 1
Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.

though i changed my code to

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
alter PROCEDURE [dbo].[uspGetEmployee]
 @FilterType [nvarchar](50),
 @FilterValue[nvarchar](50),
 @startRowIndex int,
 @maxRows int,
 @totalRows int out
   
AS
BEGIN
    SET NOCOUNT ON;
 
       
      if @FilterType = '' or @FilterValue = ''
 
        BEGIN  
               DECLARE @firstID1 int, @startRow1 int
                     SET @startRow1 = ((@startRowIndex - 1) * @maxRows) + 1
                   SET ROWCOUNT @startRow1
                       
                        SELECT @firstID1 = EmployeeID FROM Employee ORDER BY EmployeeID
 
                        SET ROWCOUNT @maxRows
 
                        SELECT  FirstName, LastName, Organization,Disabled FROM Employee
                        WHERE EmployeeID >= @firstID1
 
                        SET ROWCOUNT 0
 
                        SELECT @totalRows = COUNT(EmployeeID) FROM Employee
                  END    
                 
      else
              BEGIN      
                        DECLARE @firstID2 int, @startRow2 int,@totalRows1 as int
                        SET @startRow2 = ((@startRowIndex - 1) * @maxRows) + 1
                        SET ROWCOUNT @startRow2      
                        declare @SQL varchar(max)
                 
      set @SQL='SELECT ' + convert(varchar,@firstID2) + ' = EmployeeID FROM Employee where ' + @FilterType + ' like ''%' +@FilterValue + '%'' ORDER BY EmployeeID'
                       
                        exec sp_executesql @SQL
 
                        SET ROWCOUNT @maxRows
 
                        set @SQL ='SELECT EmployeeID, FirstName, LastName, Organization FROM Employee WHERE EmployeeID >= ' + convert(varchar,@firstID2) + ' and ' + @FilterType + ' like ''%' +@FilterValue + '%'''
 
      exec sp_executesql @SQL
                                    
                        SET ROWCOUNT 0
 
                        set @SQL ='SELECT ' + convert(varchar,@totalRows1) + ' = COUNT(EmployeeID) FROM Products where ' + @FilterType + ' like ''%' +@FilterValue + '%'''
                       
                        exec sp_executesql @SQL
                        set @totalRows = convert(int,@totalRows1)
                         
            END
                 
END

i  tried changing all convert statement to "nvarchar" .but still no luck .

try this

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
alter PROCEDURE [dbo].[uspGetEmployee]
 @FilterType [nvarchar](50),
 @FilterValue[nvarchar](50),
 @startRowIndex int,
 @maxRows int,
 @totalRows int out
   
AS
BEGIN
    SET NOCOUNT ON;
 
       
      if @FilterType = '' or @FilterValue = ''
 
        BEGIN  
               DECLARE @firstID1 int, @startRow1 int
                     SET @startRow1 = ((@startRowIndex - 1) * @maxRows) + 1
                   SET ROWCOUNT @startRow1
                        
                        SELECT @firstID1 = EmployeeID FROM Employee ORDER BY EmployeeID
 
                        SET ROWCOUNT @maxRows
 
                        SELECT  FirstName, LastName, Organization,Disabled FROM Employee
                        WHERE EmployeeID >= @firstID1
 
                        SET ROWCOUNT 0
 
                        SELECT @totalRows = COUNT(EmployeeID) FROM Employee
                  END     
                  
      else
              BEGIN      
                        DECLARE @firstID2 int, @startRow2 int,@totalRows1 as int
                        SET @startRow2 = ((@startRowIndex - 1) * @maxRows) + 1
                        SET ROWCOUNT @startRow2      
                        declare @SQL nvarchar(max)
                  
      set @SQL=N'SELECT ' + convert(nvarchar,@firstID2) + N' = EmployeeID FROM Employee where ' + @FilterType + N' like ''%' +@FilterValue + N'%'' ORDER BY EmployeeID'
                        
                        exec sp_executesql @SQL
 
                        SET ROWCOUNT @maxRows
 
                        set @SQL =N'SELECT EmployeeID, FirstName, LastName, Organization FROM Employee WHERE EmployeeID >= ' + convert(nvarchar,@firstID2) + N' and ' + @FilterType + N' like ''%' +@FilterValue + N'%'''
 
      exec sp_executesql @SQL
                                    
                        SET ROWCOUNT 0
 
                        set @SQL ='SELECT ' + convert(nvarchar,@totalRows1) + ' = COUNT(EmployeeID) FROM Products where ' + @FilterType + ' like ''%' +@FilterValue + '%'''
                        
                        exec sp_executesql @SQL
                        set @totalRows = convert(int,@totalRows1)
                         
            END
                  
END

Select allOpen in new window

hi,
but i am not getting any results back for any kind of parameter values

instead of executing, try to print your dynamic sql. observe the dynamic sql query and try to execute that. If you need assistance post the dynamic sql here. we can have a look into that.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
alter PROCEDURE [dbo].[uspGetEmployee]
 @FilterType [nvarchar](50),
 @FilterValue[nvarchar](50),
 @startRowIndex int,
 @maxRows int,
 @totalRows int out
   
AS
BEGIN
    SET NOCOUNT ON;
 
       
      if @FilterType = '' or @FilterValue = ''
 
        BEGIN  
               DECLARE @firstID1 int, @startRow1 int
                     SET @startRow1 = ((@startRowIndex - 1) * @maxRows) + 1
                   SET ROWCOUNT @startRow1
                        
                        SELECT @firstID1 = EmployeeID FROM Employee ORDER BY EmployeeID
 
                        SET ROWCOUNT @maxRows
 
                        SELECT  FirstName, LastName, Organization,Disabled FROM Employee
                        WHERE EmployeeID >= @firstID1
 
                        SET ROWCOUNT 0
 
                        SELECT @totalRows = COUNT(EmployeeID) FROM Employee
                  END     
                  
      else
              BEGIN      
                        DECLARE @firstID2 int, @startRow2 int,@totalRows1 as int
                        SET @startRow2 = ((@startRowIndex - 1) * @maxRows) + 1
                        SET ROWCOUNT @startRow2      
                        declare @SQL nvarchar(max)
                  
      set @SQL=N'SELECT ' + convert(nvarchar,@firstID2) + N' = EmployeeID FROM Employee where ' + @FilterType + N' like ''%' +@FilterValue + N'%'' ORDER BY EmployeeID'
                        
                        --exec sp_executesql @SQL
                        print @SQL
 
                        SET ROWCOUNT @maxRows
 
                        set @SQL =N'SELECT EmployeeID, FirstName, LastName, Organization FROM Employee WHERE EmployeeID >= ' + convert(nvarchar,@firstID2) + N' and ' + @FilterType + N' like ''%' +@FilterValue + N'%'''
 
      --exec sp_executesql @SQL
      print @SQL
                                    
                        SET ROWCOUNT 0
 
                        set @SQL ='SELECT ' + convert(nvarchar,@totalRows1) + ' = COUNT(EmployeeID) FROM Products where ' + @FilterType + ' like ''%' +@FilterValue + '%'''
                        
                        exec sp_executesql @SQL
                        set @totalRows = convert(int,@totalRows1)
                         
            END
                  
END

Select allOpen in new window

OK , i am trying to use the 'print' command in my SP ,but nothing getting printed. i  am using SQLServer 2005 Express Edition. i  used  print(@SQL)

I don't know what is your goal. Nothing getting printed means either @FilterType or @FilterValue paramter is blank (empty string). So you are executing only IF part.
In the IF part, you have calculated the @totalRows with some logic.
Try to pass some string values to both the @FilterType and @FilterValue parameters and check the dynamic sql.
You can include as many print statements as possible for debugging to know whats happening.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
alter PROCEDURE [dbo].[uspGetEmployee]
 @FilterType [nvarchar](50),
 @FilterValue[nvarchar](50),
 @startRowIndex int,
 @maxRows int,
 @totalRows int out
   
AS
BEGIN
    SET NOCOUNT ON;
 
       
      if @FilterType = '' or @FilterValue = ''
 
        BEGIN  
               DECLARE @firstID1 int, @startRow1 int
                     SET @startRow1 = ((@startRowIndex - 1) * @maxRows) + 1
                   SET ROWCOUNT @startRow1
                        
                        SELECT @firstID1 = EmployeeID FROM Employee ORDER BY EmployeeID
 
                        SET ROWCOUNT @maxRows
 
                        SELECT  FirstName, LastName, Organization,Disabled FROM Employee
                        WHERE EmployeeID >= @firstID1
 
                        SET ROWCOUNT 0
 
                        SELECT @totalRows = COUNT(EmployeeID) FROM Employee
                        print @toalRows
                  END     
                  
      else
              BEGIN      
                        DECLARE @firstID2 int, @startRow2 int,@totalRows1 as int
                        SET @startRow2 = ((@startRowIndex - 1) * @maxRows) + 1
                        SET ROWCOUNT @startRow2      
                        declare @SQL nvarchar(max)
                  
      set @SQL=N'SELECT ' + convert(nvarchar,@firstID2) + N' = EmployeeID FROM Employee where ' + @FilterType + N' like ''%' +@FilterValue + N'%'' ORDER BY EmployeeID'
                        
                        --exec sp_executesql @SQL
                        print @SQL
 
                        SET ROWCOUNT @maxRows
 
                        set @SQL =N'SELECT EmployeeID, FirstName, LastName, Organization FROM Employee WHERE EmployeeID >= ' + convert(nvarchar,@firstID2) + N' and ' + @FilterType + N' like ''%' +@FilterValue + N'%'''
 
      --exec sp_executesql @SQL
      print @SQL
                                    
                        SET ROWCOUNT 0
 
                        set @SQL ='SELECT ' + convert(nvarchar,@totalRows1) + ' = COUNT(EmployeeID) FROM Products where ' + @FilterType + ' like ''%' +@FilterValue + '%'''
                        
                        --exec sp_executesql @SQL
                        print @SQL
                        set @totalRows = convert(int,@totalRows1)
                         
            END
                  
END

Select allOpen in new window

Hi ,
MY Else statement is working fine.when i  just print('test') ,it is getting printed,only when i change it to print(@SQL) it is not printing .

One of the variable (@firstID2, @FilterType, or @FilterValue) is becoming NULL. so its not displaying the dynamic sql.
try this and check which variable is becoming NULL and why?

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
alter PROCEDURE [dbo].[uspGetEmployee]
 @FilterType [nvarchar](50),
 @FilterValue[nvarchar](50),
 @startRowIndex int,
 @maxRows int,
 @totalRows int out
   
AS
BEGIN
    SET NOCOUNT ON;
 
       
      if @FilterType = '' or @FilterValue = ''
 
        BEGIN  
               DECLARE @firstID1 int, @startRow1 int
                     SET @startRow1 = ((@startRowIndex - 1) * @maxRows) + 1
                   SET ROWCOUNT @startRow1
                        
                        SELECT @firstID1 = EmployeeID FROM Employee ORDER BY EmployeeID
 
                        SET ROWCOUNT @maxRows
 
                        SELECT  FirstName, LastName, Organization,Disabled FROM Employee
                        WHERE EmployeeID >= @firstID1
 
                        SET ROWCOUNT 0
 
                        SELECT @totalRows = COUNT(EmployeeID) FROM Employee
                        print @toalRows
                  END     
                  
      else
              BEGIN      
                        DECLARE @firstID2 int, @startRow2 int,@totalRows1 as int
                        SET @startRow2 = ((@startRowIndex - 1) * @maxRows) + 1
                        SET ROWCOUNT @startRow2      
                        declare @SQL nvarchar(max)
                  
      set @SQL=N'SELECT ' + convert(nvarchar,@firstID2) + N' = EmployeeID FROM Employee where ' + @FilterType + N' like ''%' +@FilterValue + N'%'' ORDER BY EmployeeID'
                        
                        --exec sp_executesql @SQL
                        print 'dynamic sql is : ' + @SQL
                        print '@firstID2 = ' + convert(varchar,@firstID2)
                        print '@@FilterType = ' + @FilterType
                        print '@@FilterValue = ' + @FilterValue
                        SET ROWCOUNT @maxRows
 
                        set @SQL =N'SELECT EmployeeID, FirstName, LastName, Organization FROM Employee WHERE EmployeeID >= ' + convert(nvarchar,@firstID2) + N' and ' + @FilterType + N' like ''%' +@FilterValue + N'%'''
 
      --exec sp_executesql @SQL
                        print 'dynamic sql is : ' + @SQL
                        print '@firstID2 = ' + convert(varchar,@firstID2)
                        print '@@FilterType = ' + @FilterType
                        print '@@FilterValue = ' + @FilterValue
                        SET ROWCOUNT @maxRows
                                    
                        SET ROWCOUNT 0
 
                        set @SQL ='SELECT ' + convert(nvarchar,@totalRows1) + ' = COUNT(EmployeeID) FROM Products where ' + @FilterType + ' like ''%' +@FilterValue + '%'''
                        
                        --exec sp_executesql @SQL
                        print 'dynamic sql is : ' + @SQL
                        print '@firstID2 = ' + convert(varchar,@firstID2)
                        print '@@FilterType = ' + @FilterType
                        print '@@FilterValue = ' + @FilterValue
                        SET ROWCOUNT @maxRows
                        set @totalRows = convert(int,@totalRows1)
                         
            END
                  
END

Select allOpen in new window

when i execute the above statement i am getting the output messages as below

@@FilterType = FirstName
@@FilterValue = John
 
 
@@FilterType = FirstName
@@FilterValue = John
 
 
@@FilterType = FirstName
@@FilterValue = John

(1 row(s) affected)

(1 row(s) affected)

@firstID2 is missing everywhere.so it is the one becoming NULL .i  dont know why

but here
 set @SQL ='SELECT ' + convert(nvarchar,@totalRows1) + ' = COUNT(EmployeeID) FROM Products where ' + @FilterType + ' like ''%' +@FilterValue + '%'''
                       
                        --exec sp_executesql @SQL
                        print @SQL

 we dont have @firstID2 ,even then i  am not able to print @SQL

can you try this

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
alter PROCEDURE [dbo].[uspGetEmployee]
 @FilterType [nvarchar](50),
 @FilterValue[nvarchar](50),
 @startRowIndex int,
 @maxRows int,
 @totalRows int out
   
AS
BEGIN
    SET NOCOUNT ON;
DECLARE @firstID1 int, @startRow1 int
DECLARE @firstID2 int, @startRow2 int,@totalRows1 int
declare @SQL nvarchar(max)
declare @ParmDefinition nvarchar(500)
     
      if @FilterType = '' or @FilterValue = ''
 
        BEGIN  
               
                   SET @startRow1 = ((@startRowIndex - 1) * @maxRows) + 1
                   SET ROWCOUNT @startRow1
                        
                SELECT @firstID1 = EmployeeID FROM Employee ORDER BY EmployeeID
                   SET ROWCOUNT @maxRows
                SELECT  FirstName, LastName, Organization,Disabled FROM Employee WHERE EmployeeID >= @firstID1
                   SET ROWCOUNT 0
                SELECT @totalRows = COUNT(EmployeeID) FROM Employee 
           END     
                  
      else
              BEGIN      
                        
                        SET @startRow2 = ((@startRowIndex - 1) * @maxRows) + 1
                        SET ROWCOUNT @startRow2      
                        
                        set @SQL = 'select @firstID2In = EmployeeID from Employee where @FilterTypeIn like ''%@FilterValueIn%'''
                        SET @ParmDefinition = N'FilterTypeIn nvarchar(50),@FilterValueIn nvarchar(50),@firstID2In int output'
                        EXECUTE sp_executesql @SQL,@ParmDefinition,@FilterTypeIn = @FilterType,@FilterValueIn = @FilterValue,
                                              @firstID2In = @firstID2 output
                        
                        SET ROWCOUNT @maxRows
                        
                        set @SQL = 'select EmployeeID, FirstName, LastName, Organization 
                                        FROM Employee WHERE EmployeeID >= @firstID2In and @FilterTypeIn like ''%@FilterValueIn%' 
                        SET @ParmDefinition = N'FilterTypeIn nvarchar(50),@FilterValueIn nvarchar(50),@firstID2In int'
 
                        EXECUTE sp_executesql @SQL,@ParmDefinition,@FilterTypeIn = @FilterType,@FilterValueIn = @FilterValue,
                                              @firstID2In = @firstID2 
                        
                        SET ROWCOUNT @maxRows
                                    
                        SET ROWCOUNT 0
                        
                        set @SQL =  'select @totalRows1In = COUNT(EmployeeID) FROM Products where @FilterTypeIn like ''%@FilterValueIn%' 
                        SET @ParmDefinition = N'FilterTypeIn nvarchar(50),@FilterValueIn nvarchar(50),@totalRows1In int output'
                        EXECUTE sp_executesql @SQL,@ParmDefinition,@FilterTypeIn = @FilterType,@FilterValueIn = @FilterValue,
                                              @totalRows1In = @totalRows1 output
                                              
                        SET ROWCOUNT @maxRows
                        set @totalRows = convert(int,@totalRows1)
                         
            END
                  
END

Select allOpen in new window

i just want to fix this peace of code which is to send back @totalRows  as out parameter.rest all working fine from angell and ritesh shah 's first code ,even though the logic is little different..

 SET ROWCOUNT 0
 
                        set @SQL ='SELECT ' + convert(nvarchar,@totalRows1) + ' = COUNT(EmployeeID) FROM Products where ' + @FilterType + ' like ''%' +@FilterValue + '%'''
                       
                        --exec sp_executesql @SQL
                        print @SQL
                        set @totalRows = convert(int,@totalRows1)

i  am getting  @totalRows  value 0 always .i  like to keep the same logic in both if and else ,but for atlease to get the code working in else, i want to fix this first.

i  got runtime errors as follows when i run this program

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'FilterTypeIn'.
Msg 137, Level 15, State 1, Line 1
Must declare the scalar variable "@firstID2In".
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'FilterTypeIn'.
Msg 137, Level 15, State 2, Line 2
Must declare the scalar variable "@firstID2In".
Msg 105, Level 15, State 1, Line 2
Unclosed quotation mark after the character string '%@FilterValueIn%'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'FilterTypeIn'.
Msg 137, Level 15, State 1, Line 1
Must declare the scalar variable "@totalRows1In".
Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark after the character string '%@FilterValueIn%'.

Is it possible to provide some sample data and expected result. just want to know what are you trying to do? Your @firstID2  variable is becoming NULL. thats why dynamic sql is not displayed. try my last post (# 24300472).

Can you provide your table structure and sample data. I can setup on my machine and fix this.

i  am passing values as

@FilterType [nvarchar](50), - 'FirstName'  - (column name)
 @FilterValue[nvarchar](50), - 'John'   (column value)
 @startRowIndex int, - 1
 @maxRows int, - 10
 @totalRows int out - should return 1 ,because i  have one row with firstname as John

In this SP,

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
alter PROCEDURE [dbo].[uspGetEmployee]
@FilterType [nvarchar](50),
@FilterValue[nvarchar](50),
@startRowIndex int,
@maxRows int,
@totalRows int out

AS
BEGIN
SET NOCOUNT ON;


if @FilterType = '' or @FilterValue = ''

BEGIN
DECLARE @firstID1 int, @startRow1 int
SET @startRow1 = ((@startRowIndex - 1) * @maxRows) + 1
SET ROWCOUNT @startRow1

SELECT @firstID1 = EmployeeID FROM Employee ORDER BY EmployeeID

SET ROWCOUNT @maxRows

SELECT FirstName, LastName, Organization,Disabled FROM Employee
WHERE EmployeeID >= @firstID1

SET ROWCOUNT 0

SELECT @totalRows = COUNT(EmployeeID) FROM Employee output

END

else
BEGIN
DECLARE @firstID2 int, @startRow2 int,@totalRows1 as int
SET @startRow2 = ((@startRowIndex - 1) * @maxRows) + 1
SET ROWCOUNT @startRow2
declare @SQL nvarchar(max)

set @SQL=N'SELECT ' + convert(nvarchar,@firstID2) + N' = EmployeeID FROM Employee where ' + @FilterType + N' like ''%' +@FilterValue + N'%'' ORDER BY EmployeeID'

--exec sp_executesql @SQL
print 'dynamic sql is : ' + @SQL
print '@firstID2 = ' + convert(varchar,@firstID2)
print '@@FilterType = ' + @FilterType
print '@@FilterValue = ' + @FilterValue
SET ROWCOUNT @maxRows

set @SQL =N'SELECT EmployeeID, FirstName, LastName, Organization FROM Employee WHERE EmployeeID >= ' + convert(nvarchar,@firstID2) + N' and ' + @FilterType + N' like ''%' +@FilterValue + N'%'''

--exec sp_executesql @SQL
print 'dynamic sql is : ' + @SQL
print '@firstID2 = ' + convert(varchar,@firstID2)
print '@@FilterType = ' + @FilterType
print '@@FilterValue = ' + @FilterValue
SET ROWCOUNT @maxRows

SET ROWCOUNT 0

set @SQL ='SELECT ' + convert(nvarchar,@totalRows1) + ' = COUNT(EmployeeID) FROM Products where ' + @FilterType + ' like ''%' +@FilterValue + '%'''

--exec sp_executesql @SQL
print 'dynamic sql is : ' + @SQL
print '@firstID2 = ' + convert(varchar,@firstID2)
print '@@FilterType = ' + @FilterType
print '@@FilterValue = ' + @FilterValue
SET ROWCOUNT @maxRows
set @totalRows = convert(int,@totalRows1)


END

END  
 
You have @firstId1 delcared and assigned some value, but for @firstId2, you don't assign any value so its taking NULL value.
I think you want to return the EmployeeId into @firstId2. then try like the post #24300472. Don't bother about the error message. We can fix those. As I asked provide me the structure and sample data

My Table Structure is .
Table Name - Employee
Columns - EmployeeID,FirstName,LastName,Organization
data       - 1,John ,Miller,IBM
                2,Sam,Cooper,Microsoft

Then what is your expected result from the SP?

when i pass
@FilterType [nvarchar](50), - 'FirstName'
@FilterValue[nvarchar](50),  - 'John'
@startRowIndex int, - 1
@maxRows int,  - 10
@totalRows int out  - should return 1
along with the result '1,John ,Miller,IBM '

Why do you want the SET ROWCOUINT in your SP?
What are you doing with that?
Can you explain what is the purpose of your @StartRowIndex and @maxRows parameters?

nagdotnet -  Can you tell me the reason for rating 'B'. I am pretty sure that I have fixed your problem.

Hey Sharat123 ,Sorry . Thanks for your help .you really fixed my problem.i  never really know that grades really matter to you people.i didnt really paid attention when choosing the grade .you certainly deserve A .Thanks again.