sql - combining two stored procedure to one stored procedure getting error -
i have stored procedure this:
alter procedure [dbo].[ibs_podiumsummerycount] @locid integer = null begin set nocount on; select sum(status_receved) receved, sum(status_parked) parked, sum(status_requested) requested, sum(status_requestedinprocess) requestinprocess (select case when (status = 0 ) 1 else 0 end status_receved, case when (status = 2) 1 else 0 end status_parked, case when (status = 3) 1 else 0 end status_requested, case when (status = 4) 1 else 0 end status_requestedinprocess transaction_tbl locid = @locid , dtime >= getdate()-1 , status in (0,2,3,4)) a; select l1.starttime, convert(varchar, getdate(), 108) time location_tbl l1 l1.locid = @locid end i have 1 more stored procedure this:
select dbo.podiumsummerytime( convert(decimal(10,1), avg(convert(numeric(18,2), datediff(ss, t.dtime, t.paicdate )))) ) avgparkingtime, dbo.podiumsummerytime( convert(decimal(10,1), avg(convert(numeric(18,2), datediff(ss, t.paydate, t.deldate )))) ) avgdelivarytime, dbo.podiumsummerytime( convert(decimal(10,1), avg(convert(numeric(18,2), datediff(ss, t.dtime, t.deldate )))) ) avgstaytime (select top 30 * transaction_tbl locid=@locid , dtime >= getdate()-2 order transactid desc ) t end i try write 2 stored procedure in 1 union getting error this:
all queries combined using union, intersect or except operator must have equal number of expressions in target lists.
i working sql server 2008, how can combine these 2 stored procedures 1 stored procedure...
just error says, union specific keyword use set of rows both results in. if rows different can't joined union.
there nothing stop putting both queries in 1 stored procedure, proc return 2 result sets.
for example:
alter procedure [dbo].[ibs_podiumsummerycount] @locid integer = null begin select * customers location = @locid; select * suppliers location = @locid; end will return results of both queries in separate results sets.
Comments
Post a Comment