Solved

Stored Proc - Must Declare scalar variable

Posted on 2014-01-30
2
482 Views
Last Modified: 2014-01-30
Team - Below is my stored proc.

USE [mydesk]
GO
/****** Object:  StoredProcedure [dbo].[Chart]    Script Date: 01/30/2014 20:28:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[GanttChart] 

	@ProgramNumber AS nvarchar(max)
	
AS
BEGIN
	SET NOCOUNT ON;

;with years as(
  SELECT 2012 as y union all
  SELECT 2013 as y union all
  SELECT 2014 as y),
months as (
  SELECT 1 as m UNION all
  SELECT m+1 FROM months WHERE m < 12),
alldates as (
  SELECT DATEADD(year, y-1900, DATEADD(month, m-1, DATEADD(day, 0, 0))) AS dmy
  FROM months cross join years),
firstdays as (
  select [TBL_Programs#Request No], [TBL_Project & WBS#Request No], [Project Name], 
  DATEADD(month, DATEDIFF(month, 0, [PE Gate Date]), 0) [PE Gate Date], 
  DATEADD(month, DATEDIFF(month, 0, [PC Gate Date]), 0) [PC Gate Date], 
  DATEADD(month, DATEDIFF(month, 0, [LPA Gate Date]), 0) [LPA Gate Date], 
  DATEADD(month, DATEDIFF(month, 0, [LA Gate Date]), 0) [LA Gate Date], 
  DATEADD(month, DATEDIFF(month, 0, [LR Gate Date]), 0) [LR Gate Date]
  from Program_Status)
select fd.[TBL_Programs#Request No], 
fd.[TBL_Project & WBS#Request No], 
fd.[Project Name],
ad.dmy,
CASE 
 WHEN fd.[PE Gate Date] <= ad.dmy AND fd.[PC Gate Date] > ad.dmy THEN '[PE Gate Date]'
 WHEN fd.[PC Gate Date] <= ad.dmy AND fd.[LPA Gate Date] > ad.dmy THEN '[PC Gate Date]'
 WHEN fd.[LPA Gate Date] <= ad.dmy AND fd.[LA Gate Date] > ad.dmy THEN '[LPA Gate Date]'
 WHEN fd.[LA Gate Date] <= ad.dmy AND fd.[LR Gate Date] > ad.dmy THEN '[LA Gate Date]'
 WHEN fd.[LR Gate Date] = ad.dmy THEN '[LR Gate Date]' 
 ELSE '' 
END AS value
INTO #temp
from firstdays fd 
CROSS JOIN alldates ad 
ORDER BY 1,2,3,4,5;

Declare @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(convert(varchar(10), c.dmy, 121)) 
            FROM #temp c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT [TBL_Programs#Request No], [TBL_Project & WBS#Request No], [Project Name], ' + @cols + ' from 
            (
                select [TBL_Programs#Request No], [TBL_Project & WBS#Request No], [Project Name]
                    , dmy
                    , value
                from #temp 
           ) x
            pivot 
            (
                 max(value)
                for dmy in (' + @cols + ')
            ) p where [TBL_Programs#Request No] in (@ProgramNumber)'


execute(@query)

DROP TABLE #temp
End

Open in new window


I get Must declare scalar variable when i run it.

How do i fix this?
0
Comment
Question by:Manju
2 Comments
 
LVL 16

Accepted Solution

by:
Surendra Nath earned 500 total points
ID: 39820972
you have to re-write it as below

USE [mydesk]
GO
/****** Object:  StoredProcedure [dbo].[Chart]    Script Date: 01/30/2014 20:28:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[GanttChart] 

	@ProgramNumber AS nvarchar(max)
	
AS
BEGIN
	SET NOCOUNT ON;

;with years as(
  SELECT 2012 as y union all
  SELECT 2013 as y union all
  SELECT 2014 as y),
months as (
  SELECT 1 as m UNION all
  SELECT m+1 FROM months WHERE m < 12),
alldates as (
  SELECT DATEADD(year, y-1900, DATEADD(month, m-1, DATEADD(day, 0, 0))) AS dmy
  FROM months cross join years),
firstdays as (
  select [TBL_Programs#Request No], [TBL_Project & WBS#Request No], [Project Name], 
  DATEADD(month, DATEDIFF(month, 0, [PE Gate Date]), 0) [PE Gate Date], 
  DATEADD(month, DATEDIFF(month, 0, [PC Gate Date]), 0) [PC Gate Date], 
  DATEADD(month, DATEDIFF(month, 0, [LPA Gate Date]), 0) [LPA Gate Date], 
  DATEADD(month, DATEDIFF(month, 0, [LA Gate Date]), 0) [LA Gate Date], 
  DATEADD(month, DATEDIFF(month, 0, [LR Gate Date]), 0) [LR Gate Date]
  from Program_Status)
select fd.[TBL_Programs#Request No], 
fd.[TBL_Project & WBS#Request No], 
fd.[Project Name],
ad.dmy,
CASE 
 WHEN fd.[PE Gate Date] <= ad.dmy AND fd.[PC Gate Date] > ad.dmy THEN '[PE Gate Date]'
 WHEN fd.[PC Gate Date] <= ad.dmy AND fd.[LPA Gate Date] > ad.dmy THEN '[PC Gate Date]'
 WHEN fd.[LPA Gate Date] <= ad.dmy AND fd.[LA Gate Date] > ad.dmy THEN '[LPA Gate Date]'
 WHEN fd.[LA Gate Date] <= ad.dmy AND fd.[LR Gate Date] > ad.dmy THEN '[LA Gate Date]'
 WHEN fd.[LR Gate Date] = ad.dmy THEN '[LR Gate Date]' 
 ELSE '' 
END AS value
INTO #temp
from firstdays fd 
CROSS JOIN alldates ad 
ORDER BY 1,2,3,4,5;

Declare @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(convert(varchar(10), c.dmy, 121)) 
            FROM #temp c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT [TBL_Programs#Request No], [TBL_Project & WBS#Request No], [Project Name], ' + @cols + ' from 
            (
                select [TBL_Programs#Request No], [TBL_Project & WBS#Request No], [Project Name]
                    , dmy
                    , value
                from #temp 
           ) x
            pivot 
            (
                 max(value)
                for dmy in (' + @cols + ')
            ) p where [TBL_Programs#Request No] in (' + @ProgramNumber+ ')'


execute(@query)

DROP TABLE #temp
End
                                  

Open in new window


just put the @programNumber variable outside the string....
0
 
LVL 6

Author Closing Comment

by:Manju
ID: 39821287
Thanks a ton
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

A recent question popped up and the discussion heated up regarding updating a COMMENTS (TXT) field in a table using SSRS. http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/Q_27475269.html?cid=1572#a37227028 (htt…
A recent questions about how to add SSRS named instances, couldn't find any that talks about SQL server 2008, anyway I decided to help by creating some screen shots. The installation is straightforward, you just pop the SQL server 2008 installati…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question