[deathxmortis]

I Hope it's a simple question



I have a little cursor function that provides a store procedure with an ID. The stored procedure only returns 1 varchar field.


What i want to do is something like:

FOR select top(500)
	e.Thing_ID
from BPST_UI.dbo.Thingse
where e.Things_status_code = 'a'

DECLARE @Employee_ID INT
DECLARE @Text varchar(120)
.
.
. Some code
.
FETCH NEXT FROM Get_Local INTO @Thing_ID
WHILE (@@fetch_status <> -1)
BEGIN
	IF (@@fetch_status <> -2)
	BEGIN
		SET @Text = exec [BPST_UI].dbo.sp_Get_Logical_Parent @Thing_ID /*This stores returns text*/
		Select @Text,('Something else')as 'Other thing'
	END
	FETCH NEXT FROM Get_Local INTO @Employee_ID
END

... Can you assign the result to the variable @Text?

SET @Text = exec [BPST_UI].dbo.sp_Get_Logical_Parent @Thing_ID

[baavgai]

While procedures do return in value in T-SQL, it's only numeric and intended for exit status. You're looking for a function.

e.g.

create function Test1 (@id int) 
	returns varchar(200)
as begin
	declare @result varchar(200)
	set @result = 'Test1, id=' + cast(@id as varchar) + ', dt=' + cast(GetDate() as varchar)
	return @result
end
go

declare @foo varchar(200)
exec @foo = Test1 42
print @foo
go

Alternately, you can write a procedure that passes back values:

create procedure Test2 (@id int, @result varchar(200) OUTPUT)
as begin
	set @result = 'Test2, id=' + cast(@id as varchar) + ', dt=' + cast(GetDate() as varchar)
end
go

declare @foo varchar(200)
exec Test2 42, @foo OUTPUT
print @foo

As an aside, cursors are a solution of last resort in databases. If at all possible, try to craft a SQL select. A cursor is THE slowest choice.

[deathxmortis]

Thank you very much!, this is indeed a very complete answer, I think I'll be following you around...
Cheers