• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 260
  • Last Modified:

Is there a way of using different sets of parameters in one stored procedure?

Hi,

Suppose I have the following code

CREATE PROC procMyProcedure
--Is the following the only place in which you can introduce parameters to a stored procedure or can you do it anywhere in code?
@Flag Char(1),
@Param1 int,
@Param2 int,
@Param3 int,
@Param4 int
AS
Declare @MyVariable Int
If @Flag = 'I'
INSERT INTO MyTable
VALUES (@Param1,@Param2,@Param3,@Param4)
SELECT @MyVariable = @@IDENTITY

How can I add the following code which relies on completely different set of parameters?
I am trying to establish an update pattern that can take different sets of parameters.

Declare @MyVariableb Int
If @Flag = 'Ib'
INSERT INTO MyOtherTable
VALUES (@Param1b,@Param2b,@Param3b,@Param4b)-- These parameters don't exist but how can I make them exist?
SELECT @MyVariableb = @@IDENTITY
...
Is it possible to alter this procedure such that it can use different parameters?

Hope this is clear

Regards

Paul G
0
pgilfeather
Asked:
pgilfeather
3 Solutions
 
caball88Commented:
are your second set of params different datatypes? if so you will need to include them in the parameter list for the stored proc. where are you getting the values for the second set of params? you can dynamically declare variables but there is no way to make the stored proc behave dynamic when it comes to the parameters it expects. how would the proc know what values to use for the second set of params?
0
 
Scott PletcherSenior DBACommented:
No, but you could make each set of params optional and only specify the set you wanted to, for example:

CREATE PROC procMyProcedure
--Is the following the only place in which you can introduce parameters to a stored procedure or can you do it anywhere in code?
@Flag Char(1),
@Param1 int = null,
@Param2 int = null,
@Param3 int = null,
@Param4 int = null,
@Param1b varchar(10) = null,
@Param2b varchar(10) = null,
@Param3b varchar(10) = null,
@Param4b varchar(10) = null
AS
...


EXEC procMyProcedure 'A', 1, 2, 3, 4

EXEC procMyProcedure 'B', @param1b = 'a', @param2b = 'b', @param3b = 'c', @param4b = 'd'

0
 
LowfatspreadCommented:
1. use the scope_identity function rather than @@identity its much safer in the long run..

2. not clear how you intend to use the outher parameters, how are you trying to present them to the procedure...


 
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
pgilfeatherAuthor Commented:
Lowfatspread

I think what I am trying to say is I want the stored procedure to be multifunctional and therefore be quite flexible in adjusting to whatever parameters I provide. The point is that I will be feeding different sets of parameters into the procedure and I want the procedure to respond differently according to what the @Flag parameter is set to.

Cheers


Paul G
0
 
caball88Commented:
ok another way you can make the procedure "multifunctional" is to make one or more parameters a big varchar string that is delimited with a character. you can have your flag indicate what the procedure should do in the stored proc. you can also do without the delimited varchar and just make 4 params of varchar and have t he flag indicate what the stored proc needs to do. you will have to cast the varchars to integers if needed.
0
 
LowfatspreadCommented:
>>I think what I am trying to say is I want the stored procedure to be multifunctional and therefore be quite flexible in adjusting to whatever parameters I provide.

 *KISS*
(Keep it Simple S...)

fine be flexible and have a parameter which governs some actions, but your stored procedures should really concentrate on doing
at set of similar tasks well...

if you overcomplicate the procedure then it wont optimise very well, there is a point at which coding another procedure is a better idea....

you can modularise your procedures... (call another procedure from within one...)

so yes define all the parameters you want and supply defaults to them , and then invoke the procedure using
named parameters always but do remember  *KISS*


hth  



0
 
caball88Commented:
again i agree with lowfatspread, if you try to make your stored proc more and more dynamic and flexible you will run into performance issues with SQL. if you try to make one query do so many things it might suffer in terms of performance and managability. someone else picking up a monsterous stored proc will have trouble scaling it later on.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now