x

Pass parameter to run specific part of stored proc

I have a stored procedure that has 4 blocks of code to update a table but I want to be able to pass a parameter to either run all 4 blocks or 1 block or 2 blocks or 3 blocks. How would I do this?

Stored Proc attached. Thankslink text

storedproc.txt (1.4 kB)
more ▼

asked Mar 14 at 02:13 PM in Default

avatar image

red68
498 11 16 25

I ended up combining the blocks in 1 stored proc and then use IF statements to process.

Mar 16 at 01:56 PM red68
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

You could use IF statements and GOTO statements at the end of each block to determine which block to execute next. Simple example here: https://www.techonthenet.com/sql_server/loops/goto.php

more ▼

answered Mar 15 at 01:56 PM

avatar image

Ange
130 2 4

(comments are locked)
10|1200 characters needed characters left

What restrictions are there on the "parameter"? Is it a requirement that there be only one? The reason I ask is that you could have four parameters:

 CREATE PROCEDURE dbo.my_procedure @run_insert char(1), @run_update char(1), @run_delete char(1), @run_select char(1)
 .
 .
 .
 INSERT dbo.blah
 .
 .
 .
 WHERE @run_insert = 'Y';
 .
 .
 .

Usage:

 exec dbo.my_procedure 'Y','N','Y','N';
 exec dbo.my_procedure 'N','Y','N','N';

If you really must only have one parameter you could pass in a string of Ys and Ns and split it working out what to run based on the order

more ▼

answered Mar 18 at 12:30 AM

avatar image

GPO
4.9k 42 52 58

No, it can be multiple parameters. I was thinking I could use this Y and N flag to run certain excludes or enter exclude number to run. Thanks I will look into this as well. This way, I don't have to use multiple procedure calls.

Mar 18 at 01:45 AM red68
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x428

asked: Mar 14 at 02:13 PM

Seen: 32 times

Last Updated: Mar 18 at 01:45 AM

Copyright 2018 Redgate Software. Privacy Policy