CODEWITHSUNDEEP
sql-serverstored-procedures
zanhtet
zanhtet

Reputation: 2050

Stored procedure

How to type of return value in stored procedure. And what are difference between them. Please explain me.

Upvotes: 0

Views: 242

Answers (1)

James
James

Reputation: 641

Typically with stored procedures you expect to get back a dataset. If your looking for a way to get single values back from some type of query, you might be better suited making a UDF (user defined function).

Nonetheless, here is how you can create a stored procedure with an output variable

CREATE PROCEDURE dbo.GetNameByID (
    @ID NVARCHAR(50),
    @PersonName NVARCHAR(50) OUTPUT )
AS
SELECT @PersonName = LastName
FROM Person.Contact
WHERE ID = @ID

with this procedure, you can then execute it as follows. 

DECLARE @Name NVARCHAR(50)

EXEC dbo.GetNameByID 
    @ID = 'A123FB',
    @PersonName = @Name OUTPUT

SELECT Name = @Name

Good Luck.

Upvotes: 4

Related Questions