CODEWITHSUNDEEP
sql-serverstored-proceduresdynamic-sql
Teeko
Teeko

Reputation: 53

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

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?

Upvotes: 0

Views: 57

Answers (2)

M. A. R. Bin Siddiqui Mamun
M. A. R. Bin Siddiqui Mamun

Reputation: 21

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'

Upvotes: 0

Emdad
Emdad

Reputation: 832

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.

Upvotes: 3

Related Questions