procedure parameter - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

procedure parameter

Author

Topic

SQLboom
Yak Posting Veteran

63 Posts

Posted - 2004-03-15 : 04:05:21

can the parameter of a procedure somehow be passed as, say, getdate().. . i tried this but it failed.. .and the BOL says

"The default must be a constant or it can be NULL. It can include wildcard characters (%, _, [], and

) if the procedure uses the parameter with the LIKE keyword."

but still can it be...
Thanks.

JoeIngle
Starting Member

14 Posts

Posted - 2004-03-15 : 06:43:41

Yes you can, this will do it:

Dim dtmDate

dtmDate = Now()

Set objCmd = Server.CreateObject("ADODB.Command")

With objCmd

.ActiveConnection = YOURCONNECTION
.CommandText = "Pr_Test"
.CommandType = adCmdStoredProc

.Parameters.Append .CreateParameter("@dtmDate", adDBTimeStamp, adParamInput, 8, Trim(dtmDate))

.Execute

End With

Set objCmd = Nothing

----------------------------------------------------------------

CREATE PROCEDURE Pr_Test
(
@dtmDate datetime
)
AS

INSERT INTO TABLE
(
dtmDate
)
VALUES
(
@dtmDate
)
GO

----------------------------------------------------------------

NB You'll need to include Adovbs.inc with the snip above.

Are you planning on storing the value? If so you'd be better off using a field with a default value of (GETDATE()) then you don't need to worry about passing the value around.

HTH

Joe

SQLboom
Yak Posting Veteran

63 Posts

Posted - 2004-03-16 : 00:34:18

well, the idea didn't carry. . .. i want to ask if we can keep a default parameter to the procedure definition as, say, getdate(), so that the user doesn't have to supply at least the ongoing date data.. . . .. much like below:
create procedure (a int, b datetime = getdate())
as
begin
...
...
end
thanks. ..

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-03-16 : 00:40:59

Why not have the gatdate value stored in a default variable rather than a parameter?
like this...

create procedure myproc(@a int)
as
DECLARE @b DATETIME
SET @b = GETDATE()

....blah...blah...blah....

go

exec myproc

Duane.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources