Why is that? You don't still believe that stored procedures always
perform better do ya? ;)
----- Original Message ----
From: Teddy R Payne <[EMAIL PROTECTED]>
To: [email protected]
Sent: Tuesday, June 5, 2007 2:09:40 PM
Subject: Re: [ACFUG Discuss] OT: SQL help
You use it in an adhoc query in query analyzer or SSMA. I have not
tried it as an ad hoc query in CF as I myself tend not to use
cfquery wherever I can.
Teddy
----- Original Message -----
From: Paul Morton
To: [email protected]
Sent: Tuesday, June 05, 2007 1:58 PM
Subject: RE: [ACFUG Discuss] OT: SQL help
Without creating a SP, can I use this in a select statement?
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Teddy R
Payne
Sent: Tuesday, June 05, 2007 11:45 AM
To: [email protected]
Subject: Re: [ACFUG Discuss] OT: SQL help
Paul,
There is already an open source function in SQL Server to do this
for you:
http://www.4guysfromrolla.com/webtech/031004-1.shtml
They explain how to put it together.
Here is the function creation for how I put it together from their
explanation:
if exists (select * from dbo.sysobjects where id = object_id
(N'[dbo].[Split]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[Split]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE FUNCTION dbo.Split
(
@List nvarchar(4000),
@SplitOn nvarchar(5)
)
RETURNS @RtnValue table
(
Id int identity(1,1),
Value nvarchar(250)
)
AS
BEGIN
While (Charindex(@SplitOn,@List)>0)
Begin
Insert Into
@RtnValue
(
value
)
Select
Value = ltrim(rtrim(Substring(@List,1,Charindex
(@SplitOn,@List)-1)))
Set @List = Substring(@List,Charindex(@SplitOn,@List)+len
(@SplitOn),len(@List))
End
Insert Into
@RtnValue
(
Value
)
Select
Value = ltrim(rtrim(@List))
Return
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
It is implemented as such:
CREATE PROCEDURE getEmployeesByID(@employeeList nvarchar(1000))
AS
Select
employeeId, -- integer (int)
FirstName,
LastName
from #myTable
Where
employeeId IN (Select convert(int,Value) from dbo.Split
(@employeeList,','))
Cheers,
Teddy
----- Original Message -----
From: Paul Morton
To: [email protected]
Sent: Tuesday, June 05, 2007 1:36 PM
Subject: [ACFUG Discuss] OT: SQL help
I apologize for the OT post, but I'm getting frustrated.
I am attempting to use a UDF to split a varchar (comma delimited
list of ints) in an IN clause in a SQL statement, and I'm not able
to get the SQL statement to pass a Table field to the UDF.
I'm hoping someone sees a glaring error, or can tell me I can't do
this.
MS SQL 2000
________________________________________________________________
CREATE FUNCTION dbo.Split
(
@List nvarchar(2000),
@SplitOn nvarchar(5)
)
RETURNS @RtnValue table
(
Id int identity(1,1),
Value nvarchar(100)
)
AS
BEGIN
While (Charindex(@SplitOn,@List)>0)
Begin
Insert Into @RtnValue (value)
Select
Value = ltrim(rtrim(Substring(@List,1,Charindex
(@SplitOn,@List)-1)))
Set @List = Substring(@List,Charindex(@SplitOn,@List)+len
(@SplitOn),len(@List))
End
Insert Into @RtnValue (Value)
Select Value = ltrim(rtrim(@List))
Return
END
______________________________________________________________________
__________
SQL Statement
SELECT *
FROM dbo.ORDER_ITEMS oi LEFT OUTER JOIN
dbo.AP_translation tr ON tr.option_id IN
(select convert(int,Value) from dbo.Split(oi.OptionIDs, ','))
WHERE (oi.Order_ID = 12387)
______________________________________________________________________
_______
Error received:
Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near '.'.
OptionIDs is a varchar(255) field with a comma delimited list of
integers.
It doesn't like the oi.OptionIDs. If I put in a list like '0,1,2'
in it's place, the query works.
Can I not pass a field into a UDF?
Is there another way to skin this cat?
Paul
-------------------------------------------------------------
Annual Sponsor - Figleaf Software
To unsubscribe from this list, manage your profile @
http://www.acfug.org?fa=login.edituserform
For more info, see http://www.acfug.org/mailinglists
Archive @ http://www.mail-archive.com/discussion%40acfug.org/
List hosted by FusionLink
-------------------------------------------------------------
-------------------------------------------------------------
Annual Sponsor - Figleaf Software
To unsubscribe from this list, manage your profile @
http://www.acfug.org?fa=login.edituserform
For more info, see http://www.acfug.org/mailinglists
Archive @ http://www.mail-archive.com/discussion%40acfug.org/
List hosted by FusionLink
-------------------------------------------------------------
-------------------------------------------------------------
Annual Sponsor - Figleaf Software
To unsubscribe from this list, manage your profile @
http://www.acfug.org?fa=login.edituserform
For more info, see http://www.acfug.org/mailinglists
Archive @ http://www.mail-archive.com/discussion%40acfug.org/
List hosted by FusionLink
-------------------------------------------------------------
-------------------------------------------------------------
Annual Sponsor - Figleaf Software
To unsubscribe from this list, manage your profile @
http://www.acfug.org?fa=login.edituserform
For more info, see http://www.acfug.org/mailinglists
Archive @ http://www.mail-archive.com/discussion%40acfug.org/
List hosted by FusionLink
-------------------------------------------------------------
-------------------------------------------------------------
Annual Sponsor - Figleaf Software
To unsubscribe from this list, manage your profile @
http://www.acfug.org?fa=login.edituserform
For more info, see http://www.acfug.org/mailinglists
Archive @ http://www.mail-archive.com/discussion%40acfug.org/
List hosted by FusionLink
-------------------------------------------------------------
