I have the following sample script which does a Dynamic Pivot on a table as follows:
Create Table Ex
BillName Varchar(5) )
--Inserting Sample Data
Insert into Ex
Select 1, 'Amit'
Select 2, 'Amit'
Select 3, 'BBB'
Select 4, 'Amit'
Declare @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
(MAX(BillName) For InvoiceNumber IN ('+@cols+') ) As pvt'
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.
For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden