Retrieving a particular value in SQL Server

Question

I have this stored procedure that has a value:

BEGIN TRY
    BEGIN TRANSACTION;

    -- Insert statements for procedure here
    DECLARE @return_value int
    DECLARE @media_id uniqueidentifier

    'INSERT SQL STATEMENT HERE

    SELECT [media_id] FROM [media] WHERE 1 = 1 

    -- One row affected
    RETURN 1
END TRY 
BEGIN CATCH
    IF @@TRANCOUNT > 0
    BEGIN
        ROLLBACK TRANSACTION;
    END

    -- Rollback, no row affected
    RETURN 0
END CATCH;      

I want to call the [media_id] value from another stored procedure. How do I get that value?

Answer 1

Table Definition

CREATE TABLE MY_EMPLOYEE 
(EMPID INT, NAME VARCHAR(20), 
LANGUAGEID INT , ID UNIQUEIDENTIFIER DEFAULT NEWID())
GO 

Stored Procedure

ALTER PROCEDURE usp_ProcName
@Emp_ID INT = null,
@Name VARCHAR(20) = null,
@LanguageID int =  null,
@NewID UNIQUEIDENTIFIER OUTPUT
AS
BEGIN
 SET NOCOUNT ON;
    BEGIN TRY
        BEGIN TRANSACTION;

        -- Insert statements for procedure here

       INSERT INTO [Practice_DB].[dbo].[MY_EMPLOYEE](EMPID, NAME, LANGUAGEID)
       VALUES (@Emp_ID, @Name, @LanguageID);

       -- Populating the OUTPUT variable using the other variables that were passed
       -- for INSERT statement.

        SELECT @NewID = ID 
        FROM [Practice_DB].[dbo].[MY_EMPLOYEE]
        WHERE EMPID = @Emp_ID

        -- One row affected
        COMMIT TRANSACTION 
        RETURN 1
    END TRY 
BEGIN CATCH
        IF @@TRANCOUNT > 0
        BEGIN
            ROLLBACK TRANSACTION;
        END

        -- Rollback, no row affected
        RETURN 0
    END CATCH

END

GO

Calling Stored Procedure

DECLARE @value int, @ID VARCHAR(100)

EXECUTE @value =  usp_ProcName 
@Emp_ID = 50, 
@Name = 'John',
@LanguageID = 50,
@NewID = @ID OUTPUT  --<-- passing this variable with OUTPUT key word this will be 
                     -- populated inside the Procedure and then you can SELECT it or 
                     -- whatever you want to do with this value.  
SELECT @ID
SELECT @value