how to use userdefined datatype in UDF?

how to use userdefined datatype in UDF?

Post by Rich Protze » Wed, 16 Jul 2003 06:45:05



Hello,

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


RETURNS bigint
AS  
BEGIN


  IncrInt1 = IncrInt1


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

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!

 
 
 

how to use userdefined datatype in UDF?

Post by Dean Savovi » Wed, 16 Jul 2003 09:18:56


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!


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


> Hello,

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


> RETURNS bigint
> AS
> BEGIN


>   IncrInt1 = IncrInt1


> 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

> 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!


 
 
 

how to use userdefined datatype in UDF?

Post by David Porta » Wed, 16 Jul 2003 09:24:43


Quote:> I created a userdefined datatype called IncrInt1 as bigint.  I want to


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)




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

 
 
 

1. Creating userdefined datatypes

I'm setting up the following constraint:

A user defined datatype : uddtProvince which must be 2
chars.  

So far, I have created the user defined datatype:

EXEC sp_addtype uddtProvince, 'nvarchar(2) ', 'NULL'

Next, I need to restrict the characters to be 2

ALTER TABLE Project
ADD CONSTRAINT Chk_ Region_Length
                CHECK (Region

2. Claris Solutions Alliance HELP?

3. Function returning table with columns userdefined datatype

4. ADO Renaming Access db Field?

5. Domains (userdefined Datatypes)

6. JDBC Driver for Informix Dynamic Server 2000

7. UDF problem using If-Else in simple UDF

8. US-DC-MD-Wash-Balto>**INFORMIX-4GL PROGRAMMERS**(Multiple Positions Av

9. UDF Input Datatype question

10. UDF wich RETURNs a table datatype variable

11. Is there a driver which will convert oracle datatype into sybase datatype ...when using with jdbc

12. xpath query against UDF with UDF parameters

13. using cursors and tables in a UDF