pgilfeather
asked on
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,@
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,@Param3
SELECT @MyVariableb = @@IDENTITY
...
Is it possible to alter this procedure such that it can use different parameters?
Hope this is clear
Regards
Paul G
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,@
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,@Param3
SELECT @MyVariableb = @@IDENTITY
...
Is it possible to alter this procedure such that it can use different parameters?
Hope this is clear
Regards
Paul G
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?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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...
2. not clear how you intend to use the outher parameters, how are you trying to present them to the procedure...
ASKER
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
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
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.