?
Solved

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

Posted on 2005-03-18
7
Medium Priority
?
258 Views
Last Modified: 2006-11-17
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
Comment
Question by:pgilfeather
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 9

Expert Comment

by:caball88
ID: 13576710
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
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 640 total points
ID: 13576838
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
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 13582099
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
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 

Author Comment

by:pgilfeather
ID: 13589684
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
 
LVL 9

Expert Comment

by:caball88
ID: 13591644
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
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 680 total points
ID: 13599020
>>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
 
LVL 9

Assisted Solution

by:caball88
caball88 earned 680 total points
ID: 13603246
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

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question