Running execute statement within SQL statement

Question

I'm using SQL Server 2012 and I have a statement that runs a stored procedure:

SELECT TOP(10) 
    'exec dbo.PurgeResults '''+ Description + ''',''' + CONVERT(VARCHAR, DATEADD(dd,-30,GetDate()), 101)  + '''' 
FROM
    Alerts

The result I get is like this but there's a lot.

exec dbo.PurgeResults '127 - PM_Sanitation','09/03/2018'

How do I turn that statement I have to run the exec inside the result? I'm not sure if I am phasing it correctly.

Right now you are only displaying the exec command as a string which is an output from select query — Kamil Gosciminski
– Kamil Gosciminski, Commented Oct 3, 2018 at 17:03
It's displaying a string but how can i output and run it. I'm trying to eliminate data. — James
– James, Commented Oct 3, 2018 at 17:19

digital.aaron · Accepted Answer · 2018-10-03 17:07:12Z

2

You can put your results into a temp table, and then loop through them and execute them as you would dynamic sql. You can modify your existing query like this:

DECLARE @QueryStatement NVARCHAR(MAX)

SELECT TOP(10) 
Query = 'exec dbo.PurgeResults '''+ Description + ''',''' + CONVERT(VARCHAR, DATEADD(dd,-30,GetDate()), 101)  + '''' 
INTO #QueryTable
FROM Alerts

WHILE EXISTS (SELECT 1 FROM #QueryTable)
BEGIN
    SELECT TOP 1 
        @QueryStatement = Query 
    FROM #QueryTable

    EXEC(@QueryStatement)

    DELETE #QueryTable
    WHERE Query = @QueryStatement
END

answered Oct 3, 2018 at 17:07

digital.aaron

5,7152 gold badges27 silver badges46 bronze badges

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

4 Comments

digital.aaron Over a year ago

Yes. You can verify it yourself. Create a simple stored proc, then try to call it with DECLARE @QueryStatement NVARCHAR(MAX) = 'EXEC procName'; EXEC(@QueryStatement)

Kamil Gosciminski Over a year ago

No way to test it right now, appreciate the answer though :-)

James Over a year ago

Testing this right now, i'll let you know in a few.

James Over a year ago

It works, I had to add a drop table command to be able to rerun it.

Collectives™ on Stack Overflow

Running execute statement within SQL statement

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related