HiI have the following sample script which does a Dynamic Pivot on a table as follows:--Creating TableCreate Table Ex(InvoiceNumber int, BillName Varchar(5) )--Inserting Sample Data Insert into ExSelect 1, 'Amit'Union ALLSelect 2, 'Amit'Union ALLSelect 3, 'BBB'Union ALLSelect 4, 'Amit'--Dynamic PivotDeclare @cols Varchar(max), @sql Varchar(max)Select @cols = STUFF((Select ', [' + CAST(InvoiceNumber As Varchar(10) ) + ']' From Ex For XML Path('')), 1, 2, '')Set @sql = 'Select '+@cols+' From Ex Pivot (MAX(BillName) For InvoiceNumber IN ('+@cols+') ) As pvt'Execute (@sql)I have done Parameterized queries where parameters are passed into the where clause. But, what I wanted to ask is if we can change this whole dynamic pivot into a Parameterized Query where parameters can be passed into the "Select" part of the query and then Executed using "sp_Executesql".I have read quite a bit about Parameterized Queries on the internet but have only seen blog posts or explanations where Parameters are passed into the Where Clause of the Parameterized Query. Is it possible to Parameterize the "Select" or any other parts of the query??...Any insight you might have on Parameterized queries would be really helpful. It would be even better if you could reccommend some good reads on this topic.Looking forward to replies. Vinu Vijayan
Converting Dynamic SQL to Parameterized Query
Posted - 2012-08-17 : 03:18:33
