Solved

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

Posted on 2014-11-17
3
101 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 50

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

734 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