Solved

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

Posted on 2014-11-17
3
96 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.
0
Comment
Question by:yguyon28
3 Comments
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 500 total points
ID: 40447630
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
 
LVL 47

Expert Comment

by:Vitor Montalvão
ID: 40449226
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
 
LVL 9

Expert Comment

by:Valliappan AN
ID: 40449227
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

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now