views:

476

answers:

3

I have the following dynamic sql statement where I want to add @StartRowIndex + @MaximumRows and subtract 1 from it. I am unclear on where to put the single quotes in the statement. Here it is:

SET @sql = @sql + ' SELECT *
 FROM
LicenseInfo
WHERE RowNum 
BETWEEN ' + @StartRowIndex + ' AND ' + 
'(' + @StartRowIndex + @MaximumRows + ')'  -  1
+ ' ORDER BY cnt desc'
+2  A: 

Create new variable @EndRowIndex and calculate it before you construct the dynamic sql statement.

Something like:

DECLARE @EndRowIndex int

SET @EndRowIndex = @StartRowIndex + @MaximumRows - 1

SET @sql = @sql + ' SELECT *
 FROM
LicenseInfo
WHERE RowNum 
BETWEEN ' + @StartRowIndex + ' AND ' + @EndRowIndex 
+ ' ORDER BY cnt desc'
Tomas
I did this, but I can't actually tell if it worked (I think it will), but I am getting this annoying error "Conversion failed when converting the nvarchar value" followed by part of my dynamic sql statement (the value stored in @sql). What could be causing this? Could by statement be too long? I am storing it in varchar(MAX)
Xaisoft
+2  A: 

You need to cast the int parameters into varchar

SET @sql = @sql + ' SELECT *
 FROM
LicenseInfo
WHERE RowNum 
BETWEEN ' + @StartRowIndex + ' AND ' + 
'(' + CAST(@StartRowIndex as varchar(10)) + CAST(@MaximumRows as varchar(10)) + ') - 1  
 ORDER BY cnt desc'
Ender
Is this what is causing the conversion failed message? If I cast, how does it add two ints? Should I firs to the EndRowIndex as suggested by Thomas and then cast that.
Xaisoft
Yes. It will be calculating fine ,try this : DECLARE @cmd varchar(200) SELECT @cmd = 'SELECT 1 + 1;';EXEC(@cmd);
Ender
I did it and it worked, I am just a little unclear on why the cast is needed?
Xaisoft
Is it because it is dynamic sql? and I am storing it in a varchar field.
Xaisoft
The query statement you are building is in string representation. MS SQL does not have implicit cast to string for the int type, so you have to cast it to concatenate it with the SQL command you're building.
Ender
Ok, that clarifies it some more. When SQL executes it, how does it actually know that I want to add the numbers and not concatenate them? I guess that is what I am trying to understand. Thanks for the help.
Xaisoft
+1  A: 

Declare a variable, do the calculation and CAST it to varchar when generating the SQL statement

DECLARE @LastRowIndex int

SET @LastRowIndex = @StartRowIndex + @MaximumRows - 1

SET @sql = @sql + ' 
SELECT *
FROM LicenseInfo
WHERE 1=1
AND RowNum BETWEEN ' + CAST (@StartRowIndex as VarChar) + 
' AND ' + CAST (@LastRowIndex as VarChar)
+ ' ORDER BY cnt DESC'

You have to cast in order to let SQL Server concatenate string values, otherwise it will try to convert the nVarChar to number and try to add them as numerics.

Raj More
Thanks as well.
Xaisoft
If I understand you correctly, the cast is necessary because I am storing the sql statement in a varchar(max) field. When I execute the dynamic sql, it will execute it correctly, by adding the variables together, correct? It is a little confusing because I actually want sql server to add the numbers together.
Xaisoft
SQL Server does some strange things like not letting you add parameters on the same command as passing them. `Exec SPSomething @Value1 + @Value2` will bomb. You have to add them beforehand and then pass them. I explicitly cast variables whenever required.
Raj More