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.
yguyon28Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Can I answer this a bit more generally, as it may help in other situations?

Go to View - Template Browser.

You can see a list of some of the things you can do in SQL Server.

Expanded "Stored Procedure", and double click on "Create Procedure Basic Template".

Then, fill in the blanks.

So, you will see that what you need is

CREATE PROCEDURE dbo.NonKitRevenueForecast
AS
SELECT 
              -- This is where you will insert the above code
FROM
              -- This is where you insert the table names, and any JOINS
GO

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vitor MontalvãoMSSQL Senior EngineerCommented:
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

0
Valliappan ANSenior Tech ConsultantCommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.