How to call stored procedure from update statement
-
Hello, I have a stored procedure called P_DATE_TO_MS that takes 2 parameters DATE & MILLISECOND and I need to call this procedure from an update statement: conConnection.Execute ("UPDATE table SET CURRENT_TIME_IN_MS = P_DATE_TO_MS(getDate()) WHERE id=123") It looks like the stored procedure returns the information in the second variable (ByRef?) and not as a return value. I'm calling the statement through ADODB.Connection object and not in the SQL directly. Please correct me if my assumption is incorrect, I'm not an SQL guy and I'm just getting into SQL Can anyone tell me if this is possible or how to do it. here is the store procedure: CREATE PROCEDURE P_DATE_TO_MS @DATE DATETIME, @MILLISECOND DECIMAL OUTPUT SET @MILLISECOND = TIME_IN_MS END GO Thank you in advance ilya
-
Hello, I have a stored procedure called P_DATE_TO_MS that takes 2 parameters DATE & MILLISECOND and I need to call this procedure from an update statement: conConnection.Execute ("UPDATE table SET CURRENT_TIME_IN_MS = P_DATE_TO_MS(getDate()) WHERE id=123") It looks like the stored procedure returns the information in the second variable (ByRef?) and not as a return value. I'm calling the statement through ADODB.Connection object and not in the SQL directly. Please correct me if my assumption is incorrect, I'm not an SQL guy and I'm just getting into SQL Can anyone tell me if this is possible or how to do it. here is the store procedure: CREATE PROCEDURE P_DATE_TO_MS @DATE DATETIME, @MILLISECOND DECIMAL OUTPUT SET @MILLISECOND = TIME_IN_MS END GO Thank you in advance ilya
Hi, I think a user defined function would work better for what you are trying to do (a
SCALAR FUNCTIONin this case)CREATE FUNCTION dbo.udfGetSeconds (@Days INT) RETURNS INT AS BEGIN DECLARE @Seconds INT SET @Seconds = @Days * 24 * 60 * 60 RETURN @Seconds ENDThen use it as followsSELECT dbo.udfGetSeconds(5)(edit) or in an UPDATE statementUPDATE [Table] SET [column] = dbo.udfGetSeconds(5)(edit)
The man who smiles when things go wrong has thought of someone he can blame it on. If you tell a man there are 300 billion stars in the universe, he'll believe you. But if you tell him a bench has just been painted, he'll have to touch it to be sure.
-- modified at 8:03 Thursday 6th October, 2005