When I use the query individually then it works. But it fails when I combine both the Stored Procedure statements.

spMarkAsApproved is a Store Procedure with Update statement.

This works

spMarkAsApproved '112', 'A';

This also works

spMarkAsApproved '113', 'A';

This Doesn't work

spMarkAsApproved '112', 'A';
spMarkAsApproved '113', 'A';

Error Mesage

Incorrect syntax near 'spMarkAsApproved'.

Any suggestion will be helpful.

CodePudding user response：

A stored procedure can be called and executed without the EXEC keyword if the procedure is the first statement in the Transact-SQL batch.

https://learn.microsoft.com/en-us/sql/relational-databases/stored-procedures/execute-a-stored-procedure?view=sql-server-ver16

exec spMarkAsApproved '112', 'A';
exec spMarkAsApproved '113', 'A';

CodePudding user response：

As per the documentation if you are calling a procedure and it is the first statement in the batch, then the EXEC keyword can be omitted:

Remarks

...
You do not have to specify the EXECUTE keyword when executing modules if the statement is the first one in a batch.

Examples: SQL Server

...
If the following is the first statement in a batch or an osql or sqlcmd script, EXEC is not required.

dbo.uspGetEmployeeManagers 6;  
GO  
--Or  
dbo.uspGetEmployeeManagers @EmployeeID = 6;  
GO  

If the statement is therefore not the first statement, then EXEC must be used.

Your second statement is not the first statement, so EXEC must be used:

spMarkAsApproved '112', 'A';
EXEC spMarkAsApproved '113', 'A';

Better yet, if I am honest, always use EXEC(UTE):

EXEC spMarkAsApproved '112', 'A';
EXEC spMarkAsApproved '113', 'A';