Passing a value from a stored procedure

[Kedgeboy]

Hello

I'm still learning SQL and have a question about passing a value from a stored procedure to be displayed on the screen.

say I've got the stored procedure

create proc pr_TestProc(
	@a nchar (5),
	@b int output
) as
select @b = b from database_name
Where a = @a
Return

Can I use the following code to display the value of @b on the screen?

select @varb = 'Sales'
exec pr_TestProc @varb
print @b

If not how can I display the value of @b outside the stored procedure? (as i realise I could add the print line to the procedure)

[skhanal]

See this sample from Books online

USE pubs
GO
IF EXISTS(SELECT name FROM sysobjects
WHERE name = 'titles_sum' AND type = 'P')
DROP PROCEDURE titles_sum
GO
USE pubs
GO
CREATE PROCEDURE titles_sum @TITLE varchar(40) = '%', @SUM money OUTPUT
AS
SELECT 'Title Name' = title
FROM titles
WHERE title LIKE @TITLE
SELECT @SUM = SUM(price)
FROM titles
WHERE title LIKE @TITLE
GO

Next, use the OUTPUT parameter with control-of-flow language.



Note The OUTPUT variable must be defined during the table creation as well as during use of the variable.


The parameter name and variable name do not have to match; however, the data type and parameter positioning must match (unless @SUM = variable is used).

DECLARE @TOTALCOST money
EXECUTE titles_sum 'The%', @TOTALCOST OUTPUT
IF @TOTALCOST < 200
BEGIN
PRINT ' '
PRINT 'All of these titles can be purchased for less than $200.'
END
ELSE
SELECT 'The total cost of these titles is $'
+ RTRIM(CAST(@TOTALCOST AS varchar(20)))

[Kedgeboy]

so in my example if I used:

DECLARE @varb nchar(5), @b_out int
SELECT @varb = 'Sales'
EXEC pr_TestProc @varb, @b_out output
print @b_out

That would work?