?
Solved

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

Posted on 2014-11-17
3
Medium Priority
?
104 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 2000 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 51

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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

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…
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

743 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