Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Creating a Stored Procedure that accepts parameters Expand / Collapse
Author
Message
Posted Tuesday, November 20, 2012 4:17 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, December 02, 2012 9:30 AM
Points: 17, Visits: 49
Err
Post #1386812
Posted Tuesday, November 20, 2012 4:25 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, March 14, 2014 2:19 AM
Points: 2,820, Visits: 3,916
So whats your requirement then ..Sorry i havent able to retrieve anyting from your miles- long script . you only need to post the stpred proc (which is creating prob or need assistence) here

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1386821
Posted Tuesday, November 20, 2012 4:28 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, April 10, 2014 10:18 AM
Points: 1,769, Visits: 2,800
It's definitely one of the most complete examples of DDL I've seen on here - thanks.

But could do with seeing how far you've got with the proc so far.
Why the restriction on the result not being a integer, and what should happen in the case it is?

Thanks
Post #1386823
Posted Tuesday, November 20, 2012 4:33 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, February 10, 2014 1:53 AM
Points: 1,219, Visits: 672
Waste of time putting all that on here.

Start by writing the sql script that would perform this task and work from there.

I may be worng but this sounds fairly straightforward

Graeme



Post #1386828
Posted Tuesday, November 20, 2012 4:51 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 7:37 AM
Points: 860, Visits: 2,323
as i'm in a bit of a lul at the moment I thought I've have a crack at it, and its one of the most comprehensive set of DDL I've seen on SSC, even if it is a little overkill.

One think I did notice is that the Extended cost is set as a Decimal(18,2) and I believe money has 4 Decimal places, so you might get truncation occuring.

You also need to decide what to do if there is a rounding issue on the Unit cost calculation, eg 200.0000/3 is 66.6667, rounding up will give incorrect values when multiplied back out, rounding up 66.67*3 = 200.01 and if you round down its 199.98.

The Only column I cant seem to get data for is the ExtendedPrice, but this is a bare bones stab at what you might be looking for.

CREATE PROCEDURE InsertSalesOrderPart 
@OrderNumber Int
,@PartNumber Int
,@Quantity Int
,@ExtendedCost Money
AS

Insert into SalesOrderPart
(OrderNumber
,PartID
,Quantity
,UnitPrice
,ExtendedPrice
,UnitCost
,ExtendedCost
)
Select
@OrderNumber
,p.PartID
,@Quantity
,p.Price
,0 ExtendedPrice -- WHERE DOES THIS COME FROM
,@ExtendedCost/@Quantity UnitCost
,@ExtendedCost
From Part p
where
P.PartID=@PartNumber

I would suggest runing this first and check the data you get for a specific set of paramaters.

DECLARE	@OrderNumber Int = <replace with orderno>
,@PartNumber Int = <replace with partId>
,@Quantity Int = <replace with Quantity>
,@ExtendedCost Money =<replace with cost>

Select
@OrderNumber
,p.PartID
,@Quantity
,p.Price
,0 ExtendedPrice --Where does this comefrom
,@ExtendedCost/@Quantity UnitCost
,@ExtendedCost
From Part p
where
P.PartID=@PartNumber



_________________________________________________________________________
SSC Guide to Posting and Best Practices
Post #1386839
Posted Tuesday, November 20, 2012 5:10 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, December 02, 2012 9:30 AM
Points: 17, Visits: 49
Sorry,

This is work related and something that someone asked from me. Be sure that I'll be posting more stuff. Thank you all for the reply.
Post #1386852
Posted Tuesday, November 20, 2012 9:47 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, December 02, 2012 9:30 AM
Points: 17, Visits: 49
errr
Post #1387011
Posted Tuesday, November 20, 2012 1:24 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:24 PM
Points: 11,990, Visits: 11,007
lex9120 (11/20/2012)
Also,

by any chance does anyone have an idea on how to perform the following?

I need to create a stored procedure named PartInventory that returns a grid (temp table).
The stored procedure takes one parameter named @PartID int and returns a table in the above format for that PartID. This shows the ins (receipts and returns) and outs (shipments and spoilage) for the given part. The InventoryType values are "Receipt", "Shipment", "Return", and "Spoilage".

And it also needs to returns these values in this order:


• PartID int
• InventoryType char(8)
• InventoryDate datetime
• Quantity int
• TotalCost decimal
• UnitPrice decimal


You did a fine job posting ddl and sample data. However, it is very unclear what you want from this query. Can you explain in detail and clearly what you want here? It would also help a lot if you could pick a PartID and post what the desired results would be.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1387131
Posted Saturday, December 01, 2012 12:55 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, December 02, 2012 9:30 AM
Points: 17, Visits: 49
errr
Post #1391663
Posted Saturday, December 01, 2012 12:57 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, December 02, 2012 9:30 AM
Points: 17, Visits: 49
I'm not sure exactly what the result would look like because they're just asking it from me. However, I'd like to see what the guru's would write based on the information. Please if possible.
Post #1391665
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse