Solved

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

Posted on 2009-04-07
6
174 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
  • 3
  • 3
6 Comments
 
LVL 28

Expert Comment

by:TextReport
Comment Utility
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
Comment Utility
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
Comment Utility
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:Dovberman
Comment Utility
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
Comment Utility
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
Comment Utility
Just what I needed. Thanks,
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

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.…
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

772 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now