sql - Unable to resolve: 'Must declare the scalar variable "@Parameter"`' -

i need enter parameter in pivot down below in stored procedure. states, "must declare scalar variable "@pweek"."

i have tried declare every way, unable figure out. else fine on coding numbers want, way want them. need able put in ssrs , able enter parameter. thank you.

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  set nocount on  declare @accountref_fullname  nvarchar(max)    select  @accountref_fullname = coalesce(@accountref_fullname + ',', '') + '['+ accountref_fullname + ']'   ( select distinct accountref_fullname  (select accountref_fullname journalcreditlinedetail)journalcreditline union (select accountref_fullname journaldebitlinedetail) union (select accountref_fullname txnexpenselinedetail) union (select accountref_fullname depositlinedetail) union (select discountaccountref_fullname [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     * account_summary_view week = @pweek    )account_data '  +'  pivot (' + '  sum(amount)     accountref_fullname in ('+ @accountref_fullname +')' +'  )  pivottable '   --+' )data '  exec (@sql)   ; 

change this:

where week = @pweek 

to this:

where week = ''' + cast(@pweek varchar(31)) + ''' 

explanation: you're using dynamic sql; putting string contains sql command , executing it. dynamic string doesn't have access parameters , variables declared outside of itself. doesn't recognize @pweek parameter. didn't declare in dynamic sql string.

when way showed you, concatenating value of @pweek string, that, instance if passed value of 1 @pweek, string gets executed contain where week = '1', sql has no trouble understanding.

you doing this line in existing code:

 accountref_fullname in ('+ @accountref_fullname +')'