sql - Stored procedure not returning any data for the date passed -
i have stored procedure takes 1 parameter date
. return data date table.
alter procedure [dbo].[userreportdata] @as_ondate datetime begin declare @reportdate datetime --declare @opening int select * #temptable (select a.cuser_id, b.user_id, a.u_datetime reportdate inward_doc_tracking_trl inner join user_mst b on a.cuser_id = b.mkey , a.u_datetime = @as_ondate) x declare cur_1 cursor select cuser_id, user_id #temptable open cur_1 declare @cuser_id int declare @user_id int fetch next cur_1 @cuser_id, @user_id while (@@fetch_status = 0) begin select u_datetime inward_doc_tracking_trl u_datetime = @as_ondate update #temptable set reportdate = @reportdate cuser_id = @cuser_id , user_id = @user_id , fetch next cur_1 @cuser_id, @user_id end close cur_1 deallocate cur_1 select * #temptable drop table #temptable end
when execute stored procedure this, not rows returned
exec userreportdata '20160610'
but in table have rows date.
like running below query
select u_datetime inward_doc_tracking_trl u_datetime = '2016-06-10 14:56:11.000'
so, why there no records returned when executing stored procedure?
because u_datetime column contains date & time
one way handle in clause
and a.u_datetime >= @as_ondate , a.u_datetime < dateadd(day, 1, @as_ondate)
also might want consider converting query not use cursor
Comments
Post a Comment