Solved

How to Set the Value of a Parameter Within in a Stored Procedure

Posted on 2009-04-07
6
179 Views
Last Modified: 2012-05-06
I need to set the value of @ToDate
Can I set the parameter value within the stored procedure statement instead of passing it into the stored procedure?

ALTER PROCEDURE [dbo].[usp_getWatchList]
      -- Add the parameters for the stored procedure here
      @ToDate datetime    
AS

@ToDate=(SELECT MAX(QuoteDate) FROM Table1 WHERE MarketID=1)
-- This does not work. How can I set the value of @ToDate ?

SELECT * FROM Table2
WHERE Table2.QuoteDate = @ToDate
0
Comment
Question by:Dovberman
[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
  • 3
6 Comments
 
LVL 28

Expert Comment

by:TextReport
ID: 24088049
You can etiher
SELECT @ToDate=(SELECT MAX(QuoteDate) FROM Table1 WHERE MarketID=1)
or
SET @ToDate=(SELECT MAX(QuoteDate) FROM Table1 WHERE MarketID=1)
Cheers, Andrew
0
 

Author Comment

by:Dovberman
ID: 24089104
I am not sure how to use this suggestion.

The SP usp_getWatchList shown in the code snippet compiles.

However,
exec usp_getWatchList
@UserID = 'BA275A68-9E77-4301-9D97-7CC7DFECDA0D',
@FromDate = '2009-01-23'  

displays the following error:

Msg 201, Level 16, State 4, Procedure usp_getWatchList, Line 0
Procedure or function 'usp_getWatchList' expects parameter '@ToDate', which was not supplied.


What am I missing?
thanks,

ALTER PROCEDURE [dbo].[usp_getWatchList]
	-- Add the parameters for the stored procedure here
	@UserID varchar(50), 
	@ToDate datetime,   
	@FromDate datetime 
 
AS
 
/* 
exec usp_getWatchList
@UserID = 'BA275A68-9E77-4301-9D97-7CC7DFECDA0D',
@ToDate = '2009-02-27', 
@FromDate = '2009-01-23' 
*/
 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
 
    -- Insert statements for procedure here
SET @ToDate=(SELECT MAX(QuoteDate) FROM DownLoadDates WHERE MarketID=1)
 
 
SELECT 
w.SymbolID
, s1.SymbolName
, sym.SecName
, w.MarketID
, mkt.MarketName 
, w.PickDate
, DATEDIFF(day, w.PickDate, GETDATE()) AS DaysonList 
, s1.ClosePrice AS PickPrice
, s2.ClosePrice AS CurrentPrice
, (SELECT ClosePrice FROM StockHist  
WHERE QuoteDate = @FromDate AND SymbolID = w.symbolID) AS StartPrice  
, s2.ClosePrice - s1.ClosePrice AS PriceChange   
, ((s2.ClosePrice - s1.ClosePrice) *100) /s1.ClosePrice AS PctChange
FROM Watchlist w INNER JOIN
   StockHist s1 ON w.SymbolID = s1.SymbolID AND w.PickDate = s1.QuoteDate INNER JOIN
   StockHist s2 ON w.SymbolID = s2.SymbolID AND s2.QuoteDate = @ToDate INNER JOIN 
   Symbol sym ON w.SymbolID = sym.SymbolID  INNER JOIN 
   Market mkt ON mkt.MarketID = sym.MarketID  
WHERE w.UserID=@UserID 
ORDER BY PriceChange DESC 
END

Open in new window

0
 
LVL 28

Expert Comment

by:TextReport
ID: 24089244
You have not passed a value for the parameter @ToDate as demonstrated in Line 12, Line 22 is not required, this would be used for an output parameter and the way you are rnning the code you should be passing the @ToDate
Cheers, Andrew
exec usp_getWatchList
@UserID = 'BA275A68-9E77-4301-9D97-7CC7DFECDA0D',
@ToDate = '2009-02-27', 
@FromDate = '2009-01-23'

Open in new window

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Dovberman
ID: 24089595
My intention is not to pass the @ToDate but to declare and set the @ToDate within the Stored Procedure.

Is this possible?

0
 
LVL 28

Accepted Solution

by:
TextReport earned 250 total points
ID: 24089625
Yes remove it from the paramters section and change it to a DECLARE
Cheers, Andrew
ALTER PROCEDURE [dbo].[usp_getWatchList]
	-- Add the parameters for the stored procedure here
	@UserID varchar(50), 
	@FromDate datetime 
 
AS
DECLARE 	@ToDate datetime
 
/* 
exec usp_getWatchList
@UserID = 'BA275A68-9E77-4301-9D97-7CC7DFECDA0D',
@ToDate = '2009-02-27', 
@FromDate = '2009-01-23' 
*/
 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
 
    -- Insert statements for procedure here
SET @ToDate=(SELECT MAX(QuoteDate) FROM DownLoadDates WHERE MarketID=1)
 
 
SELECT 
w.SymbolID
, s1.SymbolName
, sym.SecName
, w.MarketID
, mkt.MarketName 
, w.PickDate
, DATEDIFF(day, w.PickDate, GETDATE()) AS DaysonList 
, s1.ClosePrice AS PickPrice
, s2.ClosePrice AS CurrentPrice
, (SELECT ClosePrice FROM StockHist  
WHERE QuoteDate = @FromDate AND SymbolID = w.symbolID) AS StartPrice  
, s2.ClosePrice - s1.ClosePrice AS PriceChange   
, ((s2.ClosePrice - s1.ClosePrice) *100) /s1.ClosePrice AS PctChange
FROM Watchlist w INNER JOIN
   StockHist s1 ON w.SymbolID = s1.SymbolID AND w.PickDate = s1.QuoteDate INNER JOIN
   StockHist s2 ON w.SymbolID = s2.SymbolID AND s2.QuoteDate = @ToDate INNER JOIN 
   Symbol sym ON w.SymbolID = sym.SymbolID  INNER JOIN 
   Market mkt ON mkt.MarketID = sym.MarketID  
WHERE w.UserID=@UserID 
ORDER BY PriceChange DESC 
END 
Open in New Window Select All 

Open in new window

0
 

Author Closing Comment

by:Dovberman
ID: 31567542
Just what I needed. Thanks,
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…
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…

739 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