Stacie
asked on
Create a store procedure that return value from a table in a database
I need to create a store procedure that return values from a table.. Need help I'm new to that.
Create a stored procedure which returns the following data.
JobID
,UniqueID
,ProdPubCode
,CustomerID
,JobDescription
,SellPriceDevelopment + SellPricePrint AS 'ExtendedAmount'
,TotalSellPrice AS 'ExtendedAmountTest'
,Quantity
,DateShipFinal
,DateProjectedInvoice
,ModelYear
,Make
,Model
,VehicleType
,Product
,ProductProdLanguage
,ProdVersion
,Market
Name this procecure NonKitRevenueForecast.
Create a stored procedure which returns the following data.
JobID
,UniqueID
,ProdPubCode
,CustomerID
,JobDescription
,SellPriceDevelopment + SellPricePrint AS 'ExtendedAmount'
,TotalSellPrice AS 'ExtendedAmountTest'
,Quantity
,DateShipFinal
,DateProjectedInvoice
,ModelYear
,Make
,Model
,VehicleType
,Product
,ProductProdLanguage
,ProdVersion
,Market
Name this procecure NonKitRevenueForecast.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I think you already got most part of it, adding up to what is mentioned by Phillip.
CREATE PROCEDURE NonKitRevenueForecast
AS
SELECT
JobID
,UniqueID
,ProdPubCode
,CustomerID
,JobDescription
,SellPriceDevelopment + SellPricePrint AS 'ExtendedAmount'
,TotalSellPrice AS 'ExtendedAmountTest'
,Quantity
,DateShipFinal
,DateProjectedInvoice
,ModelYear
,Make
,Model
,VehicleType
,Product
,ProductProdLanguage
,ProdVersion
,Market
FROM yourtable T
JOIN products P ON T.ProductID = P.ProductID
JOIN customerstable C ON T.CustomerID = C.CustomerID
WHERE <somecondition>
GO
Something like above, basically you need to know the joins (or links) between different tables you want to get the info from and use the same.
HTH.
CREATE PROCEDURE NonKitRevenueForecast
AS
SELECT
JobID
,UniqueID
,ProdPubCode
,CustomerID
,JobDescription
,SellPriceDevelopment + SellPricePrint AS 'ExtendedAmount'
,TotalSellPrice AS 'ExtendedAmountTest'
,Quantity
,DateShipFinal
,DateProjectedInvoice
,ModelYear
,Make
,Model
,VehicleType
,Product
,ProductProdLanguage
,ProdVersion
,Market
FROM yourtable T
JOIN products P ON T.ProductID = P.ProductID
JOIN customerstable C ON T.CustomerID = C.CustomerID
WHERE <somecondition>
GO
Something like above, basically you need to know the joins (or links) between different tables you want to get the info from and use the same.
HTH.
Open in new window