Can''t get UDF to replace Cursor
-
I got a script off this site. Here is a simplified version
-- Simplified script from "cadebryant"
CREATE TABLE dbo.Contacts( ContactID integer,
FirstName varchar(10),
LastName varchar(10))
INSERT INTO Contacts VALUES( 1, 'John', 'Smith')
CREATE TABLE dbo.PhoneNumbers( ContactID integer,
PhoneNumber varchar(50))
INSERT INTO PhoneNumbers
VALUES( 1, '111-222-3333')
INSERT INTO PhoneNumbers
VALUES( 1, '444-555-6666')
INSERT INTO PhoneNumbers
VALUES( 1, '777-888-9999')
GO
CREATE FUNCTION dbo.GetPhones( @ContactID integer) -- Code to create UDF for getting phone numbers
RETURNS varchar(50)
AS
BEGIN
DECLARE @Phones varchar(50)
SET @Phones = ''
SELECT @Phones = CASE @Phones
WHEN ''
THEN ISNULL( PhoneNumber, '')
ELSE @Phones + ', ' + ISNULL( PhoneNumber, '')
END
FROM PhoneNumbers
WHERE ContactID = @ContactID
RETURN( @Phones)
END
GO
SELECT FirstName, LastName, dbo.GetPhones( ContactID) -- Code to execute function from SELECT statement
FROM Contacts
GO
DROP TABLE dbo.Contacts
DROP TABLE dbo.PhoneNumbers
GO
DROP FUNCTION dbo.GetPhones
GO
----------------------------------------------------------------------------------
I have tried to use this method for one of our Cursors. While I cannot give you data, the table names should provide enough information to understand what I am trying to do.
Basically, I get no results back when I try and run this UDF. Can anyone see where I am going wrong?
IF EXISTS( SELECT * FROM dbo.sysobjects WHERE id = object_id( N'dbo.GetInternationalCodes')
AND xtype IN( N'FN', N'IF', N'TF'))
DROP FUNCTION dbo.GetInternationalCodes
GO
CREATE FUNCTION dbo.GetInternationalCodes( @ID integer)
RETURNS varchar(250)
AS
BEGIN
DECLARE @IntlCodes varchar(250)
SET @IntlCodes = ''
SELECT @IntlCodes = CASE @IntlCodes
WHEN ''
THEN ISNULL( @IntlCodes, '')
ELSE @IntlCodes + CONVERT( varchar, ISNULL( tb_intl.country_code, '')) + ' ' +
ISNULL( Countries.country_name, '') + ' ' +
CONVERT( varchar, ISNULL( tb_intl.pct, '')) + ' ' +
CONVERT( varchar, ISNULL( tb_intl.intl_marker, ''))
END
FROM tb_intl
LEFT JOIN Countries ON( tb_intl.country_code = Countries.NIH_State_Country_Code)
WHERE tb_intl.award_id = @ID
RETURN( @IntlCodes)
END
SELECT F.Project, dbo.GetInternationalCodes( F.id) AS InternationalCodes
FROM #Final F
I wasn't born stupid - I had to study.
-
Your treatment of null and converts of numerics appears to be reversed. You need first to convert from numeric to varchar (which will convert null numerics to null varchars) and then replace nulls with empty strings.
Instead of
CONVERT( varchar, ISNULL( tb_intl.pct, ''))
Try
COALESCE( CONVERT( varchar, tb_intl.pct) , '')
P.S.
The COALESCE and ISNULL functions are equivalent but COALESCE is ANSI standard.
SQL = Scarcely Qualifies as a Language
-
Farrell,
you are probably missing one IsNull function on the concatenation:
CREATE FUNCTION dbo.GetInternationalCodes( @ID integer)
RETURNS varchar(250)
AS
BEGIN
DECLARE @IntlCodes varchar(250)
SET @IntlCodes = ''
SELECT @IntlCodes = CASE @IntlCodes
WHEN ''
THEN ISNULL( @IntlCodes, '')
ELSE ISNULL( @IntlCodes, '') + CONVERT( varchar, ISNULL( tb_intl.country_code, '')) + ' ' +
ISNULL( Countries.country_name, '') + ' ' +
CONVERT( varchar, ISNULL( tb_intl.pct, '')) + ' ' +
CONVERT( varchar, ISNULL( tb_intl.intl_marker, ''))
END
FROM tb_intl
LEFT JOIN Countries ON( tb_intl.country_code = Countries.NIH_State_Country_Code)
WHERE tb_intl.award_id = @ID
RETURN( @IntlCodes)
END
I am sure there is a more compact way to write that but I am leaving the office now. (i'll leave that to you
)Cheers,
* Noel -
Still not returning any values:
IF EXISTS( SELECT * FROM dbo.sysobjects WHERE id = object_id( N'dbo.GetInternationalCodes') AND xtype IN( N'FN', N'IF', N'TF'))
DROP FUNCTION dbo.GetInternationalCodes
GO
CREATE FUNCTION dbo.GetInternationalCodes( @AwardID integer)
RETURNS varchar(250)
AS
BEGIN
DECLARE @IntlCodes varchar(250)
SET @IntlCodes = ''
SELECT @IntlCodes = CASE @IntlCodes
WHEN ''
THEN COALESCE( @IntlCodes, '')
ELSE COALESCE( @IntlCodes, '') +
COALESCE( CONVERT( varchar, tb_intl.country_code), '') + ' ' +
COALESCE( Countries.country_name, '') + ' ' +
COALESCE( CONVERT( varchar, tb_intl.pct), '') + ' ' +
COALESCE( CONVERT( varchar, tb_intl.intl_marker), '')
END
FROM tb_intl
LEFT JOIN Countries ON( tb_intl.country_code = Countries.NIH_State_Country_Code)
WHERE tb_intl.award_id = @AwardID
RETURN( @IntlCodes)
END
------------------------------------------------------------------------------------
This is the original CURSOR. Yes, it was based upon the International ID, but it should work on the #Final.ID as well. The #Final table contains all the records for a report.
SELECT i.[ID], i.id_intl, i.country_code, C.Country_Name, i.pct, i.intl_marker
INTO #intl1
FROM tb_intl i
LEFT JOIN Countries C ON( i.country_code = C.NIH_State_Country_Code)
CREATE CLUSTERED INDEX [CIX_id_intl] ON [#intl1]( [id_intl] ASC)
CREATE INDEX [IX_ID] ON [#intl1]( [ID])
DECLARE int_csr CURSOR FAST_FORWARD FOR
SELECT i.id_intl
FROM #intl1 i
INNER JOIN #final f ON( i.[ID] = f.[ID])
GROUP BY i.id_intl
ORDER BY i.id_intl
OPEN int_csr
FETCH int_csr INTO @id_intl
WHILE @@fetch_status = 0
BEGIN
UPDATE #final SET
intl_codes1 = ISNULL( f.intl_codes1, '') + ' ' +
CONVERT( varchar, ISNULL( i.country_code, '')) +
' ' + ISNULL( i.country_name, '') + ' ' +
CONVERT( varchar, ISNULL( i.pct, '')) + ' ' +
CONVERT( varchar, ISNULL( i.intl_marker, ''))
FROM #final f
INNER JOIN #intl1 i ON( f.[ID] = i.[ID]
AND i.id_intl = @id_intl)
FETCH int_csr INTO @id_intl
END
CLOSE int_csr
DEALLOCATE int_csr
I wasn't born stupid - I had to study.
-
When I test a portion of the function:
SELECT CONVERT( varchar, COALESCE( tb_intl.country_code, '')) + ' ' +
COALESCE( Countries.country_name, '') + ' ' +
CONVERT( varchar, COALESCE( tb_intl.pct, '')) + ' ' +
CONVERT( varchar, COALESCE( tb_intl.intl_marker, ''))
FROM tb_intl
LEFT JOIN Countries ON( tb_intl.country_code = Countries.NIH_State_Country_Code)
WHERE [ID] = 497923
I get 3 records returned:
-----------------------------------
795 TANZANIA U REP 25 4
834 UNITED KINGDOM 3 4
890 ZAMBIA 72 5
Of course, I need these as a single string. I am dumbfounded as to why this does not work as it follows the "cadebryant" script closely.
Hope this helps....
I wasn't born stupid - I had to study.
-
The End for the CASE is in the wrong place. Here is a tested solution that results in
1 1 Country One 10 11 , 2 Country Two 20 22 , 3 Country Three 30 33 , 1 Country One 10 11 , 2 Country Two 20 22 , 3 Country Three 30 33
create table tb_intl
(award_idinteger
,country_code integer
,Pctinteger
,intl_marker integer
)
go
create table Countries
(NIH_State_Country_Codeinteger
,country_namevarchar(255)
)
go
insert into tb_intl
(award_id,country_code ,Pct,intl_marker )
select 1, 01, 10, 11union all
select 1, 02, 20, 22union all
select 1, 03, 30, 33union all
select 1, 01, 10, 11union all
select 1, 02, 20, 22union all
select 1, 03, 30, 33
go
insert into Countries
(NIH_State_Country_Code,country_name)
select 1 , 'Country One' union all
select 2 , 'Country Two' union all
select 3 , 'Country Three'
CREATE FUNCTION dbo.GetInternationalCodes( @AwardID integer)
RETURNS varchar(250)
AS
BEGIN
DECLARE @IntlCodes varchar(250)
SET @IntlCodes = ''
SELECT @IntlCodes
-- When NOT the first row, append a comma to variable
=CASE @IntlCodes WHEN '' THEN ''ELSE @IntlCodes + ' , 'END
--Add values to variable
+ COALESCE( CAST( tb_intl.country_code as varchar(255) ), '') + ' '
+ COALESCE( Countries.country_name, '') + ' '
+ COALESCE( CAST( tb_intl.pct as varchar(255) ), '') + ' '
+ COALESCE( CAST( tb_intl.intl_marker as varchar(255) ), '')
FROM tb_intl
LEFT JOIN Countries
ON tb_intl.country_code = Countries.NIH_State_Country_Code
WHERE tb_intl.award_id = @AwardID
RETURN( @IntlCodes)
END
go
select 1 , dbo.GetInternationalCodes(1)
go
SQL = Scarcely Qualifies as a Language
-
I believe that is it, Carl.
Thank you!!
I wasn't born stupid - I had to study.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply