June 30, 2008 at 9:38 pm
Hi,
I have a SP to return a datetime value. However, when I EXECUTE the SP I always get this error
Msg 257, Level 16, State 3, Procedure sp_date_after_clarify_close, Line 57
Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query.
Here is the complete code.
Thanks!
----------------------------------------------------------------
Main Program
DECLARE @date_after_clarify_close datetime
EXEC @date_after_clarify_close=dbo.sp_date_after_clarify_close
@date_clarify_close,
@int_issue_id,
''
-----------------------------------------------------------------
STORED PROCEDURE
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[sp_date_after_clarify_close]
@date_clarify_close datetime,
@int_issue_id int,
@date_after_clarify_close datetime OUTPUT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @date_temp datetime
DECLARE cr_date_after_clarify_close CURSOR
FOR
SELECT date_response
FROM responses
WHERE issue_id = @int_issue_id
ORDER BY date_response
OPEN cr_date_after_clarify_close
FETCH NEXT FROM cr_date_after_clarify_close into @date_temp
WHILE @@FETCH_STATUS = 0
BEGIN
IF @date_temp = @date_clarify_close
BEGIN
FETCH NEXT FROM cr_date_after_clarify_close into @date_temp
SET @date_after_clarify_close = @date_temp
END
ELSE
FETCH NEXT FROM cr_date_after_clarify_close into @date_temp
END
CLOSE cr_date_after_clarify_close
DEALLOCATE cr_date_after_clarify_close
RETURN (@date_after_clarify_close) --> If I remark this RETURN then it's working fine.
END
--------------------------------------------------------------------
July 1, 2008 at 1:22 am
Check RETURN in bol !
Return is not used to fillup output variables, but it gives an integer value to the calling object. (mostly used to indicate OK or not OK)
To fill up the output variable, just use a set or select statement !
in your case, just use RETURN without anything else at that place
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply