call from one stored procedure that returns result set to another stored procedure for combining that result set
Date : March 29 2020, 07:55 AM
fixed the issue. Will look into that further I have a stored procedure that gives one input parameter. It compares the values of two colmns in the same table and show if it is 'available' or 'not available'. It returns all the rows (30) of that table as result set. When I run it, it is like -- Declare a table variable that captures
-- the output from your SP
declare @T table
(
ID int,
A varchar(15)
)
-- Add rows from SP to @T
insert into @T
exec GetValues
-- Query the table variable
select T1.ID as ID1,
T2.ID as ID2,
case when 'available' in (T1.A, T2.A)
then 'available'
else 'not available'
end as A
from @T as T1
inner join @T as T2
on T1.ID + 1 = T2.ID
where T1.ID % 2 = 1
ID1 ID2 A
----------- ----------- -------------
1 2 available
3 4 not available
5 6 available
7 8 not available
9 10 available
11 12 available
|
SQL Server stored procedure called from another stored proc returns command completed succesfully but no result set
Date : March 29 2020, 07:55 AM
like below fixes the issue First create a temporary table that captures data obtained from executing first procedure . Then use Select statement to retrieve desired data from temporary table. Don't forget to use drop the temporary table table after Select statement else next time you execute the procedure error will be thrown saying table already exist . On other hand you can use table variable to avoid dropping table as scope of table variable is limited to lifetime of the containing stored procedure. ` Insert into @temptable
Exec sprcdre1
Select * from @temptable
|
Stored procedure returns int instead of result set
Tag : chash , By : chudq7
Date : March 29 2020, 07:55 AM
help you fix your problem Entity Framework can't tell what your stored procedure is returning. I've had success creating a table variable that mirrors the data from your SELECT statement. Just insert into the table variable then do a select from that table variable. EF should pick it up.
|
My stored procedure keeps saying it only returns an int and not a result
Tag : chash , By : JoeKaras
Date : March 29 2020, 07:55 AM
help you fix your problem Pretty sure your entire procedure can be reduced to a single query along these lines. This of course then begs the question if this should be turned into an inline table valued function. ALTER procedure [dbo].[cGetGrossMargin_sp]
(
@OrderHeaderID INT
)
AS
set nocount on;
select SalesTax = isnull(oh.SalesTax * .01, 0)
, TotalPrice = isnull(sum(MaterialPrice * SellQuantity), 0)
, LaborCost = isnull(sum(isnull(LaborCost, 0) * SellQuantity), 0)
, MaterialCost = isnull(sum(isnull(MaterialCost, 0) * PurchaseQuantity), 0)
, TaxAssesed = isnull(sum(isnull(od.MaterialCost,0) * isnull(od.PurchaseQuantity,0)) * isnull(oh.SalesTax * .01, 0), 0)
, TotalPrice = Case when oh.SalesTax /*if sales tax is greater than 0 then the whole calculation is*/ > 0
then 1 - ((isnull(sum(isnull(MaterialCost, 0) * PurchaseQuantity), 0) + isnull(sum(isnull(LaborCost, 0) * SellQuantity), 0) + isnull(sum(isnull(od.MaterialCost,0) * isnull(od.PurchaseQuantity,0)) * isnull(oh.SalesTax * .01, 0), 0)) / isnull(sum(MaterialPrice * SellQuantity), 0))
else 0.00
end
from OrderHeader oh
join OrderDetail od on od.OrderHeaderID = oh.OrderHeaderID
left join Vendor v on v.VendorID = od.VendorID
and v.SalesTaxed = 1 --changed to left join in case there are no rows
where oh.OrderHeaderID = @OrderHeaderID
group by oh.SalesTax
|
Datareader returns no results in VS yet Stored Procedure returns multiple result sets in Sql Server
Date : March 29 2020, 07:55 AM
|