Returning a Strin from SP ?

[N3XuS]

I have the following stored procedure.

ALTER PROCEDURE dbo.GetTree 
	@id int
AS
	DECLARE @parent int
	 DECLARE @iLink nvarchar(50)
	SELECT @parent = parent from tlkpnavigation WHERE id = @id
	IF @parent = 0
		BEGIN
			SET @iLink = '@id' 
		END
	ELSE
		BEGIN
			SET @iLink = '@parent'
		END

	WHILE (@parent != 0)
	BEGIN
		SELECT @parent = parent from tlkpnavigation WHERE id = @parent
		SET @iLink = '@parent' + '_' + '@iLink'
	END
	
	RETURN @iLink

And the following error
Syntax error converting the nvarchar value '@parent_@iLink' to a column of data type int.

It seems if you don't say what type you want to return it wants to convert it to an int How do I tell the stored procedure to return a string please ? Been searching for over an hour :s

many thanks

 

[SQLDenis]

return is always an int, use ouput parameters instead

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[druer]

Instead of the return @ilink just use:

select @iLink

and the value in @iLink will be returned as the result set. A stored proc can return anything you want as the result set you just have to SELECT it.

I'm not sure that you want to to always return the hardcoded string: '@parent' + '_' + '@iLink' that you will be returning now. Are you sure that you don't want to concatenate the values for @parent and @iLink? Right now you are concatenating the literal strings '@Parent' and '@iLink' and will end up selecting the string:

@Parent_@iLink

Except in the case where no parents were found the first time, then you'll just return the other string literals.