Multiple Instance of UDF in Proc

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

SQL Server Development (2000)

Multiple Instance of UDF in Proc

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-02-04 : 09:04:40

MDavid writes "SQL 2000 SP3
Win2k SP4

I hope someone can help! I'm trying to call a UDF twice in the same proc (as below). If I use it only once (and hard code values for either IN) all works fine. When I use UDF twice, the Server just chunks and chunks...never an error, never a timeout, never a return.

Any help would be GREATLY appreciated.

Here's Proc....
CREATE PROCEDURE aa_sp_List_Conditions

@BodyRegionsKey int,
@AgesList varchar (15),
@GendersList varchar (15)

AS
SELECT bcs_lst_Conditions.fld_condition_name, bcs_lst_Conditions.fld_condition_key
FROM bcs_tbl_BR_Cond_Symp_XREF INNER JOIN
bcs_lst_Conditions ON
bcs_tbl_BR_Cond_Symp_XREF.fld_condition_key = bcs_lst_Conditions.fld_condition_key
GROUP BY bcs_lst_Conditions.fld_condition_name, bcs_lst_Conditions.fld_condition_key,
bcs_tbl_BR_Cond_Symp_XREF.fld_bodyregion_key, bcs_lst_Conditions.fld_agegroup_key,
bcs_lst_Conditions.fld_gender_key
HAVING (bcs_tbl_BR_Cond_Symp_XREF.fld_bodyregion_key = @BodyRegionsKey )
AND (bcs_lst_Conditions.fld_agegroup_key IN ( Select RetInt from dbo.SplitCSVtoInts(@AgesList) ))
AND (bcs_lst_Conditions.fld_gender_key IN ( Select RetInt from dbo.SplitCSVtoInts(@GendersList) ))

GO

Here's UDF
CREATE FUNCTION dbo.SplitCSVtoInts
(
@IntsList varchar(100)
)
RETURNS
@ParsedList table
(
RetInt int
)
AS
BEGIN
DECLARE @RetIntID varchar(10), @Pos int

SET @IntsList = LTRIM(RTRIM(@IntsList))+ ','
SET @Pos = CHARINDEX(',', @IntsList, 1)

IF REPLACE(@IntsList, ',', '') <> ''
BEGIN
WHILE @Pos > 0
BEGIN
SET @RetIntID = LTRIM(RTRIM(LEFT(@IntsList, @Pos - 1)))
IF @RetIntID <> ''
BEGIN
INSERT INTO @ParsedList (RetInt)
VALUES (CAST(@RetIntID AS int))
END
SET @IntsList = RIGHT(@IntsList, LEN(@IntsList) - @Pos)
SET @Pos = CHARINDEX(',', @IntsList, 1)

END
END
RETURN
END"

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-02-04 : 15:02:25

Another option would be to Parse the CSV values using the following technique:

http://www.sqlteam.com/item.asp?ItemID=2652

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources