udf not working like sql - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

udf not working like sql

Author

Topic

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2005-11-24 : 12:31:34

i have a problem where a sql script behaves one way but if i put that same code in a udf, it misbehaves and returns a different result.
basically this query returns a bitmask that shows the combination of types that a contact at a company has been set to
here's the sql that works fine:

CREATE TABLE [dbo].[mytbl] (
[mytype] [int] NULL ,
[marked] [bit] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[mytbl] ADD
CONSTRAINT [DF_mytbl_mytype] DEFAULT (0) FOR [mytype],
CONSTRAINT [DF_mytbl_marked] DEFAULT (0) FOR [marked]
GO

DECLARE @RowsAdded int
declare @typ int
declare @typbuf int

set @typ = 0
set @typbuf = 0
set @RowsAdded = 0

insert mytbl(myType)
select TYP_TypeEnum - 1000 --(the result of this is one of the follwing vals: 1,2,4,8,16,32,64)
FROM Types
INNER JOIN TypeLinks ON TYP_TypeID = CTS_TypeID
INNER JOIN CompanyContacts ON CTS_ParentID = CON_ContactID
where CON_ContactID = '3CF4C52C-298B-451E-9F71-2D43901B6C8F'

SET @RowsAdded = @@rowcount
WHILE @RowsAdded > 0
begin
set @typbuf = (select top 1 myType from mytbl where Marked = 0)
if @typbuf is null break
SET @RowsAdded = @@rowcount
set @typ = @typ | @typbuf
update mytbl set Marked = 1 where myType = @typbuf
end
print @typ

so there you go, that the sql statement that works fine. if i for example have a contact that has to typelinks records (say one is 1 and the other is 32), "print @typ" returns 33

HOWEVER, do the same thing in a function, and the function returns 1 and not 33:

create FUNCTION dbo.udf_gui_GetContactTypeBitMask(@contactGUID UNIQUEIDENTIFIER)
returns int
AS
BEGIN
declare @tbl table (Typ int default 0, Marked bit default 0)
DECLARE @RowsAdded int
declare @typ int
declare @typbuf int

set @typ = 0
set @typbuf = 0
set @RowsAdded = 0

insert @tbl(Typ)
select TYP_TypeEnum - 1000
FROM Types
INNER JOIN TypeLinks ON TYP_TypeID = CTS_TypeID
INNER JOIN CompanyContacts ON CTS_ParentID = CON_ContactID
where CON_ContactID = @contactGUID

SET @RowsAdded = @@rowcount

WHILE @RowsAdded > 0
begin
set @typbuf = (select top 1 Typ from @tbl where Marked = 0)
if @typbuf is null break
SET @RowsAdded = @@rowcount
set @typ = @typ | @typbuf
update @tbl set Marked = 1 where Typ = @typbuf
end

RETURN @typ
END

and then call it:
select dbo.udf_gui_GetContactTypeBitMask('3CF4C52C-298B-451E-9F71-2D43901B6C8F')

I've spent an hour on this, can any kind soul help?

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2005-11-24 : 17:02:08

Sorry guys, I didnt give you enough info, can we try that again? I was talking rubbish as well. both cases below produce 1 as a result but I am after 33 as a result when I "or" the values. So it's got nothing to do with whether you use a function or a script. But debugging the script may be easier for you. I'm sure it's something dead simple. What am I missing?

--ddl
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[mytbl]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[mytbl]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CompanyContacts]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[CompanyContacts]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TypeLinks]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TypeLinks]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Types]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Types]
GO

