I have 1 files, one is .sql and another is stored procedure. SQL file will call the stored procedure by passing the variables setup in the SQL file. However, everything ran well except at the end, I try to get the return value from SP to my SQL file ... which will send notification email. But, I get the following msg ... I am not sure how to fix this ... help!!! :(
Syntax error converting the varchar value 'ABC' to a column of data type int.
SQL file
======================
DECLARE @.S AS VARCHAR(1000)
EXEC @.S = PDT.DBO.SP_RPT
'ABC'
SELECT CONTENT = @.S -- this is the value I am trying to pass as content of the email
EXEC PRODUCTION.DBO.SENDEMAIL'xxx@.hotmail.com', 'Notification', @.S
======================
Stored Procedure
======================
CREATE procedure sp_RPT
( @.array varchar(1000) )
AS
DECLARE @.content AS VARCHAR(2000)
SET @.content = 'RPT: ' + @.array + ' loaded successfully '
SET @.array = 'ABC'
RETURN CONVERT(VARCHAR(1000),@.array)
GO
try
cast( @.array as varchar(1000))
Hope this help
|||Don't use RETURN values in that manner. Either use an output parameter, or a resultset. RETURN values should be used as a control channel, not as a data channel; RETURN's purpose is to terminate control of a stored procedure immediately and also to report on how control was terminated (0 = normal/good, anything else is abnormal/bad).
No comments:
Post a Comment