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

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

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] 值。我如何获得该值？

最佳答案

表定义

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

关于sql-server - 在 SQL Server 中检索特定值，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/20622464/