October 10, 2008 at 8:08 am
I need to get a variable return from stored procedure and I'm doing this
BEGIN
declare @bank varchar(50)
SET NOCOUNT ON;
select @Bank=(Select nome_banco from dbo.bancos where Codigo_Banco=1)
return @Bank
END
I'm getting this error:
Msg 245, Level 16, State 1, Procedure Unibanco_test, Line 12
Conversion failed when converting the varchar value 'Brasil' to data type int.
why it is trying to convert to INT ?? the column type is varchar(50)
any ideas?
thanks
October 10, 2008 at 8:21 am
The return value of a stored procedure is INT. You cannot return a string or other data types. The correct way to do this is to use an OUTPUT parameter.
.
October 10, 2008 at 8:21 am
The return value of a stored procedure can only be an integer. Stored procedures are not really designed to return data like this. The return value is there just to indicate an error (anything other than zero being an error).
To return data, you should use an output parameter:
[font="Courier New"]CREATE PROC MyProc
@OutputData VARCHAR(20) OUTPUT[/font]
The other option is to use a function.
October 10, 2008 at 8:26 am
oh ok, well in that case
I will type at the end of the script
return @OutputData
or just
return ??
thanks
October 10, 2008 at 8:29 am
You can just do a 'RETURN' at the end of the stored procedure. Assign correct value to the OUTPUT parameter and it will be passed to the calling procedure.
.
October 10, 2008 at 8:30 am
It will look something like this:
[font="Courier New"]CREATE PROC MyProc
@InputVal VARCHAR(20)
, @OutputData VARCHAR(20) OUTPUT
AS
/* do something */
SET @OutputData = 'MyOutputVal'
RETURN(0) --Good practice to return 0 on success[/font]
Look in book online for CREATE PROCEDURE and it will give you the specifics.
October 10, 2008 at 8:33 am
Thank you !!!
October 10, 2008 at 9:26 am
Just make sure to remember to specify the parameter as an output parameter when you call it too. T/SQL is a little anoying that way.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply