Im trying to make the code below into a stored procedure. I would like to avoid defining it as one long string... This has developed into a problem: Sometimes the inputs in my procedure are defined as strings, and other times they are not (at least i think that is the problem)
[select pvt.Dates , pvt.1stProduct , pvt.2ndproduct , JR.discountd_prices from ( select Name, Dates, Prices from dbo.tbSourceData ) tb pivot ( max(Prices) for Name in (1stProduct, 2ndProduct) ) pvt left join tbSourceData JR on JR.Dates = pvt.Dates and JR.Name = '1stProduct' where pvt.Dates > getdate()-100]
I have rewritten this into the stored procedure:
alter proc dbo.spTest1 ( @ProductID1 Varchar(15) = '1stProduct', @ProductID2 varchar(15) = '2ndProduct', @DaysBack int = 100 ) as select pvt.Dates , pvt.@ProductID1 , pvt.@ProductID2 , JR.discountd_prices from ( select Name, Dates, Prices from dbo.tbSourceData ) tb pivot ( max(Prices) for Name in (@ProductID1, @ProductID2) ) pvt left join tbSourceData JR on JR.Dates = pvt.Dates and JR.Name = @ProductID1 where pvt.Dates > getdate()-@DaysBack
As an example in the line which begins with "pivot" i would like to use two product inputs, but as you see above, when defining column names for a pivot-table, they should not be written as 'Column1' and 'Column2' but as Column1 and Column2, preferably [Column1],[Column2].
Can anybody help me fix this, without turning the code into one long string?
- If i have forgotten anything else above, or i could do somethings in a smarter way, please let me know im new in all this