在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]的值。我该如何获取这个值？

- javisrk

---

https://dev59.com/uHjfs4cB2Jgan1znO3TI#11743655 - Andrew Savinykh

添加了额外的代码。 - javisrk

Answer 1

表定义

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

调用存储过程

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

- M.Ali

---

这里遇到了一个问题。通过 SQL 插入记录时，[media_id] 字段会自动生成。因此，EXECUTE 语句对我来说不起作用，因为我不需要该参数进行插入。有没有其他方法可以调用它？ - javisrk

@javisrkпјҢдҪ жІЎжңүеҗ‘@mediaidдј йҖ’д»»дҪ•еҖјпјҢдҪ еҸӘжҳҜе°ҶдёҖдёӘеёҰжңүе…ій”®еӯ—OUTPUTзҡ„еҸҳйҮҸдј йҖ’з»ҷеҸӮж•°@mediaidгҖӮдёҖж—ҰеӯҳеӮЁиҝҮзЁӢжү§иЎҢпјҢдҪ дј йҖ’зҡ„еҸҳйҮҸе°ұдјҡиў«еЎ«е……пјҢ然еҗҺдҪ еҸҜд»ҘйҖүжӢ©е®ғ并еҜ№иҝҷдёӘеҸҳйҮҸиҝӣиЎҢд»»дҪ•дҪ жғіеҒҡзҡ„ж“ҚдҪңгҖӮз”ұдәҺжҲ‘дёҚеӨӘжё…жҘҡдҪ зҡ„еӯҳеӮЁиҝҮзЁӢеҶ…йғЁеҸ‘з”ҹдәҶд»Җд№ҲпјҢжүҖд»ҘеҫҲйҡҫе‘ҠиҜүдҪ еҰӮдҪ•еҮҶзЎ®ең°еЎ«е……дҪ зҡ„иҫ“еҮәеҸҳйҮҸгҖӮ - M.Ali

@javisrk 我已经创建了一张表格，然后为它编写了一个存储过程，该存储过程几乎可以完成你的存储过程所需的所有功能。请仔细查看，希望这能消除你可能存在的任何误解。 - M.Ali

Answer 2

我认为这应该可以工作

BEGIN TRY
    BEGIN TRANSACTION;

    -- Insert statements for procedure here
    DECLARE @return_value int
    DECLARE @media_id uniqueidentifier
    --get the value from the stored procedure here 
    Declare @SQL varchar(max)
    set @SQL='INSERT SQL STATEMENT HERE
    SELECT @media_id FROM [media] WHERE 1 = 1'
    exec @SQL

    -- One row affected
    RETURN 1
END TRY 

- sam

Answer 3

这应该会有所帮助：从存储过程返回数据。

- Andrew Savinykh