KnowledgeHub
Questions
Tags
Users
Search
Alex Rivera
|
Logout
Edit Question
Title
Body
A Dynamic SQL query from lobodava is: declare @sql nvarchar(4000) = N';with cteColumnts (ORDINAL_POSITION, COLUMN_NAME) as ( select ORDINAL_POSITION, COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = N'''+ @tableName + ''' and COLUMN_NAME like ''' + @columnLikeFilter + ''' ), cteValues (ColumnName, SumValue) as ( SELECT ColumnName, SumValue FROM (SELECT ' + @sumColumns + ' FROM dbo.' + @tableName + ') p UNPIVOT (SumValue FOR ColumnName IN (' + @columns + ') )AS unpvt ) select row_number() over(order by ORDINAL_POSITION) as ID, ColumnName, SumValue from cteColumnts c inner join cteValues v on COLUMN_NAME = ColumnName order by ORDINAL_POSITION' exec sp_executesql @sql --OR exec (@sql) Why did lobodava pick exec sp_executesql @sql and not exec(@sql) So what is the difference here? Is it better to use sp_executesql on recursive dynamic queries ? In other post they say sp_executesql is more likely to promote query plan reuse... So it helps in these kind of queries?
Tags (comma-separated)
Save Edits
Cancel