0

I've been searching for hours on how to execute the statements I have stored in a table that has the results of a SELECT statement.

I have found similar questions but none of them works.

So now I have a table where the first column's cells contain 

DROP TABLE table1
DROP TABLE table2
..... and so on.

I've tried using the EXEC command but I can't make it work.

Please help. Thank you.

ahmed abdelqader
  • 3,409
  • 17
  • 36
couchPotato
  • 9
  • 7
  • 1
    Why would you want to do that? Seems like a bad idea. – juergen d Feb 15 '17 at 10:06
  • I read that it's bad security-wise, but it's not something that will be reused. I only need to drop the tables from a database that have a certain characteristic. – couchPotato Feb 15 '17 at 10:15

2 Answers2

1

I think you need to implement a cursor if you want to execute all statements

DECLARE @Query VARCHAR(250)

DECLARE crs_ExecStatement CURSOR FAST_FORWARD
FOR 
    SELECT Column1 FROM YourTable 

OPEN crs_ExecStatement
    FETCH NEXT FROM crs_ExecStatement INTO @Query
    WHILE @@FETCH_STATUS = 0
    BEGIN 
        EXEC(@Query)

        FETCH NEXT FROM crs_ExecStatement INTO @Query
    END

CLOSE crs_ExecStatement
DEALLOCATE crs_ExecStatement
Kostis
  • 953
  • 9
  • 21
  • Cursor is killing performance, plz follow my answer, to get another solution that more friendly than cursor – ahmed abdelqader Feb 15 '17 at 10:15
  • How many records are there in the table and how often will you execute these commands? Declaring the cursor as FAST_FORWARD you get a slightly better performance – Kostis Feb 15 '17 at 10:16
  • if you have any doubt about the relation between cursor and performance, Simply, Use execution plan for the two approaches, and let SQL Server Engine says its word. – ahmed abdelqader Feb 15 '17 at 10:20
  • I don't have any doubts about that, i just said that if it doesn't need to be executed very often and for a very large amount of records then the cost in performance is not very important. – Kostis Feb 15 '17 at 10:23
  • The SQL Server Engine Says: you are wrong, I used Execution Plan and The cost is `31%` and without cursor is `10%` – ahmed abdelqader Feb 15 '17 at 10:34
  • and this error is raising `'FAST_FROWARD' is not a recognized CURSOR option.` – ahmed abdelqader Feb 15 '17 at 10:35
  • It is FAST_FORWARD..sorry for the misspelling – Kostis Feb 15 '17 at 10:38
  • I just tried and it's working for me...do you have permissions to drop the tables? – Kostis Feb 15 '17 at 10:59
  • I didn't. Got the permission and now it's working. Thank you so much!! – couchPotato Feb 15 '17 at 12:12
0

use EXEC and separate the values with GO as next demo:-

Create database Demo
go
use Demo
go

Create table MyTable (Value varchar (200))
go
insert into MyTable values ('Drop Table table1')
go
insert into MyTable values ('Drop Table table2')
go
insert into MyTable values ('Drop Table table3')
go

declare @Query nvarchar(max)
SELECT @Query = isnull(@Query,'') + Value + char (10) + 'Go'
FROM MyTable 
--print @Query
SET @Query = 'EXEC (''' + REPLACE(REPLACE(@Query, '''', ''''''), 'GO', '''); EXEC(''') + ''');'
EXEC (@Query)

The idea of executing dynamic Query with go is taken from here.

Community
  • 1
  • 1
ahmed abdelqader
  • 3,409
  • 17
  • 36