July 19, 2006 at 11:39 am
Simple question, but it's really got me right now.
I have a variable @draft that i want to set equal to the results of a stored proc, similar to below
create proc test
(
@x char(1)
)
if @x = 'y'
begin
select 'yes'
end
if @x = 'n'
begin
select 'no'
end
go
declare @in char(1)
declare @out varchar(10)
set @out = (exec test @in) --can't get this line to work
Hope that makes it clear.....
Thanks in advanced guys!
July 19, 2006 at 11:54 am
BOL
Execute a stored procedure:
[ [ EXEC [ UTE ] ]
{
[ @return_status = ]
{ procedure_name [ ;number ] | @procedure_name_var
}
[ [ @parameter = ] { value | @variable [ OUTPUT ] | [ DEFAULT ] ]
[ ,...n ]
[ WITH RECOMPILE ]
Execute a character string:
EXEC [ UTE ] ( { @string_variable | [ N ] 'tsql_string' } [ + ...n ] )
so correct syntax would be:
exec @out = test @in BUT
@return_status
Is an optional integer variable that stores the return status of a stored procedure. This variable must be declared in the batch, stored procedure, or function before it is used in an EXECUTE statement.
When used to invoke a scalar-valued user-defined function, the @return_status variable can be of any scalar data type.
you should write a function
CREATE FUNCTION [dbo].[ReturnTest]
(@X char(1))
RETURNS varchar(10)
AS
BEGIN
RETURN CASE @X
WHEN 'n' THEN 'No'
WHEN 'y' THEN 'Yes'
ELSE 'Wrong Value'
END
END
SET @Out=dbo.ReturnTest(@in)
or use an output parameter from the stored procedure see syntax in BOL
Vasc
July 20, 2006 at 6:15 am
To return values from a stored procedure, use OUTPUT parameters:
drop proc test
go
create proc test
(
@in varchar(1)
, @out varchar(3) OUTPUT
)
as
if @in = 'y'
begin
SET @out = 'yes'
end
if @in = 'n'
begin
SET @out = 'no'
end
go
declare @in char(1)
declare @out varchar(10)
SET @in = 'y'
exec test @in, @out OUTPUT
PRINT @out
SET @in = 'n'
exec test @in, @out OUTPUT
PRINT @out
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply