How to check value of stored procedure output parameter
- by Anna T
I have a stored procedure that:
A. inserts some rows into a "table variable" based on some joins
B. selects all values from column x from that table into a string with comma separated values
C. selects all from the "table variable"
If I execute the procedure like this:
EXEC CatalogGetFilmDetails2 2,111111;
a table is returned as instructed per step C above.
How can I execute it so that also the output parameter value is displayed? (see point B above). I need to check if it's calculated properly.
And since the second parameter is of output type, meaning it's calculated inside the procedure, why is it mandatory to specify a value for it when executing the procedure? I normally use a random value for it, it anyway doesn't matter/impact the result. On the other hand if I try to execute it without the output parameter, it returns an error)
Thank you very much!
This is how the procedure starts:
CREATE PROCEDURE CatalogGetFilmDetails2
(@FilmID int,
@CommaSepString VARCHAR(50) OUTPUT)
AS
And this is how @CommaSepString is calculated:
SELECT @CommaSepString
= STUFF((SELECT ', ' + Categ FROM @Filme1 FOR XML PATH('')), 1,1,'')