CREATE TABLE [dbo].[TypeLinks] (
[CTS_TypeLinkID] uniqueidentifier ROWGUIDCOL NOT NULL ,
[CTS_TypeID] [uniqueidentifier] NOT NULL ,
[CTS_ParentID] [uniqueidentifier] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Types] (
[TYP_TypeID] uniqueidentifier ROWGUIDCOL NOT NULL,
[TYP_TypeEnum] [smallint] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[TypeLinks] ADD
CONSTRAINT [DF_TypeLinks_CTS_TypeLinkID] DEFAULT (newid()) FOR [CTS_TypeLinkID]
GO

ALTER TABLE [dbo].[Types] ADD
CONSTRAINT [DF_Types_TYP_TypeID] DEFAULT (newid()) FOR [TYP_TypeID]
GO

CREATE TABLE [dbo].[CompanyContacts] (
[CON_ContactID] [uniqueidentifier] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[CompanyContacts] ADD
CONSTRAINT [DF_CompanyContacts_CON_ContactID] DEFAULT (newid()) FOR [CON_ContactID]
GO

CREATE TABLE [dbo].[mytbl] (
[mytype] [int] NULL ,
[marked] [bit] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[mytbl] ADD
CONSTRAINT [DF_mytbl_mytype] DEFAULT (0) FOR [mytype],
CONSTRAINT [DF_mytbl_marked] DEFAULT (0) FOR [marked]
GO

--test data
---types
declare @a1 uniqueidentifier
declare @a2 uniqueidentifier
declare @a3 uniqueidentifier
declare @a4 uniqueidentifier
declare @a5 uniqueidentifier
declare @a6 uniqueidentifier
declare @a7 uniqueidentifier
set @a1 = newid()
set @a2 = newid()
set @a3 = newid()
set @a4 = newid()
set @a5 = newid()
set @a6 = newid()
set @a7 = newid()
insert into types select @a1,1
insert into types select @a2,2
insert into types select @a3,4
insert into types select @a4,8
insert into types select @a5,16
insert into types select @a6,32
insert into types select @a7,64

--contact
declare @c1 uniqueidentifier
set @c1 = newid()
insert into CompanyContacts select @c1

--typelinks
declare @t1 uniqueidentifier
declare @t2 uniqueidentifier
set @t1 = newid()
set @t2 = newid()
insert into typelinks select @t1,@a1,@c1
insert into typelinks select @t2,@a6,@c1

--sql script that prints "1" - but I'm trying to get it to register 33 because I want it to "OR" the values
DECLARE @RowsAdded int
declare @typ int
declare @typbuf int

set @typ = 0
set @typbuf = 0
set @RowsAdded = 0

insert mytbl(myType)
select TYP_TypeEnum
FROM Types
INNER JOIN TypeLinks ON TYP_TypeID = CTS_TypeID
INNER JOIN CompanyContacts ON CTS_ParentID = CON_ContactID
where CON_ContactID = @c1

SET @RowsAdded = @@rowcount
WHILE @RowsAdded > 0
begin
set @typbuf = (select top 1 myType from mytbl where Marked = 0)
if @typbuf is null break
SET @RowsAdded = @@rowcount
set @typ = @typ | @typbuf
update mytbl set Marked = 1 where myType = @typbuf
end
print @typ
go

--same thing in a function, returns 1 and not 33:

create FUNCTION dbo.udf_gui_GetContactTypeBitMask(@contactGUID UNIQUEIDENTIFIER)
returns int
AS
BEGIN
declare @tbl table (Typ int default 0, Marked bit default 0)
DECLARE @RowsAdded int
declare @typ int
declare @typbuf int

set @typ = 0
set @typbuf = 0
set @RowsAdded = 0

insert @tbl(Typ)
select TYP_TypeEnum
FROM Types
INNER JOIN TypeLinks ON TYP_TypeID = CTS_TypeID
INNER JOIN CompanyContacts ON CTS_ParentID = CON_ContactID
where CON_ContactID = @contactGUID

SET @RowsAdded = @@rowcount

WHILE @RowsAdded > 0
begin
set @typbuf = (select top 1 Typ from @tbl where Marked = 0)
if @typbuf is null break
SET @RowsAdded = @@rowcount
set @typ = @typ | @typbuf
update @tbl set Marked = 1 where Typ = @typbuf
end

RETURN @typ
END
go
--and then call it:
declare @c1 uniqueidentifier
set @c1 = (select top 1 con_contactid from companycontacts)
select dbo.udf_gui_GetContactTypeBitMask(@c1)

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2005-11-24 : 17:19:21

Well well well, what do we have here??? It appears I have a solution that envolves CURSORS

Any Master based sets out there care to show me how to do this without one? (jokes aside, this is a "production" query and I don't REALLY want to leave the cursor code in there...)

Runn this against the DDL provided previously:

create FUNCTION dbo.udf_gui_GetContactTypeBitMask(@contactGUID UNIQUEIDENTIFIER)
returns int
AS
BEGIN
declare @tbl table (Typ int default 0, Marked bit default 0)
DECLARE @RowsAdded int
declare @typ int
declare @typbuf int

set @typ = 0
set @typbuf = 0
set @RowsAdded = 0

insert @tbl(Typ)
select TYP_TypeEnum - 1000
FROM Types
INNER JOIN TypeLinks ON TYP_TypeID = CTS_TypeID
INNER JOIN CompanyContacts ON CTS_ParentID = CON_ContactID
where CON_ContactID = @contactGUID

DECLARE _Cursor CURSOR FOR
SELECT Typ from @tbl
OPEN _Cursor
declare @T_typ int
FETCH NEXT FROM _Cursor INTO @T_typ
WHILE @@FETCH_STATUS = 0
BEGIN
set @typ = @typ | @T_typ
FETCH NEXT FROM _Cursor INTO @T_typ
END
CLOSE _Cursor
DEALLOCATE _Cursor

RETURN @typ
END

nr
SQLTeam MVY

12543 Posts

Posted - 2005-11-24 : 19:10:27

think you can replace the cursor with
select @typ = 0
select @typ = @typ | Typ
from @tbl

or even
select @typ = 0
select @typ = @typ | (TYP_TypeEnum - 1000)
FROM Types
INNER JOIN TypeLinks ON TYP_TypeID = CTS_TypeID
INNER JOIN CompanyContacts ON CTS_ParentID = CON_ContactID
where CON_ContactID = @contactGUID

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2005-11-24 : 21:07:48

does it ever get boring to be so clever?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources