Professional Web Applications Themes

how to use userdefined datatype in UDF? - Microsoft SQL / MS SQL Server

Hello, I created a userdefined datatype called IncrInt1 as bigint. I want to use this data type in a UDF as follows: CREATE FUNCTION [Incre] (Inc bigint) RETURNS bigint AS BEGIN Declare Incr bigint If Inc = null IncrInt1 = IncrInt1 set Incr = IncrInt1 return Incr END I want to use IncrInt1 like a static var where I am sequentially incrementing IncrInt1 and will write that value to a bigint field in a table. I pass a field value to Inc (which will always be null). Then I want to set that field to the value of IncrInt1. But ...

  1. #1

    Default how to use userdefined datatype in UDF?

    Hello,

    I created a userdefined datatype called IncrInt1 as bigint. I want to
    use this data type in a UDF as follows:

    CREATE FUNCTION [Incre] (Inc bigint)
    RETURNS bigint
    AS
    BEGIN
    Declare Incr bigint
    If Inc = null
    IncrInt1 = IncrInt1
    set Incr = IncrInt1

    return Incr
    END

    I want to use IncrInt1 like a static var where I am sequentially
    incrementing IncrInt1 and will write that value to a bigint field in a
    table. I pass a field value to Inc (which will always be null). Then
    I want to set that field to the value of IncrInt1. But I have a syntax
    error with the above at the userdefined datatype. Is it possible to use
    a userdefined datatype in a UDF?

    Specifically here is the sql I would use to call the udf

    Update tbl1 Set IDcount = dbo.Incre(IDcount)

    Is this doable? How?

    Thanks
    Rich

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Rich Guest

  2. #2

    Default Re: how to use userdefined datatype in UDF?

    I'm not sure that I know what you whant to do! Tried to figure it out from
    you create function code but it makes no sense!

    First of all when comparing a variable to a NULL value use "is null" or "is
    not null" because two NULLs are not equal!

    Line of code IncrInt1 = IncrInt1 is useless because IncrInt1 is datataype
    not a variable!

    Why pass a value to a function that is always null (you say that Inc is
    allways null!)?

    Describe the problem in more detail so we can provide help, and yes you can
    use UDD in UDF but you must follow some rules!

    --
    Dean Savovic
    www.teched.hr


    "Rich Protzel" <com> wrote in message
    news:phx.gbl... 


    Dean Guest

  3. #3

    Default Re: how to use userdefined datatype in UDF?

    > I created a userdefined datatype called IncrInt1 as bigint. I want to
    Data types for variables are specified in a DECLARE statement: DECLARE var
    INCRINT1

    Your incrementing function won't work because UDFs are deterministic. Also,
    there are no global, user-defined, variables allowed in TSQL so you can't
    implement a Rownum count within a function. Even if there were global
    variables they probably wouldn't be permitted in UDFs.

    Either use an IDENTITY column or use the solution that Anith gave to your
    earlier post (reproduced below). Better still, rethink your requirement. Are
    you sure you really need an arbitrary unique row number and for what
    purpose? Do you really need a BIGINT for this? If you genuinely have more
    than 2 billion rows then creating the Rownum count will be a very big
    overhead which maybe you don't really need.

    CREATE TABLE tbl1 (keycol INTEGER PRIMARY KEY, rownum BIGINT NULL)

    INSERT INTO tbl1 VALUES (1,NULL)
    INSERT INTO tbl1 VALUES (2,NULL)
    INSERT INTO tbl1 VALUES (3,NULL)

    DECLARE r BIGINT
    SET r=0
    UPDATE tbl1 SET r = RowNum = r + 1

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --



    David Guest

Similar Threads

  1. ASP.NET Insert into bit datatype
    By RajDogLives in forum Adobe Dreamweaver & Contribute
    Replies: 9
    Last Post: May 20th, 04:45 PM
  2. citext datatype
    By Sim Zacks in forum PostgreSQL / PGSQL
    Replies: 0
    Last Post: January 2nd, 12:20 PM
  3. I think it's a datatype issue
    By Fox in forum ASP Database
    Replies: 11
    Last Post: January 7th, 09:01 PM
  4. UserDefined Fuctions and SQL
    By Trent in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 14th, 01:55 PM
  5. Check datatype
    By Andrew Durstewitz in forum ASP
    Replies: 2
    Last Post: June 30th, 03:46 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