How to get Stored Procedure to Run in Scheduled Job

1

I have done this in the past, but have forgotten how to save the Stored Procedure to make it Run like it does when you comment out the 'Create' portion of the Procedure, and uncomment the 'Begin' and 'End'. When I save it like that, it just changes 'Create' to 'Alter' and does not run in the Scheduled Job. What am I missing? What are the steps to creating a Stored Procedure? Here is what I have done to create mine:

.
.
.
/*BEGIN */
CREATE PROCEDURE [dbo].[UpdateWSDCustomer]
@SAMPNAME nvarchar(254) = ''
as
set nocount on

begin transaction
UPDATE CUSTOMER
SET SAMPNAME = ...

Then after I have all of my queries in place and have Run it to Create the Stored Procedure, I place Comment markers before 'Create' and after 'set nocount on'. Then I remove the comments from the 'Begin' and 'End' and run it to make sure I get the output I am expecting. Then I save the Stored Procedure, thinking that it will save it with the 'Begin' and 'End' uncommented, and ready to Run... but it does not. It just changes the 'Create' to 'Alter' and comments out the 'Start' and 'End', and that fails when I try to run it in a job.

I have a database that gets updated every 4 hours and then I have Stored Procedures that run immediately after to correct any incorrectly formatted data. But what is the process that I am missing to Save this Stored Procedure with the 'Start' and 'End' uncommented so that the Procedure will run?

Thank you.

2

I didn't quite understand what you are trying to accomplish. Under most circumstances, you shouldn't have to comment or uncomment code to make it run. The code fragment that you have posted (along with the rest of the code) is the code for creating a stored procedure. When you run it, it creates a stored procedure in the database. From then on, you can use that stored procedure by invoking it like this:

EXEC [dbo].[UpdateWSDCustomer] @SAMPNAME = N'WhateverSampleNameParameterYouWantToUse';

If you need to make changes to the stored procedure code, then the most expedient way is to use SSMS to find the stored procedure (it will be in the object explorer under YourServerName -> YourDatabaseName -> Programmability -> Stored procedures ), right click and select script stored procedure as -> alter to -> new query editor window). This will show you the code. You can make the changes and click the execute button once to save the changed stored procedure.

The process you described appears to be something ad-hoc that is very unusual.

3

Or just click EDIT (or maybe it is MODIFY?) at that point?

(Personally we store source code for each Sproc in a separate file, but people do certainly just "edit" the Sprocs in-situ in the DB)

4

It is MODIFY.

When I want to modify a stored proc, I select the CREATE option, make the changes I want, and finally when I am satisfied, I change the CREATE keyword to ALTER. Just a habit that I have that helps me avoid accidentally executing the code when I didn't mean to.

5

OR...
Go to your source control system and check out the latest version of the code for edit.

6

:slight_smile: Thanks.

I prefer to have the code outside the database. We have "other stuff" in the file along with the SProc. All the necessary (EXEC) permissions, the UNIT tests and so on.

Also, I have known of situations where DEVs just doing Right-Click-Modify have had two people modify an Sproc at the same time, with one person's (different) changes overwriting the other. Still happens with separate code files of course, but the Check-In process, brings to light that there is a conflict.