Assign result of dynamic SQL in Procedure A to variable defined in Procedure B

Question

I am struggling with getting the result back for a dynamic query that is being called from a different stored procedures. Here is what I am trying to achieve.

Procedure A:

CREATE PROCEDURE A
    @C1 int, @F1 int
AS
    SET @SQL = 'SELECT ID FROM EMPLOYEE_TABLE WHERE '+@C1+' = +'@F1'
    EXEC(@SQL)

Procedure B:

CREATE PROCEDURE B
    @C1 int, @F1 int
AS
    DECLARE @Result INT
    EXEC @Result = A  @C1, @F1

I need to run stored procedure B and let it return back to me the result. I just cannot seem to get the correct result back. How can I fix this problem?

Emdad · Accepted Answer · 2018-03-16 04:52:18Z

3

You can try the following two store procedures query statement

Procedure A:

ALTER PROCEDURE A
@C1 VARCHAR(250),
@F1 int
AS
DECLARE @SQL AS VARCHAR(MAX);
SET @SQL = 'SELECT ID FROM PatientTest WHERE '+ @C1+' = ' + CONVERT(VARCHAR(12),@F1)
EXEC(@SQL)

Procedure B:

ALTER PROCEDURE B
@C1 VARCHAR(250),
@F1 int
AS
Declare @Result int
EXEC @Result = A  @C1, @F1

If you will face further problem, please let me know in comment. Thanks.

edited Mar 16, 2018 at 4:52

answered Mar 16, 2018 at 2:38

Emdad

8227 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Teeko Over a year ago

Hello @Emdad, thanks for your response. Unfortunately this did not work. The issue seems to be that the '@SQL' is being run in a separate batch and therefore the result is not being captured from within Procedure A. Also, I see you decalred an int variable '@G1' but did not use it.

Emdad Over a year ago

I just added @G1 and print function in test purpose. I have modified the query. Can you please let me know your current result which procedure B returns.

M. A. R. Bin Siddiqui Mamun · Accepted Answer · 2018-03-29 18:03:03Z

0

Try these two. I think you will meet your expected result.

Procedure 1

CREATE PROCEDURE GetValue
@ColumnName VARCHAR(250),
@ColumnValue VARCHAR(250)
AS
DECLARE @SQL AS VARCHAR(MAX);
SET @SQL = 'SELECT Email FROM Person WHERE '+ @ColumnName + ' = ''' + @ColumnValue + ''''
EXEC (@SQL)
-- EXEC GetValue 'MobileNo', '+8801919111333'

Procedure 2

CREATE PROCEDURE ReturnValue
@ColumnName VARCHAR(250),
@ColumnValue VARCHAR(250)
AS
DECLARE @Result VARCHAR(250)
EXEC @Result = GetValue @ColumnName, @ColumnValue
-- EXEC ReturnValue 'MobileNo', '+8801919111333'

answered Mar 29, 2018 at 18:03

M. A. R. Bin Siddiqui Mamun

212 bronze badges

Collectives™ on Stack Overflow

Assign result of dynamic SQL in Procedure A to variable defined in Procedure B

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related