CODEWITHSUNDEEP
sql-serversyntax-error
hotcoder
hotcoder

Reputation: 3246

SQL Server query syntax error

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

Upvotes: 0

Views: 252

Answers (3)

Muhammad Akhtar
Muhammad Akhtar

Reputation: 52241

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)

Upvotes: 2

Saurabh
Saurabh

Reputation: 5727

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

Upvotes: 0

Mithrandir
Mithrandir

Reputation: 25337

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

Upvotes: 0

Related Questions