sql-server - 在 SQL Server 中检索特定值

Question

我有一个有值的存储过程:

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;      

我想从另一个存储过程中调用 [media_id] 值。我如何获得该值？

Answer 1

表定义

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

存储过程

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

调用存储过程

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