May 15, 2008 at 9:58 am
I wrote a stored procedure that manipulates data for any record where the value of a specific date field is older than 21 days; but I want to change the SP to make it so that the user who calls the SP can specify the age of data to be deleted by passing a numeric value to the SP when it is called. If no value is passed, I want the paramter to default to 21; but I can't seem to get the syntax right.
Can anybody help?
Thanks
d
May 15, 2008 at 10:19 am
deekadelic (5/15/2008)
I wrote a stored procedure that manipulates data for any record where the value of a specific date field is older than 21 days; but I want to change the SP to make it so that the user who calls the SP can specify the age of data to be deleted by passing a numeric value to the SP when it is called. If no value is passed, I want the paramter to default to 21; but I can't seem to get the syntax right.Can anybody help?
Thanks
d
Post what you started - let's see if we can help you adjust it.....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 15, 2008 at 10:48 am
Here's the SP with the input variable decalred, but not used. I'm not sure where to go from here; and the literature I have here is not doing the trick.
Thanks
d
-----------------
CREATE procedure sp_clear_tbl_launch_OLD
as
-- Declare Variable
Declare @days int
begin
insert into dbo.tbl_BF_deek
select *
from dbo.tbl_L_Deek L
where L.InsertDate < ( GetDate()-21)
delete from dbo.tbl_L_Deek
where InsertDate < ( GetDate()-21)
end
GO
-------------------
May 15, 2008 at 11:24 am
You've defined a variable, but not a parameter (variable is internal only to the SP, Parameters pass stuff IN or OUT).
You should also avoid the * in here - will cause you grief. Take the time and list your column names.
[font="Courier New"]
CREATE PROCEDURE sp_clear_tbl_launch_OLD (@Dayoffset INT = 21)
AS
BEGIN
-- Declare Variable
DECLARE @days INT
SET @days=@dayoffset;
INSERT INTO dbo.tbl_BF_deek
SELECT *
FROM dbo.tbl_L_Deek L
WHERE L.InsertDate < ( GETDATE()-@days)
DELETE FROM dbo.tbl_L_Deek
WHERE InsertDate < ( GETDATE()-@days)
END
GO[/font]
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 15, 2008 at 11:43 am
that looks great, thanks!
one last thing- what's the proper syntax for calling the SP with a value for @days?
May 15, 2008 at 12:16 pm
In the case above - you're calling with a value for @DayOffset (the parameter), not @days (the variable). Being a little nitpicky, but it's useful to see the difference. Like I mentioned previously - a parameter is to pass information in (or out) of a stored procedure or function. A variable is something used within the stored procedure or function and not "visible" from outside (so you can't refer to it unless you're inside the design of the SP).
As to how to call it:
The most common way:
Exec sp_clear_tbl_launch_OLD @Dayoffset =26
or
Execute sp_clear_tbl_launch_OLD @Dayoffset =26
Additionally - since we've assigned a "default value" to the @dayoffset, the parameter is now optional, so you you can simply do
Exec sp_clear_tbl_launch_OLD
For what it's worth - we also didn't deal with the possibility that someone might pass in a NULL value, so perhaps changing
SET @days=@dayoffset;
to
SET @days=isnull(@dayoffset,21);
would be best.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 15, 2008 at 12:17 pm
nevermind- i got it.
thanks for your help!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply