Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2004
    Posts
    3

    Unanswered: Stored Procedure into Variable?

    Hello experts!

    I have a problem. I am trying to get a value from stored procedure A into a variable in storded procedure B, kind of like this:

    Note: don't be fooled by the simplicity of these samples. There is no other way for me to solve this problem, but to get the value from sp_A into a variable in sp_B, since sp_A is in a database out of my control and i have no clue what happens therein...

    CREATE PROCEDURE sp_A
    AS

    SELECT 'Hello world!'
    RETURN

    GO

    CREATE PROCEDURE sp_B
    AS

    DECLARE @Value nvarchar(50)

    SET @Value = --** Here i want to get the value from sp_A
    SELECT @Value

    GO

    Thanks!

    /Murti

  2. #2
    Join Date
    Oct 2003
    Posts
    84
    EXEC @MyVariable = MyStoredProcedure



    ???is that what you meant?

  3. #3
    Join Date
    May 2004
    Posts
    125
    Try this:

    Code:
    Create Procecure sp_A
        @valuetoreturn output
    as
    --do your code here
    set @valuetoreturn = What_ever_Value
    --or you could do select @valuetoreturn = column1 from table1

    Then you can call Stored Proc A from Stored Proc B

    Code:
    Create Procedure sp_B
    as
    declare @receive_value
    exec sp_A @receive_value output
    --Place code here to do what you need with your new value.

    Let me know if you need anything explained.

    HTH

  4. #4
    Join Date
    Nov 2004
    Posts
    3
    Actually, neither works, but thank you for responding.

    afx2029>>
    When i do:
    EXEC @MyVariable = MyStoredProcedure

    I then want to do:
    SELECT @MyVariable

    But in this case, the @MyVariable only holds the value '0' as a code for the result of executing MyStoredProcedure ('0' = no error code returned)


    DMWCincy>>
    That might work in another context, but i have no control over sp_A. I am not allowed to alter that sp. Actually, all i know is that sp_A is a scalar stored procedure that only returns one value, kind of like:

    CREATE PROCEDURE sp_A
    AS

    SELECT 'Hello world!'

    GO

    Any ideas?

  5. #5
    Join Date
    Nov 2004
    Posts
    3
    I solved it...
    This is how i did:

    Code:
    CREATE PROCEDURE sp_B
    AS
    
    DECLARE @Value nvarchar(50) CREATE TABLE #Temp( [Value] nvarchar(50) ) INSERT INTO #Temp EXEC sp_A SELECT @Value = [Value] FROM #Temp DROP TABLE #Temp SELECT @Value
    GO
    Thank you anyways and have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •