2 Replies - 4760 Views - Last Post: 13 July 2012 - 09:18 AM

#1 deathxmortis  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 11
  • Joined: 02-April 12

Assign to variable a result of Stored procedure T-SQL

Posted 13 July 2012 - 08:15 AM

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


Is This A Good Question/Topic? 0
  • +

Replies To: Assign to variable a result of Stored procedure T-SQL

#2 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5746
  • View blog
  • Posts: 12,553
  • Joined: 16-October 07

Re: Assign to variable a result of Stored procedure T-SQL

Posted 13 July 2012 - 08:59 AM

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.
Was This Post Helpful? 2
  • +
  • -

#3 deathxmortis  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 11
  • Joined: 02-April 12

Re: Assign to variable a result of Stored procedure T-SQL

Posted 13 July 2012 - 09:18 AM

Thank you very much!, this is indeed a very complete answer, I think I'll be following you around...
Cheers :euro:
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1