How to get the return value from EXEC(Query) inside stored procedure

Question

Is there any way to get the return value from the following SQL script?

ALTER PROCEDURE spInvert 
    (@Id INT, @column CHAR(1))
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @ColumnValue CHAR(1)
    DECLARE @Query VARCHAR(1000)

    SET @Query = 'select Top 1 ' + @column + ' from TEST5 where Id = ''' + @Id + ''

    SET @ColumnValue = EXEC(@Query) // here I need to get the column value
END

The @ColumnValue will be 1 or 0. I am expecting single value from @ColumnValue. I want to check if the @ColumnValue is NULL or not. If Not Null, I want to update a table with this @ColumnValue.

Thanks in advance

are you expecting to get only one value returned? If so you can use OUTPUT paramater, or are you expecting to get multiple rows/columns? — Brad, Mar 30 '18 at 16:07
You can select results from SP call to temp table: insert into #temptable exec mysp. Which one are you expecting and I can write up some code — Brad, Mar 30 '18 at 16:10
what table do you want to update with this value? The same table, or a different one? Do you want this handled in the procedure or outside of it? — S3S, Mar 30 '18 at 16:42

score 1 · Answer 1 · answered Mar 30 '18 at 16:18

Given your example code I wouldn't use dynamic sql at all. Just change up the query a little bit and use EXISTS.

ALTER PROCEDURE spInvert (@Id INT, @column char(1))
AS
BEGIN

SET NOCOUNT ON;

select @column where exists(select * from TEST5 where Id = @Id)

END

Emdad · Accepted Answer · 2018-03-30T19:13:41.463

1

If you update table inside SP, you can try the following query.It is better not to use sp in procedure name.

ALTER PROCEDURE USP_Invert 
    (@Id INT, @column VARCHAR(100))
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @Query VARCHAR(1000)

    SET @Query ='UPDATE TEST5 SET ' + @column +' = CASE WHEN (SELECT Top 1 ' + @column + ' FROM TEST5 where Id = ' + Convert(VARCHAR(10),@Id) + ') = 0 THEN 1
                                                    WHEN (SELECT Top 1 ' + @column + ' FROM TEST5 where Id = ' + Convert(VARCHAR(10),@Id) + ') = 1 THEN 0 END '

    EXEC(@Query); -- here I need to get the column value
END

Thanks

edited Mar 30 '18 at 19:13

answered Mar 30 '18 at 17:46

Emdad

822
7
14

@Shaju Madheena, please give your feed back in comment section. Thanks. – Emdad Mar 30 '18 at 18:58
@Shaju Madheena, I have updated my query, please check. Thanks. – Emdad Mar 30 '18 at 19:05

score 0 · Answer 3 · answered Mar 30 '18 at 16:11

0

Since you have TOP 1 in your procedure, only one value will be returned. So simply place SELECT @ColumnValue at the end of the procedure. Though, this is really unecessary and you could just remove this:

SET @ColumnValue = EXEC(@Query) // here i need to get the column value

And replace it with:

EXEC(@Query)

answered Mar 30 '18 at 16:11

S3S

24,809
5
26
45

This answer is not applicable. I want to update the same table in the SP. Any idea to help me ? – Shaju Madheena Mar 30 '18 at 16:57
You didn’t state that in the original question – S3S Mar 30 '18 at 16:58

score 0 · Answer 4 · answered Oct 25 '21 at 15:05

0

It works as below:

DROP TABLE If Exists #temp CREATE TABLE #temp ( column1 real

) insert into #temp exec(@sqlcmd) select * from #temp

answered Oct 25 '21 at 15:05

user17242971

1

1

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Oct 25 '21 at 16:19

How to get the return value from EXEC(Query) inside stored procedure

4 Answers4