CODEWITHSUNDEEP
sqlprocedure
Muhammad Taqi
Muhammad Taqi

Reputation: 315

Return Varchar value from Stored Procedure

I have the following basic stored Procedure which return varchar value

create proc myproc
AS
return 'SSS'

Now when I call this stored procedure using the following query

declare @ret varchAR(max)
 exec sp_executesql N'exec @ret =procc', 
N'@ret varchar(MAX) OUTPUT',@ret = @ret OUTPUT select @ret  as result

I get error the following error

Conversion failed when converting the varchar value 'SSS' to data type int.

Kindly help.

Thanks

Upvotes: 0

Views: 13377

Answers (5)

R.Alonso
R.Alonso

Reputation: 1065

alter procedure GetPrueba(@a int, @b int, @Suma INT OUTPUT, @Resta int OUTPUT) as
begin
select  @Suma=@a+@b, @Resta=@a- @b
end

alter procedure getprueba2 as
begin
declare @s int, @r int
exec getprueba 2,5, @s OUTPUT, @r OUTPUT
select @s,@r
end


getprueba2

Upvotes: 0

Mathusuthanan
Mathusuthanan

Reputation: 117

If you want to return a value from stored procedure, you have to declare an output parameter for that.

Hope the below code will help you

create proc myproc
@outputvalue VARCHAR (16) output
AS
set @outputvalue='SSS'
select @outputvalue

Upvotes: 0

Madhivanan
Madhivanan

Reputation: 13700

Have output parameter

create proc myproc
(@value varchar(100) output)
AS
select @value='SSS'

GO

declare @ret varchAR(max)
 exec sp_executesql N'exec @ret =procc', 
N'@ret varchar(MAX) OUTPUT',@ret = @ret OUTPUT select @ret  as result

Upvotes: 0

Jodrell
Jodrell

Reputation: 35716

Stored procedures in MS SQL Sever can only return an Integer.

Did you want to use an output parameter instead?

CREATE PROC [myproc]
    @output VARCHAR(3) OUTPUT
AS
    SET @output = 'SSS';
RETURN 0;

Which you could call like this,

DECLARE @output VARCHAR(3);
EXEC [myproc] @output OUTPUT;
SELECT @output;

Or maybe you'd prefer to return a scalar result set?

CREATE PROC [myproc]
AS
    SELECT 'SSS';
RETURN 0;

which would return the same by simply executing,

EXEC [myproc];

Upvotes: 3

Kristen B
Kristen B

Reputation: 83

Since stored procedures in MS SQL Sever can only return an Integer, try selecting the value rather than returning it:

create proc myproc AS SELECT 'SSS'

Upvotes: 1

Related Questions