SQL Server query syntax error

Question

I have written a SQL Server query:

declare  @TaxYear VARCHAR(50)
set @TaxYear='13'
declare    @BBL VARCHAR(50) 
set @BBL=''
declare @Appartment VARCHAR(50) 
set @Appartment=''
declare    @ResidenceTypeDescription VARCHAR(200) 
set @ResidenceTypeDescription=''
declare    @SN1 VARCHAR(20) 
set @SN1=''
declare    @SN2 VARCHAR(20) 
set @SN2=''
declare    @Status VARCHAR(100) 
set @Status='Unassigned'
declare    @RowIndex INT 
set @RowIndex=1
declare    @MaxRows INT 
set @MaxRows=25
declare    @SortExpression varchar(50) 
set @SortExpression='bbl desc'
declare @sql varchar(max)
DECLARE @StartRow INT
DECLARE @EndRow INT
    SET @StartRow =  @RowIndex 
    SET @EndRow = ( @StartRow + @MaxRows ) - 1
          set @sql=  'SELECT  *
            FROM    ( SELECT    * ,
                                ROW_NUMBER() OVER ( ORDER BY '+@SortExpression +') AS ROW
                      FROM      vwApplicationList
                      WHERE     TaxYear = '+@TaxYear+'
                                AND Status = '+@Status+'
                                AND REPLACE(BBL, ''--'', '''') LIKE ''%' + @BBL
                                + '%''
                                AND COALESCE(UnitOrAppartmentNumber, '''') LIKE ''%'
                                + @Appartment + '%''
                                AND COALESCE(ResidenceTypeDescription, '''') LIKE ''%'
                                + @ResidenceTypeDescription + '%''
                                AND ( COALESCE(SN1, '''') LIKE ''%' + @SN1 + '%''
                                      OR COALESCE(SN2, '''') LIKE ''%' + @SN2
                                      + '%''
                                    )
                    ) AS NumberedUsers
            WHERE   ROW BETWEEN '+@StartRow+' AND '+@EndRow
exec (@sql)

When I run this query I get an error:

enter image description here

please, format your question's code - it's very hard to read it — chopikadze
– chopikadze, Commented Feb 2, 2012 at 7:18

Off The Gold · Accepted Answer · 2016-02-11 15:02:14Z

The problem is at the end of your query where you are trying to concatenate the integer value, you have have convert it to a varchar...

declare  @TaxYear VARCHAR(50)
set @TaxYear='13'
declare    @BBL VARCHAR(50) 
set @BBL=''
declare @Appartment VARCHAR(50) 
set @Appartment=''
declare    @ResidenceTypeDescription VARCHAR(200) 
set @ResidenceTypeDescription=''
declare    @SN1 VARCHAR(20) 
set @SN1=''
declare    @SN2 VARCHAR(20) 
set @SN2=''
declare    @Status VARCHAR(100) 
set @Status='Unassigned'
declare    @RowIndex INT 
set @RowIndex=1
declare    @MaxRows INT 
set @MaxRows=25
declare    @SortExpression varchar(50) 
set @SortExpression='bbl desc'
declare @sql varchar(max)
DECLARE @StartRow INT
DECLARE @EndRow INT
    SET @StartRow =  @RowIndex 
    SET @EndRow = ( @StartRow + @MaxRows ) - 1
          set @sql=            
          'SELECT  *
            FROM    ( SELECT    * ,
                                ROW_NUMBER() OVER ( ORDER BY '+@SortExpression +') AS ROW
                      FROM      vwApplicationList
                      WHERE     TaxYear = '+@TaxYear+'
                                AND Status = '+@Status+'
                                AND REPLACE(BBL, ''--'', '''') LIKE ''%' + @BBL
                                + '%''
                                AND COALESCE(UnitOrAppartmentNumber, '''') LIKE ''%'
                                + @Appartment + '%''
                                AND COALESCE(ResidenceTypeDescription, '''') LIKE ''%'
                                + @ResidenceTypeDescription + '%''
                                AND ( COALESCE(SN1, '''') LIKE ''%' + @SN1 + '%''
                                      OR COALESCE(SN2, '''') LIKE ''%' + @SN2
                                      + '%''
                                    )
                    ) AS NumberedUsers
            WHERE   ROW BETWEEN '+ Convert(varchar,@StartRow)+' AND '+Convert(varchar,@EndRow)

exec (@sql)

Mithrandir · Accepted Answer · 2012-02-02 07:21:00Z

0

Replace @StartRow with CAST(@StartRow AS VARCHAR(50)) and @EndRow with CAST(@EndRow AS VARCHAR(50))

answered Feb 2, 2012 at 7:21

Mithrandir

25.5k6 gold badges53 silver badges67 bronze badges

Comments

Saurabh · Accepted Answer · 2012-02-02 07:21:35Z

0

Use: convert(int,@StartRow) and convert(int,@EndRow)

answered Feb 2, 2012 at 7:21

Saurabh

5,7272 gold badges28 silver badges32 bronze badges

Collectives™ on Stack Overflow

SQL Server query syntax error

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related