Creating dynamic variables in Stored procedure?

Question

Post reply

Creating dynamic variables in Stored procedure?

Brian MacKrill

SSC Enthusiast

Points: 194
More actions
December 25, 2007 at 5:03 am

#122772

Hello good people
I was wondering if it is possible to create a stored procedure variable dynamically. I have around 100 input parameters to process from a WEB front-end and need to validate each input variable and would like to reuse code. (SqlServer 2000 at present)
Eg:
Create Procedure ValidateAnswers
(
@A1 int,
@A2 int,
@A3 int,
... up to @A100 int) AS
Declare
@intParamCount Int, -- Used as general parameter counter
@intCurrent int -- Reused to process input params
Set @intParamCount = 1
While @intParamCount < 101
Begin
Set @intCurrent = @a(IntParamCount) <=== This is where I would like to create the variable A1 .. A100 using the counter and a text string "A")
Exec spProcessParams @intCurrent --- (A user defined function to process input params)
Set @intParamCount = @intParamCount + 1
End
Possible or is there a better way to do this other than repeat lines for each parameter?
Thanks in advance

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736479 More actions · Answer 1

You can't create a dynamic stored procedure like that. You would need to specify each variable:

create proc myproc

@a1 int = null

@a2 int = null

...

as

Specify a default value for each variable that you can test for. In this way you can pass in those parameters you need

exec myproc @a2 = 4

You can check if the parameter came in with "if @a2 is not null then exec otherproc a2"

There is no easy way to call another procedure for each variable without using dynamic sql, which opens you up to security issues.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 2

Ummmm... Not quite true... you can pass in a single delimited variable with named parameter pairs using either a delimited string or XML and split it in the proc. Of course, they wouldn't be treated as variables... you would, instead, join to the table that resulted from the split.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Brian MacKrill SSC Enthusiast Points: 194 More actions · Answer 3

Hey Steve and Jeff

I'll need to revisit my approach. Instead of 100 parameters I'll change it to a comma delimited string (what the hell ... the front-end designer can do some extra work too!) string, split it up with built-in functions and stick them into a temp table and validate.

I appreciate your response. Thanks!

ChiragNS One Orange Chip Points: 26137 More actions · Answer 4

Hi

I have used comma delimited strings as params and they work quite well for a few values. If you have 100 or more values to pass as params , I think you are better off using XML.

"Keep Trying"

jacob sebastian SSChampion Points: 11822 More actions · Answer 5

I agree with chirag and jeff. Probably you can look at a few examples that use XML:

http://www.sqlservercentral.com/articles/Miscellaneous/2908/

http://www.sqlservercentral.com/articles/Miscellaneous/2909/

http://www.sqlservercentral.com/articles/Miscellaneous/2911/

http://www.sqlservercentral.com/articles/Stored+Procedures/2912/

.

Brian MacKrill SSC Enthusiast Points: 194 More actions · Answer 6

Hi Jacob and Chirag

Thanks for the input. As the front-end is running Adobe Flex, XML is an option. Thanks for posting those examples. Very cool!

I will keep trying. Thanks!

Brian