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

[quoted text, click to view]
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
--

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


[quoted text, click to view]