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.

ASKER CERTIFIED SOLUTION

Phillip Burton

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Vitor Montalvão

Just write the SELECT query and put on top of that the CREATE PROCEDURE statement:

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 YourTableNameHere

Open in new window

Valliappan AN

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.