[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

database level stored procedure with parameters

Posted on 2012-09-18
5
Medium Priority
?
521 Views
Last Modified: 2012-09-18
I have the following stored procedure that takes the value of DeliverID and ShipID and determines the correct shipping rate.  This stored procedure work fine.

ALTER PROCEDURE find_rate
AS

DECLARE @ShipID int = 1
DECLARE @DID int = 2

SELECT tbl_GenShip.ShipID
            ,tbl_GenDairy.Dairy
            ,tbl_Rate.Rate
                  
FROM  tbl_Rate INNER JOIN
      tbl_GenShip ON tbl_Rate.ShipID = tbl_GenShip.ShipID INNER JOIN                  tbl_GenDairy ON tbl_Rate.DeliveryID = tbl_GenDairy.DeliveryID
                        
                              
WHERE  (tbl_Rate.ShipID = @ShipID) AND (tbl_Rate.DeliveryID= @DID)

Go

Exec find_rate

What I would like to do is use this stored procedure at the database level when the table gets updated.  

I have a table tbl_testLoad that has the ShipID and DeliveryID I would like to pass those as paramaters (and the table key LoadID) to the above stored procedure instead of the fixed values I have now (DECLARE @ShipID int = 1   DECLARE @DID int = 2 ).  

I would then like the stored procedure to add the output "rate" to the field rate in tbl_testLoad.  

Does anyone know a way to do this?

Thank you.
0
Comment
Question by:rtay
  • 2
  • 2
5 Comments
 
LVL 21

Expert Comment

by:Dale Burrell
ID: 38411936
ALTER PROCEDURE find_rate
(
  @ShipID int = 1
  , @DID int = 2
)
AS
...


exec find_rate {shipid}, {did}
0
 
LVL 9

Accepted Solution

by:
borki earned 2000 total points
ID: 38411940
Something like this perhaps:

ALTER PROCEDURE find_rate
   (
   @ShipID int
   ,@DID int
   )

AS

--DECLARE @ShipID int = 1
--DECLARE @DID int = 2

DECLARE @Rate money  -- change to correct type

SELECT tbl_GenShip.ShipID
            ,tbl_GenDairy.Dairy
            ,@Rate = tbl_Rate.Rate
FROM  tbl_Rate INNER JOIN
   tbl_GenShip ON tbl_Rate.ShipID = tbl_GenShip.ShipID INNER JOIN
   tbl_GenDairy ON tbl_Rate.DeliveryID = tbl_GenDairy.DeliveryID
WHERE  (tbl_Rate.ShipID = @ShipID) AND (tbl_Rate.DeliveryID= @DID)

-- Fix insert statement with appropriate other values, defaults)
INSERT INTO tbl_testLoad (Rate) VALUES (@Rate)
0
 
LVL 5

Author Closing Comment

by:rtay
ID: 38412084
thank you
0
 
LVL 21

Expert Comment

by:Dale Burrell
ID: 38412105
Which is exactly the same solution I posted?
0
 
LVL 9

Expert Comment

by:borki
ID: 38412350
Dale, you missed this bit:

"I would then like the stored procedure to add the output "rate" to the field rate in tbl_testLoad"
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Suggested Courses

834 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