In sql sever, building dynamic sql with integer variables

Question

I'm building this dynamic sql but getting an error when executing it.

Conversion failed when converting the varchar value 'SELECT 
                    DDoSAttacksId,
                    AttackId,
                    TargetIP,
                    PeakBPS, 
                    DateStarted                                                                
            FROM (
                SELECT 
                    ROW_NUMBER() OVER (ORDER BY DateStarted Desc) AS Row,
                    DDoSAttacksId                                 AS DDoSAttacksId,
                    AttackId                                      AS AttackId,
                    TargetIP                                      As TargetIP,
                    CONCAT(PeakBPS / 1000000000,' Gbps')        As PeakBPS,
                    DateStarted                                   AS DateStarted
                FROM
                    DDosAttacks
                WHERE TargetIP IN ('108.61.51.101', '206.221.181.5')) AS DDosAttacksRows
            WHERE
                (Row between (' to data type int.

Note: It plugs in the IPAddress just fine, but not the integer values.

set quoted_identifier off

declare @sql              nvarchar(4000),
        @PageNumber       int,
        @PageSize         int,
        @IPAddressList   varchar(100)

Select @IPAddressList = "'108.61.51.101', '206.221.181.5'"

select @sql= 'SELECT 
                DDoSAttacksId,
                AttackId,
                TargetIP,
                PeakBPS, 
                DateStarted                                                                
        FROM (
            SELECT 
                ROW_NUMBER() OVER (ORDER BY DateStarted Desc) AS Row,
                DDoSAttacksId                                 AS DDoSAttacksId,
                AttackId                                      AS AttackId,
                TargetIP                                      As TargetIP,
                CONCAT(PeakBPS / 1000000000,'' Gbps'')        As PeakBPS,
                DateStarted                                   AS DateStarted
            FROM
                DDosAttacks
            WHERE TargetIP IN ('+@IPAddressList+')) AS DDosAttacksRows
        WHERE
            (Row between ('+@PageNumber+' * ('+@PageSize+' - '+@PageSize+')) AND ('+@PageNumber+' * ('+@PageSize+' - 1)))';

EXECUTE sp_executesql @sql

I can hard code numbers in their place it and executes fine....for example (Row between (1 * (20 - 20)) AND (1 * (20 - 1)))';

It appears you are trying to construct a `NVARCHAR` string by concatenating type `int` (ie, `@PageNumber` in the `(Row between (' + @PageNumber +` line) which is invalid. — Anthony Forloney, Jan 23 '15 at 19:04
possible duplicate of [How to Concatenate Numbers and Strings to Format Numbers in T-SQL?](http://stackoverflow.com/questions/951320/how-to-concatenate-numbers-and-strings-to-format-numbers-in-t-sql) — LittleBobbyTables - Au Revoir, Jan 23 '15 at 19:05

Jamie Paolino · Accepted Answer · 2015-01-26T18:19:54.577

3

you can try something like:

'(Row between ('+ cast(@PageNumber as varchar(255)) +' * ('+cast(@PageSizeas varchar(255)) +' - '+cast(@PageSize as varchar(255))+')) AND ('+cast(@PageNumber as varchar(255))+' * ('+cast(@PageSize as varchar(255))+' - 1)))';

Updated my answer to provided a length of the casted varchar's due to KM response below. thank you KM for the explanation.

edited Jan 26 '15 at 18:19

answered Jan 23 '15 at 19:05

Jamie Paolino

597
4
6

1

Thanks it worked.. to everyone who responded similarly...thanks. – user3020047 Jan 23 '15 at 19:10
2

It is a worst practice to not specify a length to your char/varchar. You will eventually get burned when your data is truncated because the default is smaller than the resulting string. – KM. Jan 23 '15 at 19:21
1

@KM With regards to your comment, wouldn't the system allocate the `MAX` length to the `VARCHAR` when a length is omitted within a `CAST`? – Anthony Forloney Jan 23 '15 at 19:27
1

@user3020047 In an effort to help the community, you should mark answers as accepted as often as you can. It helps to quickly identify a solution to a problem that other users may be experiencing. – Anthony Forloney Jan 23 '15 at 20:57
1

@Anthony Forloney, **no SQL Server WILL NOT default varchar to max**. When declaring a variable like `DECLARE @x varchar` it will default to `varchar(1)`. When using an in-line conversion like in the answer, it will default to `varchar(30)`. SQL Server can't read your mind, or understand your needs, so always select a data type and length appropriate for your specific case, you are the programmer, take charge. See https://sqlblog.org/2009/10/09/bad-habits-to-kick-declaring-varchar-without-length – KM. Jan 26 '15 at 14:19

score 1 · Answer 2 · answered Jan 23 '15 at 19:05

The + operator is overloaded in SQL Server. If any argument is numeric, then it defaults to a numeric plus, rather than string concatenation.

The solution is to convert the values to strings before hand. Something like:

cast(@PageNumber as varchar(255))

Note that when using cast() or convert(), you should always include a length for the type.

In sql sever, building dynamic sql with integer variables

2 Answers2