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

Re: how can do stored procedure dynamically 80 80

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

Re: how can do stored procedure dynamically 80 80

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)
Re: how can do stored procedure dynamically 80 80

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
Re: how can do stored procedure dynamically 80 80

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
Re: how can do stored procedure dynamically 80 80

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'
)
Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.19 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.