How to Set the Value of a Parameter Within in a Stored Procedure
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
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
ASKER
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-7
@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,
The SP usp_getWatchList shown in the code snippet compiles.
However,
exec usp_getWatchList
@UserID = 'BA275A68-9E77-4301-9D97-7
@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
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
Cheers, Andrew
exec usp_getWatchList
@UserID = 'BA275A68-9E77-4301-9D97-7CC7DFECDA0D',
@ToDate = '2009-02-27',
@FromDate = '2009-01-23'
ASKER
My intention is not to pass the @ToDate but to declare and set the @ToDate within the Stored Procedure.
Is this possible?
Is this possible?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Just what I needed. Thanks,
SELECT @ToDate=(SELECT MAX(QuoteDate) FROM Table1 WHERE MarketID=1)
or
SET @ToDate=(SELECT MAX(QuoteDate) FROM Table1 WHERE MarketID=1)
Cheers, Andrew