0

I have an PHP script that execute a store procedure. So far so good. Now I have a store procedure that execute two queries within the store procedure:

EXEC ('Delete From [DB].dbo.[Table]')
EXEC ('INSERT INTO [DB].dbo.[Table] ([Col])VALUES(1)')

If I call the SP with php, it only delete the content of the Table and doesn't Insert the first row. (It seemed to be that only the first Exec will be executed) I call the SP in PHP as followed:

$stmt = sqlsrv_query($connection, "EXEC my_sp" );
...
while( $row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC) ) {
...
}

The return isn't that important for me at the moment. I'm only wondering why only the first query is executed. If call the SP directly at the database the query will be executed like expected. There is no error-message or something like that. Does anybody know how I can execute both "execs" an a SP with only one php call?

Thank's for help! V

Der_V
  • 177
  • 1
  • 16

1 Answers1

0

I think you dont need to write EXEC ('Delete From [DB].dbo.[Table]') in SP. Ingnore EXEC(). Just write like Delete From [DB].dbo.[Table].

Mahipat Kanzariya
  • 148
  • 7
  • Hey, thank's for the answer.Yes in this case it could be possible.I use the exec to define tables and columns as parameter.So there are cases where i need two EXECs. The EXEC has got a limit of query-string-length, so i need more than one. Is there a possibility to call two EXECs in a store procedure with one PHP-call? – Der_V Jul 25 '14 at 17:28
  • I think you dont need EXEC even if you want to pass table name as variable. Directly write query. – Mahipat Kanzariya Jul 28 '14 at 05:07
  • I don't how this could work... i have a store procedure for example like this: CREATE PROCEDURE [bla] @table_name nvarchar(50) AS DELETE * FROM [@table_name] This would only run, if i put this delete-query in a text, concatinate the variables and EXEC the query... and this string i can execute with EXEC is limitted..That's why i use more than one EXEC, but with the described problem. – Der_V Jul 30 '14 at 05:35