SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Creating a Stored Procedure that accepts parameters


Creating a Stored Procedure that accepts parameters

Author
Message
nzu8946
nzu8946
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 Visits: 49
Err
Bhuvnesh
Bhuvnesh
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13360 Visits: 4077
So whats your requirement then ..Sorry i havent able to retrieve anyting from your miles- long script :-P . 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;-)
Gazareth
Gazareth
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7770 Visits: 6045
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
Graeme100
Graeme100
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2374 Visits: 795
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



Jason-299789
Jason-299789
SSCertifiable
SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)

Group: General Forum Members
Points: 5017 Visits: 3232
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
nzu8946
nzu8946
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 Visits: 49
Sorry,

This is work related and something that someone asked from me. Be sure that I'll be posting more stuff. Sad Thank you all for the reply.
nzu8946
nzu8946
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 Visits: 49
errr
Sean Lange
Sean Lange
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63031 Visits: 17959
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 Modens 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)
nzu8946
nzu8946
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 Visits: 49
errr
nzu8946
nzu8946
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search