如何动态地存储过程？ [英] How can do stored procedure dynamically?

问题描述

您好b $ b

我在以下存储过程中遇到问题。



以下商店程序运行正常。但修复了年份。



任何人都知道如何用户动态选择年份？



请咨询



Maideen

Hi
I have an issue in below stored procedure.

below Store procedure is working fine. But fixed the Year.

Any one know How can do the year dynamically select by user?

Pls advice

Maideen

SELECT * FROM ( SELECT salesEXE,SalesEXEName,name,gross,YearNo FROM vw_Pivot_Month_ALL) TableYearNo
pivot (Sum(gross) for [year] in ([2008],[2009],[2010],[2011],[2012],[2013],[2014],[2015],[2016],[2017],[2018],[2019],[2020])
)pivotTable

推荐答案

将年份作为存储过程参数传递并对此参数运行查询。

例如

Pass the year as a stored procedure parameter and run a query against this parameter.
E.g

Create SP_1 as
@Year as char(4)
AS
SELECT * FROM ( SELECT salesEXE,SalesEXEName,name,gross,YearNo FROM vw_Pivot_Month_ALL) TableYearNo
pivot (Sum(gross) for [year] in (@Year)
)pivotTable

DECLARE @Year nvarchar(max)='[2008],[2009],[2010],[2011],[2012],[2013],[2014],[2015],[2016],[2017],[2018],[2019],[2020]' -- here you can provide your dynamic values.
DECLARE @QUery nvarchar(max)='SELECT * FROM ( SELECT salesEXE,SalesEXEName,name,gross,YearNo FROM vw_Pivot_Month_ALL) TableYearNo
pivot (Sum(gross) for YearNo in ('+@Year+')
)pivotTable'

EXECUTE sp_executesql @QUery

这篇关于如何动态地存储过程？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！