T SQL Problem

Can somebody point me out what i  am doing wrong with the attached stored proc code. because i am getting error as below when i pass the values to this SP  

'Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '='.
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'and'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '='.
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 @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

Open in new window

nagdotnetAsked:
Who is Participating?
 
SharathConnect With a Mentor Data EngineerCommented:
fixed your sp. check the attached code.
 declare @totalRowsOut int
exec [dbo].[uspGetEmployee]
@FilterType = 'FirstName',
@FilterValue = 'John',
@startRowIndex = 1,
@maxRows = 10,
@totalRows = @totalRowsOut output
select @totalRowsOut

EmployeeID FirstName LastName Organization
1 John Miller IBM

(No column name)
1

alter procedure [dbo].[uspGetEmployee]
@FilterType nvarchar(50),
@FilterValue nvarchar(50),
@startRowIndex int,
@maxRows int,
@totalRows int out
as 
 
declare @startRow1 int,@startRow2 int
declare @firstID1 int,@firstID2 int
declare @totalRows1 int
 
declare @SQL nvarchar(2000),@ParmDefinition nvarchar(1000)
 
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 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 '+ @FilterType+' like ''%'+@FilterValue+'%'''
      set @ParmDefinition = '@firstID2In int output'
      execute sp_executesql @SQL,@ParmDefinition,@firstID2In = @firstID2 output  
      set rowcount @maxRows  
      set @SQL = 'select EmployeeID, FirstName, LastName, Organization 
                    from Employee where EmployeeID >= @firstID2In and '+ @FilterType+' like ''%'+@FilterValue+'%'''
      set @ParmDefinition = '@firstID2In int output'
      execute sp_executesql @SQL,@ParmDefinition,@firstID2In = @firstID2 output
      set rowcount 0
      set @SQL = 'select @totalRows1In = count(EmployeeId) from Employee where '+ @FilterType+' like ''%'+@FilterValue+'%'''
      set @ParmDefinition = '@totalRows1In int output'
      execute sp_executesql @SQL,@ParmDefinition,@totalRows1In = @totalRows1 output
      set @totalRows = convert(int,@totalRows1)
end

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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

Open in new window

0
 
RiteshShahCommented:
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

Open in new window

0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
nagdotnetAuthor Commented:
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




0
 
RiteshShahCommented:
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
0
 
nagdotnetAuthor Commented:
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)
0
 
RiteshShahCommented:
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

Open in new window

0
 
nagdotnetAuthor Commented:
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.
0
 
SharathData EngineerCommented:

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

Open in new window

0
 
nagdotnetAuthor Commented:
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 .
0
 
SharathData EngineerCommented:
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

Open in new window

0
 
nagdotnetAuthor Commented:
hi,
but i am not getting any results back for any kind of parameter values
0
 
SharathData EngineerCommented:

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

Open in new window

0
 
nagdotnetAuthor Commented:
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)
0
 
SharathData EngineerCommented:

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

Open in new window

0
 
nagdotnetAuthor Commented:
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 .
0
 
SharathData EngineerCommented:

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

Open in new window

0
 
nagdotnetAuthor Commented:
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
0
 
nagdotnetAuthor Commented:
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
0
 
SharathData EngineerCommented:
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

Open in new window

0
 
nagdotnetAuthor Commented:
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.
0
 
nagdotnetAuthor Commented:
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%'.
0
 
SharathData EngineerCommented:
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).
0
 
SharathData EngineerCommented:
Can you provide your table structure and sample data. I can setup on my machine and fix this.
0
 
nagdotnetAuthor Commented:
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

0
 
SharathData EngineerCommented:
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
0
 
nagdotnetAuthor Commented:
My Table Structure is .
Table Name - Employee
Columns - EmployeeID,FirstName,LastName,Organization
data       - 1,John ,Miller,IBM
                2,Sam,Cooper,Microsoft
0
 
SharathData EngineerCommented:
Then what is your expected result from the SP?
0
 
nagdotnetAuthor Commented:
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 '
0
 
SharathData EngineerCommented:
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?
0
 
SharathData EngineerCommented:
nagdotnet -  Can you tell me the reason for rating 'B'. I am pretty sure that I have fixed your problem.
0
 
nagdotnetAuthor Commented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.