Solved

T SQL Problem

Posted on 2009-05-03
33
305 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
  • 15
  • 13
  • 3
  • +1
33 Comments
 
LVL 142

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
 
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 40

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 40

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 40

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 40

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 40

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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

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 40

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 40

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 40

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 40

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 40

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 40

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 40

Accepted Solution

by:
Sharath earned 250 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 40

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 Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

746 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now