how can do stored procedure dynamically

[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?

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

pls advice

Maideen

[pritaeas]

You could pass the start and end year as two parameters, and based on that generate your query within the stored procedure.

[Maideen]

Hi pirtaeas

Below is working fine only for single value as per your advice. I could not do range that means start and end. How?

Pls advice

Maideen

ALTER PROCEDURE [dbo].[Z_usp_Circ_Details_Year] 
    @vYEAR varchar(100)

AS
BEGIN
SET NOCOUNT ON
Declare @sSQL varchar(1000)

Set @sSQL = 'SELECT * FROM 
(SELECT 
 MEMBER,[LANGUAGE],[TYPE],[PLATFORM],copies,CATEGORY,MAINAREA,MonthName,YearNo
 FROM Z_tbl_Circulation_Details) TableYearNo
pivot (Sum(copies) for YearNo IN (['+@vYEAR+'])) pivotTable'

exec(@sSQL)

[pritaeas]

Something like this, but untested:

ALTER PROCEDURE [dbo].[Z_usp_Circ_Details_Year] 
    @YearStart VARCHAR(4),
    @YearEnd VARCHAR(4)
AS
BEGIN
    DECLARE @sSQL VARCHAR(1000)
    SET @sSQL = 'SELECT * FROM (SELECT MEMBER, [LANGUAGE], [TYPE], [PLATFORM], copies, CATEGORY, MAINAREA, MonthName, YearNo FROM Z_tbl_Circulation_Details) TableYearNo PIVOT (SUM(copies) FOR YearNo BETWEEN ' + @YearStart + ' AND ' + @YearAnd) pivotTable'

    EXEC(@sSQL)
END

[Maideen]

hi pritaease

I have changed according to you. I have used print command,But error is

SELECT * FROM
(SELECT
MEMBER,[LANGUAGE],[TYPE],[PLATFORM],copies,CATEGORY,MAINAREA,MonthName,YearNo
FROM Z_tbl_Circulation_Details) TableYearNo
pivot (Sum(copies) for YearNo BETWEEN (2012 AND 2013))) pivotTable
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'BETWEEN'.

pls any idea

maideen

ALTER PROCEDURE [dbo].[Z_usp_Circ_Details_Year] 
    --@vYEAR varchar(100),
    @YearStart VARCHAR(4),
    @YearEnd VARCHAR(4)

AS
BEGIN
SET NOCOUNT ON
Declare @sSQL varchar(1000)

Set @sSQL = 'SELECT * FROM 
(SELECT 
 MEMBER,[LANGUAGE],[TYPE],[PLATFORM],copies,CATEGORY,MAINAREA,MonthName,YearNo
 FROM Z_tbl_Circulation_Details) TableYearNo
pivot (Sum(copies) for YearNo BETWEEN ('+ @YearStart+' AND '+ @YearEnd+'))) pivotTable'

print @ssql

exec(@sSQL)
END

[isozworld]

In the Pivot syntax you cant use Between as like a Where condition.
change your code like

 YearNo IN  ([2011],[2013],[2014])

The code is here (not tested)

ALTER PROCEDURE [dbo].[Z_usp_Circ_Details_Year] 
    @vYEAR varchar(100)

AS
BEGIN
SET NOCOUNT ON
Declare @sSQL varchar(1000)

exec(N'SELECT * FROM 
(SELECT 
 MEMBER,[LANGUAGE],[TYPE],[PLATFORM],copies,CATEGORY,MAINAREA,MonthName,YearNo
 FROM Z_tbl_Circulation_Details) TableYearNo
pivot (Sum(copies) for YearNo IN  ([2011],[2013],[2014])) pivotTable'
)