NetNews Usenet Archive 1992 #27/NN_1992_27.iso/spool/comp/database/7930

Internet Message Format | 1992-11-18 | 3.3 KB

Xref: sparky comp.databases:7930 comp.databases.sybase:347
Path: sparky!uunet!stanford.edu!bcm!mparsons
From: [e-mail] (Mark Parsons)
Newsgroups: comp.databases,comp.databases.sybase
Subject: Re: help needed on sybase stored procedure
Date: 18 Nov 1992 15:54:59 GMT
Organization: Baylor College of Medicine, Houston, Tx
Lines: 83
Distribution: world
Message-ID: <[e-mail]>
References: <[e-mail]> <steven.722080875@rupee>
Reply-To: [e-mail] (Mark Parsons)
NNTP-Posting-Host: fleming.csc.bcm.tmc.edu
Originator: [e-mail]


In article <steven.722080875@rupee>, [e-mail] (Steven Sweeting) writes:
|> [e-mail] (Julie Stephens) writes:
|>
|> >I'm attempting to create a generic Sybase stored procedure which will
|> >accept up to 254 input parameters.  Within the stored procedure
|> >I would like to determine how many parameters were passed to the
|> >stored procedure & process them.  To accomplish this, I'd like to
|> >have a loop which creates input parm names on the fly & retrieve
|> >the values stored in those input parameters. Can this be done?
|>
|> >Here is an example of what I'm trying to do.  The problem here is
|> >clear (see WHILE clause).  The program will evaluate the temporary
|> >variable @argName--not the input parameter @arg1.
|>
|> >    CREATE PROC processArguments
|> >        @arg1    varchar(255),
|> >        @arg2    varchar(255),
|> >        @arg3    varchar(255),
|> >        ...
|> >        @arg254 varchar(255)
|> >    AS
|> >    DECLARE @argName varchar(30)
|> >    DECLARE @argInt  int
|>
|> >    ProcessArguments:
|> >    SELECT @argInt = 1
|> >    SELECT @argName = '@arg1'
|>
|> >    WHILE @argName IS NOT NULL
|> >      BEGIN
|> >        (blah, blah, blah)
|> >        SELECT @argInt = @argInt + 1
|> >        SELECT @argName = '@arg' + (SELECT CONVERT(char(3), @argInt))
|> >      END


The suggestion on the recursive call got me thinking . . . .
Are you going to do similar processing, i.e., is the "(blah,
blah, blah)" the same?, or can you determine which processing to
do based on the actual *value* of the arg's?  Will the processing
of the args be independent of each other?

If so . . and I know this isn't as slick as we'd all like . . but
hopefully a little easier to write and debug than the recursive
call(no offense!! ;-) . . .

Redefine your first proc like such:

CREATE PROC processArguments
    @arg1    varchar(255) = NULL,    /* default to NULL if not supplied */
    ...
    @arg244    varchar(255) = NULL
AS
    exec processArguments2 @arg1
    ...
    exec processArguments2 @arg254
RETURN

Now define the second process:

CREATE PROC processArguments2
    @arg1    varchar(255)
AS
    if @arg1 is not NULL    /* or whatever series of tests you need? */
    begin
        blah, blah, blah
    end
RETURN

Of course, you could go straight to processArguments2 from your front
end . . but you'd have to do 254 calls whereas using the two level
method above would require only one front-end-to-server call.  The
calls between the processes within the server will take up much less
overhead than doing separate calls to the server(from the front-end)
for each possible variable.

Sybase isn't going to allow indirect variable processing . . . so this
is the . . .um . . 'cleanest' idea I can come up with . . .

Other suggestions?

Mark