Professional Web Applications Themes

UDF Variable parameter lists? - Microsoft SQL / MS SQL Server

Dear all, I am wanted to write a UDF where the number of parameters is not known until runtime. Any ideas on how to do this? I have looked on BOL and the web and can't find any answers. Many of the UDF's that come with SQL Server display similar functionality. Such as CHECKSUM() which can also take varied data types. Thanks Ed...

  1. #1

    Default UDF Variable parameter lists?

    Dear all,

    I am wanted to write a UDF where the number of parameters is not known
    until runtime. Any ideas on how to do this? I have looked on BOL and
    the web and can't find any answers. Many of the UDF's that come with
    SQL Server display similar functionality. Such as CHECKSUM() which can
    also take varied data types.

    Thanks

    Ed
    Edward Guest

  2. #2

    Default Re: UDF Variable parameter lists?

    Hi Edward,
     
    They're not _user_ defined, that's why :-)

    You can create the function with 2 varchar(8000) parameters, one for the
    parameterlist and the second one for the values of the parameters, similar
    to sp_executesql. In your function you can then split the varchar (8000)
    parameters into the parameters you actually want to work with(see
    http://www.algonet.se/~sommar/arrays-in-sql.html for a number of methods)
    and process them. You have to convert all your parameters to varchar for
    this to work, which can create some issues, but you can provide the datatype
    for the actual parameters in the parameterlist (which will look like
    'Customer_ID int, Customer_Name varchar(30)' etc) and do checking in your
    function to make sure that the values provided are actually of the
    appropriate data type.

    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "Edward Nicholas" <com> wrote in message
    news:google.com... 


    Jacco Guest

  3. #3

    Default Re: UDF Variable parameter lists?

    Erland Sommarskog wrote: 
    >
    >
    > CHECKSUM() is not a UDF, it is a systemfunction. The only UDFs that
    > comes with SQL Server are the functions whose namn start with fn_
    >
    > A UDF you write yourself, must have a fixed number of parameters. You
    > can have default values, but you must still supply DEFAULT as placeholders.
    >
    > I don't know what your actual business problem is, but in case you want
    > to pass something which logically is an array, this link maybe useful:
    > http://www.algonet.se/~sommar/arrays-in-sql.html.
    >[/ref]

    Hello Erland,

    I have implemented the function, iter_charlist_to_table, on this web
    site and it works fine. However, what if I have a stored procedure that
    accepts 2 parameters of 1 or more lists? How would I implement this
    function twice in a SELECT statement?

    prm_list1 = '101, 102, 103, 104'
    prm_list2 = '0011, 0012, 0013, 0014'

    Any suggestions you may have would be most appreciated.

    Thanks,

    Steve

    Steve Guest

  4. #4

    Default Re: UDF Variable parameter lists?

    Erland Sommarskog wrote:
     
    >
    >
    > SELECT *
    > FROM iter_charlist_to_table(prm_list1, DEFAULT) AS a
    > JOIN iter_charlist_to_table(prm_list1, DEFAULT) AS b ON a.str = b.str
    >
    > would find all equal values.
    >
    > If you need to correlate on position in the list, there is a listpos
    > column in the return table.
    >[/ref]

    Thanks Erland,

    Let me try to be more specific. Here's what I currently have...

    DECLARE SetCodes nvarchar(2000)
    DECLARE Chains nvarchar(2000)

    SET SetCodes = '062, 063, 064, 065'
    SET Chains = '0011'

    SELECT TOP 100 PERCENT
    DMANo,
    SUM(AUVCalcYr01) AS sum01,
    SUM(AUVCalcYr02) AS sum02,
    SUM(AUVCalcYr03) AS sum03,
    SUM(AUVCalcYr04) AS sum04,
    SUM(AUVCalcYr05) AS sum05,
    SUM(AUVCalcYr06) AS sum06,
    SUM(AUVCalcYr07) AS sum07,
    SUM(AUVCalcYr08) AS sum08,
    SUM(AUVCalcYr09) AS sum09,
    SUM(AUVCalcYr10) AS sum10,
    SUM(StoreOpen01) AS sumopen01,
    SUM(StoreOpen02) AS sumopen02,
    SUM(StoreOpen03) AS sumopen03,
    SUM(StoreOpen04) AS sumopen04,
    SUM(StoreOpen05) AS sumopen05,
    SUM(StoreOpen06) AS sumopen06,
    SUM(StoreOpen07) AS sumopen07,
    SUM(StoreOpen08) AS sumopen08,
    SUM(StoreOpen09) AS sumopen09,
    SUM(StoreOpen10) AS sumopen10,
    SUM(StoreResponding01) AS respsum01,
    SUM(StoreResponding02) AS respsum02,
    SUM(StoreResponding03) AS respsum03,
    SUM(StoreResponding04) AS respsum04,
    SUM(StoreResponding05) AS respsum05,
    SUM(StoreResponding06) AS respsum06,
    SUM(StoreResponding07) AS respsum07,
    SUM(StoreResponding08) AS respsum08,
    SUM(StoreResponding09) AS respsum09,
    SUM(StoreResponding10) AS respsum10
    FROM RT..StoreHistory
    JOIN iter_charlist_to_table(SetCodes, DEFAULT) i ON SetCode = i.nstr
    WHERE ChainNo = Chains
    GROUP BY DMANo
    ORDER BY DMANo

    Now, this works fine having multiple SetCodes and 1 chain. But, I need
    to be able to accept multiple chains. I'm sure that the I could use
    something like: WHERE ChainNo IN( Chains ) But if the user passes in 20
    chains then I'm stuck with a possible performance issue. This will
    eventually be a stored procedure where SetCodes and Chains are being
    passed in, but right now I'm playing around with this in the Query
    yzer.

    There is only 1 table I'm selecting from (StoreHistory) as shown above.
    How can I modify the above SELECT statement to achive what I want? Or,
    am I going about this the wrong way?

    Thanks,

    Steve

    Steve Guest

  5. #5

    Default Re: UDF Variable parameter lists?

    Steve Thompson (com) writes: 

    FROM RT..StoreHistory
    JOIN iter_charlist_to_table(SetCodes, DEFAULT) i ON SetCode = i.nstr
    JOIN iter_charlist_to_table(Chains, DEFAULT) j ON ChainNo = j.nstr


    --
    Erland Sommarskog, SQL Server MVP, se

    Books Online for SQL Server SP3 at
    http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
    Erland Guest

  6. #6

    Default Re: UDF Variable parameter lists?



    Erland Sommarskog wrote: 
    >
    >
    > FROM RT..StoreHistory
    > JOIN iter_charlist_to_table(SetCodes, DEFAULT) i ON SetCode = i.nstr
    > JOIN iter_charlist_to_table(Chains, DEFAULT) j ON ChainNo = j.nstr
    >
    >[/ref]

    That works great!

    Thank you,

    Steve

    Steve Guest

Similar Threads

  1. variable in an object parameter?
    By NoMailJP in forum Macromedia Dynamic HTML
    Replies: 0
    Last Post: April 12th, 05:04 PM
  2. global variable and lists
    By MS_RASdesign in forum Macromedia Director Lingo
    Replies: 3
    Last Post: February 11th, 06:26 PM
  3. variable as a parameter
    By raul76@gmx.de in forum PHP Development
    Replies: 3
    Last Post: October 10th, 05:41 PM
  4. variable in function parameter
    By 386-Dx in forum PHP Development
    Replies: 4
    Last Post: July 29th, 07:17 AM
  5. dropdown lists - query parameter error
    By KathyB in forum ASP.NET General
    Replies: 1
    Last Post: June 26th, 06:59 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139