sql server - Not able to pass input parameters to stored procedure -
i have store procedure getting values database based upon search criteria passing ones input parameters procedure getting error @ @statusselection
create procedure [dbo].[tp_selecttransactionhistorysearch] ( @offsetrowno int, @fetchrowno int, @statusselection nvarchar(max), @isreviewed bit, @projectcasenumber varchar(max), @costpagenumber varchar(max), @transactiontypechange varchar(max), @descriptionchange varchar(max), @trasactioncreateonbegindate datetime, @transactioncreatedonenddate datetime, @transactionupdatedonbegindate datetime, @transactionupdateonenddate datetime, @itemid varchar(max) ) select th.transactionid, th.isreviewed, th.itemid, th.costpagenumber, th.comments, th.createdby, th.createddatetime, th.updatedby, th.updateddatetime, th.transactiondescription, th.transactiontypeid, iaccrualbyitem.projcase, iaccrualbyitem.uststat transactionstatusid, tstattype.name transactionstatustypename, tstattype.description transactionstatustypedescription, ttype.name transactiontypename, ttype.description transactiontypedescription, count(*) on () totalcount transactionhistory th inner join trvmvsddvw001.interface_files.dbo.dbatusta iaccrualbyitem on th.transactionid = cast(iaccrualbyitem.usttrnnbr int) left join dbo.transctionstatustype tstattype on tstattype.transactionstatustypeid = iaccrualbyitem.uststat left join dbo.transactiontype ttype on ttype.transactiontypeid = cast(th.transactiontypeid int) tstattype.name = @statusselection , th.isreviewed= @isreviewed , iaccrualbyitem.projcase=@projectcasenumber , th.costpagenumber=@costpagenumber , th.transactiondescription=@transactiontypechange , ttype.description=@descriptionchange , (th.createddatetime >= cast(@trasactioncreateonbegindate date)) , (th.createddatetime < cast(@transactioncreatedonenddate date)) , (th.updateddatetime >= cast( @transactionupdatedonbegindate date)) , (th.updateddatetime < cast(@transactionupdateonenddate date)) @th.statusid= 1 group th.transactionid,th.isreviewed,th.itemid,th.costpagenumber,th.comments,th.createdby,th.createddatetime, th.updatedby,th.updateddatetime, th.transactiondescription, th.transactiontypeid,iaccrualbyitem.projcase,transactionstatusid, tstattype.name,tstattype.description,ttype.name,ttype.description order th.transactionid,th.itemid,th.costpagenumber offset ( @offsetrowno-1 ) * @fetchrowno rows fetch next @fetchrowno rows but getting error
`msg 137, level 15, state 2, line 27 must declare scalar variable "@statusselection"`. i using sql server 2012 version
would 1 pls on solution , ideas on .. many .....
update:
declare @return_value int exec @return_value = [dbo].[tp_selecttransactionhistorysearch] @offsetrowno = 1, @fetchrowno = 1, @statusselection = n's', @isreviewed = null, @projectcasenumber = null, @costpagenumber = null, @transactiontypechange = null, @descriptionchange = null, @trasactioncreateonbegindate = '10-03-2013', @transactioncreatedonenddate = '20-03-2013', @transactionupdatedonbegindate = '20-05-2013', @transactionupdateonenddate = '04-06-2013', @itemid = null select 'return value' = @return_value go getting error msg 8114, level 16, state 5, procedure tp_selecttransactionhistorysearch, line 0 error converting data type varchar datetime.
try 1 -
create procedure [dbo].[tp_selecttransactionhistorysearch] ( @offsetrowno int, @fetchrowno int, @statusselection nvarchar(max), @isreviewed bit, @projectcasenumber varchar(max), @costpagenumber varchar(max), @transactiontypechange varchar(max), @descriptionchange varchar(max), @trasactioncreateonbegindate date, @transactioncreatedonenddate date, @transactionupdatedonbegindate date, @transactionupdateonenddate date, @itemid varchar(max) ) select th.transactionid , th.isreviewed , th.itemid , th.costpagenumber , th.comments , th.createdby , th.createddatetime , th.updatedby , th.updateddatetime , th.transactiondescription , th.transactiontypeid , iaccrualbyitem.projcase , iaccrualbyitem.uststat transactionstatusid , tstattype.name transactionstatustypename , tstattype.[description] transactionstatustypedescription , ttype.name transactiontypename , ttype.[description] transactiontypedescription , count(*) on () totalcount transactionhistory th join trvmvsddvw001.interface_files.dbo.dbatusta iaccrualbyitem on th.transactionid = cast(iaccrualbyitem.usttrnnbr int) left join dbo.transctionstatustype tstattype on tstattype.transactionstatustypeid= iaccrualbyitem.uststat left join dbo.transactiontype ttype on ttype.transactiontypeid = cast(th.transactiontypeid int) tstattype.name = @statusselection , th.isreviewed = @isreviewed , iaccrualbyitem.projcase = @projectcasenumber , th.costpagenumber = @costpagenumber , th.transactiondescription = @transactiontypechange , ttype.[description] = @descriptionchange , th.createddatetime between @trasactioncreateonbegindate , @transactioncreatedonenddate , th.updateddatetime between @transactionupdatedonbegindate , @transactionupdateonenddate , th.statusid = 1 group th.transactionid , th.isreviewed , th.itemid , th.costpagenumber , th.comments , th.createdby , th.createddatetime , th.updatedby , th.updateddatetime , th.transactiondescription , th.transactiontypeid , iaccrualbyitem.projcase , transactionstatusid , tstattype.name , tstattype.[description] , ttype.name , ttype.[description] order th.transactionid , th.itemid , th.costpagenumber offset (@offsetrowno - 1) * @fetchrowno rows fetch next @fetchrowno rows update #1:
set option, before executing query -
set dateformat dmy or, more preferable, use iso format yyyymmdd -
exec @return_value = [dbo].[tp_selecttransactionhistorysearch] ... @trasactioncreateonbegindate = '20130310', @transactioncreatedonenddate = '20132003', @transactionupdatedonbegindate = '20130520', @transactionupdateonenddate = '20130604', @itemid = null update #2:
declare @temp table ( string varchar(10) ) set dateformat dmy insert @temp (string) values ('10-03-2013'), ('20-03-2013'), ('20-05-2013'), ('04-06-2013') select cast(string date) @temp
Comments
Post a Comment