Yep. It leads to one of those holy war sort of arguments between dba's and developers. As an architect, I think that having application logic on the database server violates separation of concerns and makes applications far less manageable/maintainable. Dba's would argue performance and security, but they'd largely be wrong. With execution plan caching the old idea of stored procs always being faster isn't true anymore. As far as stored procs being more secure, I'd say maybe sometimes. Most of the security concerns with ad hoc queries versus stored procs can be mitigated easily anyway. Our standard here is procs only for data manipulation (insert, update, delete) and either procs or ad hoc queries for selects. I've only given in to that idea because it is the path of least resistance and I don't feel like fighting every battle that presents itself. If I fought every stupid thing we have to deal with here I'd do nothing but battle every day.
----- Original Message ---- From: Dean H. Saxe <[EMAIL PROTECTED]> To: [email protected] Sent: Tuesday, June 5, 2007 2:52:32 PM Subject: Re: [ACFUG Discuss] OT: SQL help Or he hasn't yet been bit by management that says they need to change RDBMS... migrating stored procs is hell. Migrating CF code is much easier, in general. It also keeps the business logic in one layer of code, not spread across the code and DB. -dhs Dean H. Saxe, CISSP, CEH [EMAIL PROTECTED] "Dissent is the purest form of patriotism." --Thomas Jefferson On Jun 5, 2007, at 2:54 PM, shawn gorrell wrote: 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 ------------------------------------------------------------- ------------------------------------------------------------- Annual Sponsor FigLeaf Software - http://www.figleaf.com 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 http://www.fusionlink.com -------------------------------------------------------------
