January 18, 2012 at 4:26 pm
Hi,
I'm going through the below link and understanding the use sp_executesql .
http://sqlfool.com/?s=index+defrag
In above link, in the index defrag script, the below code is mentioned
IF @excludeMaxPartition = 1
BEGIN
SET @excludeMaxPartitionSQL = '
SELECT ' + CAST(@databaseID AS VARCHAR(10)) + ' AS [databaseID]
, [object_id]
, index_id
, MAX(partition_number) AS [maxPartition]
FROM [' + DB_NAME(@databaseID) + '].sys.partitions
WHERE partition_number > 1
AND [rows] > 0
GROUP BY object_id
, index_id;';
INSERT INTO #maxPartitionList
EXECUTE sp_executesql @excludeMaxPartitionSQL;
From BOL, it says that we should declare the parameters as nvarchar(max data type.
http://msdn.microsoft.com/en-us/library/ms188001.aspx
But In above script, it no where declared the parameters as nvarchar(max) but still it's working. Please advice.
Thanks
January 18, 2012 at 5:12 pm
Generally when passing multiple parameters to a dynamic SQL string you would declare and pass the parameters as the link specifies, Michelle's script doesn't but then it's only passing a string variable to execute.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 18, 2012 at 7:44 pm
You don't explicitly have to declare a @params variable in this particular example, but it's probably a good idea. Your code could be modified slightly
SET @excludeMaxPartitionSQL = '
SELECT CAST(@databaseID AS VARCHAR(10)) AS [databaseID]
, [object_id]
, index_id
, MAX(partition_number) AS [maxPartition]
FROM [DB_NAME(@databaseID)].sys.partitions
WHERE partition_number > 1
AND [rows] > 0
GROUP BY object_id
, index_id;';
set @params = '@databaseID int'
exec sp_executesql
@sql = @excludeMaxPartitionSQL,
@DatabaseID = @DatabaseIDD
[/Code]
SP_ExecuteSql works as a [better] alternative to just typing Exec (@sql), for reasons beyond the scope of this. One of the benefits of this method is that you can parameterize your dynamic sql. Parameterization can have many benefits, but the one that strikes me as most important is the ability to internalize much of the script/procedure in order to dramatically reduce the amount of time it has to spend generating plans. Here are a few examples
declare @Mysql nvarchar(max), @today datetime, @Myparams nvarchar(max)
select @Mysql = ''
select @today = GETDATE()
---Unparameterized dynamic sql string
select @Mysql = 'select ''Hello. the date today is ' + cast(@today as varchar(30)) + ''''
exec (@Mysql)
go
This is the basic way to do dnymaic sql, and it works. There are security concerns about doing it that way but it works. alternatively...
declare @Mysql nvarchar(max), @today datetime, @Myparams nvarchar(max)
select @Mysql = ''
select @today = GETDATE()
select @Mysql = 'Select ''Hello. the date today is @today'''
select @Myparams = '@today datetime'
exec sp_executesql
@Sql = @MySql, --Your sql statement itself
@params = @Myparams, --The declaration of the variable holding your dynamic sql variables
@today = @today --what your outside variable correlates with INSIDE the dynamicsql
This shows what BOL meant about parameterizing your queries. You put @today within the actual dsql string rather than leaving it out to be concatenated on, and then you have to assign those variables to your @params variable when you call the procedure. This can really be a big timesaver on procedures with a lot of dynamicsql becaue everytime dynamicsql runs without parameterized variables it basically says "i dont know what to do with this, so lets try something" Parameterizing you dynamic sql can help in a lot of ways, by making the state of the dynamic sql static enough for the query optimizer to really sink its teeth in to.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply