无法解析:'必须声明标量变量“@Parameter”

I need to enter a parameter in the pivot down below in the stored procedure. but it states, "Must declare the scalar variable "@pWeek"."

在存储过程中，我需要在下面的pivot中输入一个参数。但是它声明，“必须声明标量变量“@pWeek”。

I have tried to declare it every which way, but I am unable to figure this out. Everything else is fine on this coding as I get the numbers I want, the way I want them. I just need to be able to put this in an SSRS and be able to enter a parameter. Thank you.

我试着用各种方法来说明，但是我搞不清楚。在我得到我想要的数字和我想要的方式时，在这个编码中其他的一切都没问题。我只需要能够将它放入SSRS并能够输入一个参数。谢谢你！

GO
/****** Object:  StoredProcedure [dbo].[QB_ACCOUNT_SUMMARY]    Script Date: 6/19/2015 12:42:12 PM ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[QB_ACCOUNT_SUMMARY]

@pWeek int

as
SET NOCOUNT ON

declare @AccountRef_Fullname AS  NVARCHAR(MAX)



select  @AccountRef_Fullname = COALESCE(@AccountRef_Fullname + ',', '') + '['+ AccountRef_Fullname + ']'

from 
(
select distinct Accountref_fullname 
from (select accountref_fullname from journalcreditlinedetail)JournalCreditLine
union
(select accountref_fullname from journaldebitlinedetail)
union
(select accountref_fullname from txnexpenselinedetail)
union
(select accountref_fullname from depositlinedetail)
union
(select discountaccountref_fullname from [appliedtotxndetail])
) pAccountRef_Full_Name


declare  @Sql NVARCHAR(MAX)


set @Sql = N' SELECT [REAL WEEK], [LTWS WEEK], [REAL YEAR], [LTWS YEAR], [LTWS PERIODS], ' + @AccountRef_Fullname 

+ 'from 

(SELECT     * from Account_Summary_View
where Week = @pWeek
   )Account_Data '

+'  PIVOT ('
+ '  sum(amount)   for   AccountRef_FullName in ('+ @AccountRef_Fullname +')'
+'  )  AS PivotTable '


--+' )Data '
 exec (@Sql)

 ;

1 个解决方案

#1

Change this:

改变:

where Week = @pWeek

To this:

where Week = ''' + CAST(@pWeek AS varchar(31)) + '''

Explanation: You're using dynamic sql; putting together a string that contains a SQL command and then executing it. That dynamic string doesn't have access to parameters and variables that were declared outside of itself. So it doesn't recognize the @pWeek parameter. You didn't declare it IN the dynamic sql string.

说明:您使用的是动态sql;将包含SQL命令的字符串放在一起，然后执行它。这个动态字符串不能访问在其外部声明的参数和变量。所以它不能识别@pWeek参数。您没有在动态sql字符串中声明它。

When you do it the way I showed you, you are concatenating the VALUE of @pWeek into the string, so that, for instance if you passed a value of 1 to @pWeek, then the string that gets executed would contain where Week = '1', which SQL has no trouble understanding.

当您按照我所展示的方式进行操作时，您将把@pWeek的值连接到字符串中，因此，例如，如果您将值1传递给@pWeek，那么执行的字符串将包含where Week = '1'， SQL理解起来没有问题。

You were already doing exactly this with this line in your existing code:

您已经在现有代码中使用了这一行:

 AccountRef_FullName in ('+ @AccountRef_Fullname +')'

#1