passing scalar function results

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)

passing scalar function results

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-03-28 : 08:01:53

Buck writes "I just want to pass back a simple server-side system variable to my client!

I'm using SQL 2000 with Visual Studio 6. In a VB6 application, it is important that I use the server's Current_Timestamp rather than the client's time for each record produced. The stored function is executed during record validation at "Add" or "Update".

It seems simple enough: execute a scalar timestamp function that returns a single parameter converted to a string. I didn't think I'd have to create a temp table but now I'm not so sure. Obviously, it isn't clear enough to me and I don't find any examples. Here's two examples that don't work:

1. The data view in project allows the programmer to create a new stored procedure and save it to the SQL server DB. This one would not save at all with an error message that "You can't change the object in the script." Possibly resulting from trying to save as a function rather than a proedure? However, edited from within the SQL server itself, it didn't seem to like the conversion.

CREATE FUNCTION TimeStamper
/* (Created from Access Wizard) Function converts
the system Timestamp to a string and returns it */
(
@vchTimeStamp varchar(50) OUTPUT
)
RETURNS varchar
AS
BEGIN
SELECT @vchTimeStamp = (convert(varchar, Current_Timestamp))
RETURN @vchTimeStamp
END

2. I tried the following as both a Create and an Alter with no resulting difference. Even though I specified OUTPUT, the procedure wants an input which I didn't set up to receive. All procedures must have inputs? Still, supplying a string value of "0" during the debug run results in an error message that the Current_Timestamp was correctly found but that it cannot be stored in an int column. I thought I was trying to return it it, not store it?

ALTER Procedure TimeStamp
(
@vchTimeStamp varchar(50) OUTPUT
)
/* Function converts the system Timestamp
to a string and returns it */
AS
SET nocount on
SELECT @vchTimeStamp = (convert(varchar, Current_Timestamp))
RETUTN @vchTimeStamp

For both cases, I set up a Connection object and a Command object that ultimately calls the procedure/function with:

Set objTime = objCommand.CreateParameter("vchTimeStamp", _
adVarChar, adParmOutput, 50)
objCommand.Parameters.Append objTime
objCommand.Execute
strTime = objCommand.Parameters("vchTimeStamp"))

Clearly, I'm a neophyte to SQL but this shouldn't be that difficult. Your insight would be greatly appreciated.

Thanks,
Buck Buchanan
512-342-7372"

joldham
Wiseass Yak Posting Master

300 Posts

Posted - 2002-03-28 : 08:08:38

I think you should be able to use the following:

SET @vchTimeStamp = getdate()

This will get the server's system date and time. If you wanted just the time, then you would need to parse this out, probably with the VB code.

Jeremy

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources