Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

T SQL Problem

Posted on 2009-05-03
33
Medium Priority
?
324 Views
Last Modified: 2012-05-06
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

0
Comment
Question by:nagdotnet
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 15
  • 13
  • 3
  • +1
33 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24289300
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
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24289341
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
 

Author Comment

by:nagdotnet
ID: 24291047
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 31

Expert Comment

by:RiteshShah
ID: 24292422
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
 

Author Comment

by:nagdotnet
ID: 24292939
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
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24292977
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
 

Author Comment

by:nagdotnet
ID: 24297975
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
 
LVL 41

Expert Comment

by:Sharath
ID: 24298047

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
 

Author Comment

by:nagdotnet
ID: 24298538
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
 
LVL 41

Expert Comment

by:Sharath
ID: 24299437
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
 

Author Comment

by:nagdotnet
ID: 24299818
hi,
but i am not getting any results back for any kind of parameter values
0
 
LVL 41

Expert Comment

by:Sharath
ID: 24299924

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
 

Author Comment

by:nagdotnet
ID: 24299966
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
 
LVL 41

Expert Comment

by:Sharath
ID: 24300061

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
 

Author Comment

by:nagdotnet
ID: 24300180
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
 
LVL 41

Expert Comment

by:Sharath
ID: 24300252

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
 

Author Comment

by:nagdotnet
ID: 24300423
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
 

Author Comment

by:nagdotnet
ID: 24300442
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
 
LVL 41

Expert Comment

by:Sharath
ID: 24300472
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
 

Author Comment

by:nagdotnet
ID: 24300500
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
 

Author Comment

by:nagdotnet
ID: 24300514
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
 
LVL 41

Expert Comment

by:Sharath
ID: 24300561
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
 
LVL 41

Expert Comment

by:Sharath
ID: 24300568
Can you provide your table structure and sample data. I can setup on my machine and fix this.
0
 

Author Comment

by:nagdotnet
ID: 24300590
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
 
LVL 41

Expert Comment

by:Sharath
ID: 24300603
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
 

Author Comment

by:nagdotnet
ID: 24300634
My Table Structure is .
Table Name - Employee
Columns - EmployeeID,FirstName,LastName,Organization
data       - 1,John ,Miller,IBM
                2,Sam,Cooper,Microsoft
0
 
LVL 41

Expert Comment

by:Sharath
ID: 24300637
Then what is your expected result from the SP?
0
 

Author Comment

by:nagdotnet
ID: 24300670
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
 
LVL 41

Expert Comment

by:Sharath
ID: 24301267
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
 
LVL 41

Accepted Solution

by:
Sharath earned 1000 total points
ID: 24301475
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
 
LVL 41

Expert Comment

by:Sharath
ID: 24301974
nagdotnet -  Can you tell me the reason for rating 'B'. I am pretty sure that I have fixed your problem.
0
 

Author Closing Comment

by:nagdotnet
ID: 31577298
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question