February 10, 2010 at 8:51 am
I'm using the following SP
CREATE PROCEDURE Category_GetCategoryPos
(
@idCategory INT,
@links BIT,
@BreadCrumb VARCHAR(2000) = 'No path' output
)
AS
DECLARE @CategoryDesc NVARCHAR(250)
DECLARE @idParentCategory INT
SELECT @idCategory = idCategory,@idParentCategory = idParentCategory,@CategoryDesc = CategoryDesc
FROM [Cpnet_Category]
WHERE idCategory = @idCategory;
WHILE( @idParentCategory <> 0)
BEGIN
IF @links = 0
BEGIN
SET @BreadCrumb = @CategoryDesc + ' > ' + @BreadCrumb
IF @idParentCategory <> 0
BEGIN
SELECT @idCategory = @idParentCategory
END
ELSE
BEGIN
SET @BreadCrumb = Left(@BreadCrumb,Len(@BreadCrumb) - 1)
END
END
ELSE
BEGIN
SET @BreadCrumb = '<a href="prodList.aspx?idCategory=' + CONVERT(NVARCHAR(6),@idCategory) + '" class="breadcrumb" >' + @CategoryDesc + '</a> > ' + @BreadCrumb
IF @idParentCategory <> 0
BEGIN
SELECT @idCategory = @idParentCategory
END
ELSE
BEGIN
SET @BreadCrumb = Left(@BreadCrumb,Len(@BreadCrumb) - 1)
END
END
SELECT @idCategory = idCategory,@idParentCategory = idParentCategory,@CategoryDesc = CategoryDesc
FROM [Cpnet_Category]
WHERE idCategory = @idCategory;
END
print @BreadCrumb
But the @BreadCrumb does not return to the program and is always empty. However if I use the procedure from within SSMS the print output is correct.
February 10, 2010 at 9:00 am
EDIT: Also, change the first two parameters as needed to match your data.
Try the following from SSMS.
declare @ BCrumb varchar(2000); -- Had to add a space between the @ and variable name, remove this space before running
exec dbo.Category_GetCategoryPos @idCategory = 1, @links = 0, @BreadCrumb = @BCrumb OUTPUT;
select @BCrumb as OutputBreadCrumb;
February 10, 2010 at 9:21 am
This issue is not with SSMS but with the SP returning the output parameter to a C# program. SSMS displays the Print @BreadCrumb with the breadcrumb but when querying the output parameter @BreadCrumb in the program it is always empty. But if I set @BreadCrumb to a string, e.g., SET @BreadCrumb = 'This is the crumb' the program get the literal.
The program
CP_Support.GenericDataAccess.ExecuteNonQuery (comm );
String temp = comm.Parameters [ "@BreadCrumb" ].Value.ToString();
return temp;
February 10, 2010 at 9:25 am
Look at how I have setup the exec of the stored procedure. You need to do something very similiar to return the value of the output parameter to your application. Sorry, I am a DBA?DBD, not an application developer so I am not in a position to help you on the app side.
February 10, 2010 at 9:42 am
I'm using the following EXEC
DECLARE@return_value int
DECLARE @BCrumb VARCHAR(2000)
EXEC@return_value = [dbo].[Category_GetCategoryPos]
@idCategory = 260,
@BreadCrumb = @BCrumb OUTPUT ,
@links = 1;
SELECT @BCrumb as crumb
SELECT'Return Value' = @return_value
GO
crumb contains NULL
If I change the SP so that the last line is SET @BreadCrumb = 'This is a test' then crumb contains "This is a test"
Why?
February 10, 2010 at 9:45 am
Note the PRINT statement prints the correct breadcrumb string.
February 10, 2010 at 10:00 am
Try declaring a local variable inside your sproc to use in building the BreadCrumb, then at the end assign that local variable to the output parameter.
Just shooting in the dark at the moment.
February 10, 2010 at 10:10 am
Did a little reformatting of your code, if you want give it a try.
February 10, 2010 at 10:12 am
I tried that eariler without success.
I did discover that if I remove the concatenation then it will return the last crumb
SET @BreadCrumb = '<a href="prodList.aspx?idCategory=' + CONVERT(NVARCHAR(6),@idCategory) + '" class="breadcrumb" >' + @CategoryDesc + '</a> > ' + @BreadCrumb
To
SET @BreadCrumb = '<a href="prodList.aspx?idCategory=' + CONVERT(NVARCHAR(6),@idCategory) + '" class="breadcrumb" >' + @CategoryDesc + '</a> > '
I have no idea why the latter works
February 10, 2010 at 10:16 am
Sounds like concatenation with a null value. Check out the code I posted and see if it works.
February 10, 2010 at 4:25 pm
I just got back to this and you hit it on the head @BreadCrumb needed to be initialized.
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply