Databases
StackOverflow2013
Postsdbo
POStop Running a Procedure in Batch File
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POStop Running a Procedure in Batch File
    primarykey
    data
    text
    <p>I am creating a deployment package which will run a stored procedure that's in VSS. I am able to do this but my problem is this. I have more or less 30 databases which I need to deploy to and I only have two databases which doesn't need to get the update. With that, I included the following codes to the .sql file which the batch file runs:</p> <pre><code>IF OBJECT_ID('CONFIG') IS NULL OR DB_NAME() LIKE '%SampleDB%' BEGIN PRINT 'This is not a store database. Skipping this database.' SET NOEXEC ON RETURN END IF EXISTS (SELECT * FROM dbo.sysobjects WHERE ID = OBJECT_ID(N'[dbo].[sp_ThisIsMySampleProcedure]')) DROP PROCEDURE [dbo].[sp_ThisIsMySampleProcedure] GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE PROCEDURE [dbo].[sp_ThisIsMySampleProcedure] -- Everything else follows... </code></pre> <p>This sql code runs perfectly on SQL Server. Whenever I run it against Sample DB, it automatically skips proceeding to the checking if the procedure exists and everything after that. </p> <p>But whenever I try to run it against SampleDB using my batch file, it prompts the error message but continues to run the procedure disregarding my <code>RETURN</code> keyword. So the message would look like this:</p> <blockquote> <p>This is not a store database. Skipping this database.<br> Msg 3701, Level 11, State 5, Line 8<br> Cannot drop the procedure 'sp_ThisIsMySampleProcedure', because it does not exist or you do not have permission.</p> </blockquote> <p>I understand that the error message is correct because the procedure really doesn't exist on my SampleDB. But why does it still keep running when I have the <code>RETURN</code> keyword there after it satisfied the condition to skip running the entire script?</p> <p>Thank you!</p> <p><strong>EDIT:</strong></p> <p>Okay, I think people doesn't understand me completely. This is the scenario: My procedure (see above) works well on SQL Server Management Studio. What I mean is that whenever I try to run it on SampleDB, it gives me the message <code>This is not a store database. Skipping this database.</code></p> <p>But whenever I try to run my Batch-File which executes this stored procedure, I get this message on the command prompt:</p> <blockquote> <p>This is not a store database. Skipping this database.<br> Msg 3701, Level 11, State 5, Line 8<br> Cannot drop the procedure 'sp_ThisIsMySampleProcedure', because it does not exist or you do not have permission.</p> </blockquote> <p>Which basically means that the batch-file continued executing the whole SQL Script regardless that the first condition was satisfied.</p> <p>My question is how do I make the batch-file know that whenever SQL Server throws the message <code>This is not a store database. Skipping this database.</code> the batch-file will immediately stop the execution of the sql file.</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USSmiley
    1. USSmiley
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POStop Running a Procedure in Batch File
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POStop Running a Procedure in Batch File
      1. This table or related slice is empty.
      1. VTApproveEditSuggestion
    3. VO
      singulars
      1. POStop Running a Procedure in Batch File
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload