Dynamic SQL sp_executesql problem
I have the following query:
DECLARE @sync_table_name nvarchar(500)
SET @sync_table_name = 'ContactTypes'
DECLARE @sync_batch_size bigint
SET @sync_batch_size = 2
DECLARE @sync_last_received_anchor timestamp
SET @sync_last_received_anchor = 1
DECLARE @sync_max_received_anchor timestamp
SET @sync_max_received_anchor = 18732866
DECLARE @sync_new_received_anchor timestamp
DECLARE @sync_batch_count int
DECLARE @sql NVARCHAR(500)
SET @sql = 'SELECT cast([version] as bigint) as [version], ROW_NUMBER() OVER(ORDER BY CAST([version] as BIGINT)) as RowNumber
INTO #Temp FROM '+@sync_table_name+' WHERE [version] > @min AND [version] <= @max ORDER BY [version];
SET @batchCountOUT = (SELECT COUNT(*) FROM #Temp) / @batchSize;
IF ((SELECT COUNT(*) FROM #Temp) >= @batchSize)
SELECT @newAnchorOUT开发者_开发百科 = #Temp.[version] FROM #Temp WHERE RowNumber = @batchSize;
IF ((SELECT COUNT(*) FROM #Temp) < @batchSize)
SELECT @newAnchorOUT = #Temp.[version] FROM #Temp WHERE RowNumber = (SELECT COUNT(*) FROM #Temp);
DROP TABLE #Temp'
DECLARE @sqlParam NVARCHAR(500)
SET @sqlParam = '@batchSize bigint, @min timestamp, @max timestamp, @newAnchorOUT timestamp OUTPUT, @batchCountOUT int OUTPUT'
EXECUTE sp_executesql @sql, @sqlParam, @batchSize = @sync_batch_size, @min = @sync_last_received_anchor, @max = @sync_max_received_anchor, @newAnchorOUT = @sync_new_received_anchor OUTPUT, @batchCountOUT = @sync_batch_count OUTPUT
SELECT @sync_new_received_anchor, @sync_batch_count
It's always complaining about Msg 102, Level 15, State 1, Line 8 Incorrect syntax near ')'.
BUT If I remove the second IF statement everything works ... is there something strange with sp_executesql and IF statements
Your @sql
value is more than 500 characters long. Increase the variable size.
your @sql= 716 characters. Just make it nvarchar(4000) and that way you will be safe
精彩评论