reporting services - SSRS not returning any data with Table-valued parameters -


i'm struggling understanding why adding 1 or more nullable parameters before or after table-valued parameters return empty recordset.

i followed example here: http://geekswithblogs.net/gruffcode/archive/2012/06/21/using-table-valued-parameters-with-sql-server-reporting-services.aspx

then added parameters mimic environment , cannot more records unless replace @accounttype=null @accounttype=4

here code in dataset:

exec( ' declare @customeridlist report.integerlisttabletype ' + @customeridinserts + ' exec rpt_customertransactionsummary @startdate=''' + @startdate + ''', @enddate=''' + @enddate + ''', @customerids = @customeridlist') 

and sql trace (result: empty recordset):

exec sp_executesql n'exec( '' declare @customeridlist report.integerlisttabletype '' + @customeridinserts + '' exec rpt_customertransactionsummary @startdate='''''' + @startdate + '''''', @enddate='''''' + @enddate + '''''', @accounttype='''''' + @accounttype + '''''', @customerids = @customeridlist'')', n'@customeridinserts nvarchar(40), @startdate datetime, @enddate datetime, @accounttype int', @customeridinserts=n'insert @customeridlist values (304813)', @startdate='2013-01-01 00:00:00', @enddate='2014-12-31 00:00:00', @accounttype=null 

if replace null 4 , run query in sql management studio expected results:

exec sp_executesql n'exec( '' declare @customeridlist report.integerlisttabletype '' + @customeridinserts + '' exec rpt_customertransactionsummary @startdate='''''' + @startdate + '''''', @enddate='''''' + @enddate + '''''', @accounttype='''''' + @accounttype + '''''', @customerids = @customeridlist'')', n'@customeridinserts nvarchar(40), @startdate datetime, @enddate datetime, @accounttype int', @customeridinserts=n'insert @customeridlist values (304813)', @startdate='2013-01-01 00:00:00', @enddate='2014-12-31 00:00:00', @accounttype=4 

can please set me right direction?

thanks

rick

this stored proc:

alter procedure dbo.rpt_customertransactionsummary ( @startdate datetime, @enddate datetime, @customerids report.integerlisttabletype readonly, @accounttype int ) begin     set nocount on;     select t.playerid, t.inserteddatetime, t.amount    transactions t   inner join @customerids c on t.playerid = c.value   inserteddatetime between @startdate , @enddate    , (@accounttype null or t.accounttype=@accounttype)    end 

Comments

Popular posts from this blog

c++ - QTextObjectInterface with Qml TextEdit (QQuickTextEdit) -

javascript - angular ng-required radio button not toggling required off in firefox 33, OK in chrome -

xcode - Swift Playground - Files are not readable -