We help IT Professionals succeed at work.

Create a store procedure that return value from a table in a database

128 Views
Last Modified: 2015-03-06
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.
Comment
Watch Question

Director, Practice Manager and Computing Consultant
Awarded 2014
Top Expert 2014
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Vitor Montalv√£oIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
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 ANSenior Tech Consultant

Commented:
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.