Dynamic Parameters for Stored Procedures
-
I want to write a stored procedure whose number of parameters will be dynamically decided.
e.g
In One case i will send two parameters to this procedure.
AND
In 2nd case i will send three parameters to this procedure.
The problem is the number of parameters could vary from 1--100. There for i dnt want to fix the number of parameters in the create script of the stored procedure.
I think i can do this using array kind of thing, but i dnt know the exact solution.
-
you can set a default value to the parameter. Then check to see if the value is different so that you know you need to use it. But you can't change the number of parameters. Also if you only have a few parameters to pass, you can use named parameters like :
exec spname @ParamName = 'SomeValue', @Param3 = 6...
That way you can pass only what you need (providing all the other params have defaults).
-
I don't think there is any way, short of using dynamic T-SQL to recompile the procedure, to alter the number of parameters that a T-SQL procedure accepts. However, you can define as many parameters as you need (up to 2100) and default them to NULL. Then the calling program can send over as many as it wants, leaving the called procedure to test for the parameters that are NOT NULL, i.e., present in the call at run time.
E.g.,
CREATE PROCEDURE p_foo
@parm_1 CHAR (1) = NULL
, @parm_2 CHAR (1) = NULL
, @parm_3 CHAR (1) = NULL
, @parm_4 CHAR (1) = NULL
, ...
, @parm_n CHAR (1) = NULL
AS
IF @parm_1 IS NOT NULL
--THEN
{use it}
--END IF
IF @parm_2 IS NOT NULL
--THEN
{use it}
--END IF
{etc.}
GO
Another possibility, I suppose, would be to use the little-used procedure number, and then you could dynamically determine how to call it:
CREATE PROCEDURE p_foo;1
@parm_1 CHAR (1) = NULL
AS
{etc.}
GO
CREATE PROCEDURE p_foo;2
@parm_1 CHAR (1) = NULL
, @parm_2 CHAR (1) = NULL
AS
{etc.}
GO
CREATE PROCEDURE p_foo;3
@parm_1 CHAR (1) = NULL
, @parm_2 CHAR (1) = NULL
, @parm_3 CHAR (1) = NULL
AS
{etc.}
GO
But you're then stuck with a complicated calling scheme and a lot of configuration management headaches.
-
Another possibility would be to have a single parameter that is a large varchar field. You could then send in parameters as a string composed of name/value pairs ( or a single comma delimited list of values) and parse the single string in the stored procedure.
I have actually used this technique many times when I needed to send a stored proc a variable amount of data.
Gordon
Gordon Pollokoff
"Wile E. is my reality, Bugs Bunny is my goal" - Chuck Jones
-
I'd like to see an exemple of such a proc with 20+ parameters if it's possible.
-
Picking up where Lee left off...maybe the 2 methods he listed should be combined to use nested sprocs ?!?! - Hopefully - even though the range of parameters is 1 - 100 - maybe some of them can be tested in "sets/blocks" of "IF @parm_1 IS NOT NULL AND @parm_2 IS NOT NULL" etc..as opposed to "IF @parm_1 IS NOT NULL do...", IF @parm_2 IS NOT NULL do..." etc...
- how tedious - CREATE PROCEDURE p_foo @parm_1 CHAR (1) = NULL , @parm_2 CHAR (1) = NULL , @parm_3 CHAR (1) = NULL , @parm_4 CHAR (1) = NULL , ... , @parm_n CHAR (1) = NULL AS IF @parm_1 IS NOT NULL --THEN EXEC p_foo1 @parm_1 --END IF IF @parm_2 IS NOT NULL --THEN EXEC p_foo2 @parm_2 OR EXEC p_foo2 @parm_1, @parm_2 --END IF {etc.}...the logic and conditions to be worked out based on requirements... GO
**ASCII stupid question, get a stupid ANSI !!!** -
Thought I had an example at hand, but can't put my finger on it at the moment. However, the basic technique is as follows:
If I need to send in a variable number of parameters, I could sent in a string as follows:
'parm1,parm2,parm3,parm4,parm5,parm6,...,parmN'
I can than parse this string, using ',' as a delimiter, extracting each parameter and processing as required.
Another possibility is to send the parameters in a string formated as XML. You can than load and query the xml from within a stored proc to extract parameter name and parameter value. Here is a code snippet which extracts a list of code objects which was sent to the proc as an XML formatted string in the variable called @tables. I can than usethe #Objects table to process the list of objects
DECLARE @idoc int
-- Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @tables
-- Execute a SELECT statement using OPENXML rowset provider.
insert into #Objects (objectname,fileversion,objtype)
SELECT name,version,type
FROM OPENXML (@idoc, '/request/object',1)
WITH (name varchar(100),
version varchar(10)
type varchar(10)
 
EXEC sp_xml_removedocument @idoc
Gordon Pollokoff
"Wile E. is my reality, Bugs Bunny is my goal" - Chuck Jones
-
Hi Gordon,
Two threads with similar topics.....
I have achieved the same thing on numerous occaisions using a sproc I found on here many moons ago. Just create the sproc, give it a string to strip, tell it what the delimiter is, which table / column to put the results in end then process the results however you require.
/*###########################################################################################
PURPOSE
Takes in a list of values in a string, a delimiting character, a table name and a column name and seperates the values
based around the delimiting character
PARAMETERS
@vcList - List of values, delimited by @vcDelimiter
@vcDelimiter - Delimiting character
@TableName - Name of Table to pass seperated values into
@ColumnName - Names of Column to pass seperated values into
NOTES
############################################################################################*/
CREATE PROCEDURE [sprocListToTable]
@vcList VARCHAR(8000),
@vcDelimiter VARCHAR(8000),
@TableName SYSNAME,
@ColumnName SYSNAME
AS
SET NOCOUNT ON
DECLARE @iPosStart INT,
@iPosEnd INT,
@iLenDelim INT,
@iExit INT,
@vcStr varchar(8000),
@vcSql varchar(8000)
SET @iPosStart = 1
SET @iPosEnd = 1
SET @iLenDelim = LEN(@vcDelimiter)
SET @vcSql = 'INSERT ' + @TableName + ' (' + @ColumnName + ') VALUES ('''
SET @iExit = 0
WHILE @iExit = 0
BEGIN
SET @iPosEnd = CHARINDEX(@vcDelimiter, @vcList, @iPosStart)
IF @iPosEnd <= 0
BEGIN
SET @iPosEnd = LEN(@vcList) + 1
SET @iExit = 1
END
SET @vcStr = SUBSTRING(@vcList, @iPosStart, @iPosEnd - @iPosStart)
EXEC(@vcSql + @vcStr + ''')')
SET @iPosStart = @iPosEnd + @iLenDelim
END
RETURN 0
GO
Have fun
Steve
We need men who can dream of things that never were.
-
Another option would be to build a table to hold the values for your parameter values and use that table in your procedure. This method could help you avoid a lot of dynamic SQL and the number of values is basically limitless.
Good luck!
hawg
----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011
-
Would you have some example code of this table thing?
-
Create procedure My_proc
as
SELECT * FROM #TempTable
GO
CREATE #TempTable (
Col001 int,
Col001 nvarchar(50)
)
INSERT INTO #TempTable
SELECT (whatever)
EXEC My_Proc
DROP TABLE #TempTable
_____________
Code for TallyGenerator
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply