Your NULL-friendly concatenations are easily handled using the ISNULL function. I wouldn't change the behavior of the entire database server just for this.
Regarding the variables issue, yes, it certainly does apply. Why don't you give me a call at 770-446-8866 and I'll be happy to help you through this, Jamie. I remember when I was just getting started with stored procedures some years ago before the Internet became popular and documentation was about as well-formed as primordial soup. No one was available to guide me, and it took me a long time to perfect things. I'm more than happy to help. Give me a call. Respectfully, Adam Phillip Churvis Advanced Intensive ColdFusion MX Training http://www.ColdFusionTraining.com E-mail: [EMAIL PROTECTED] Phone: 770-446-8866 ----- Original Message ----- From: "Jamie Jackson" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Thursday, July 25, 2002 2:03 PM Subject: Re: New to SPROCs... > Thanks for the response, I'll check out the wizard. > > This is my first sproc, and I used it out of necessity, as I was doing > some concatenations, and I needed to "SET CONCAT_NULL_YIELDS_NULL > OFF", so concatenations would work, even with nulls. As you suggested, > I'll stick to one sproc. > > However, it's not a dynamic sproc, and it doesn't even use parameters. > It's basically a view (but I my concatenations were failing in a view > -- that's why the simple sproc). Therefore, I don't *think* the > following suggestion applies. (?) > > >Also, declare variables containing each > >CAST value and then reuse that variable > >rather than recalling CAST. > > Last question: Was there a way to do this in a view (null-friendly > concatenations, that is)? > > Thanks > > On Thu, 25 Jul 2002 13:13:47 -0400, in cf-talk you wrote: > > >If you're new to stored procedures, you may find our freebie Stored > >Procedure Wizard useful. You can download a free copy from > >www.ProductivityEnhancement.com. > > > >My suggestion on your stored procedure is to just place all your logic for > >this in a single stored procedure and keep it as simple as possible. Also, > >declare variables containing each CAST value and then reuse that variable > >rather than recalling CAST. > > > >Hope this helps. :) > > > >Respectfully, > > > >Adam Phillip Churvis > >Advanced Intensive ColdFusion MX Training > >http://www.ColdFusionTraining.com > >E-mail: [EMAIL PROTECTED] > >Phone: 770-446-8866 > > > >----- Original Message ----- > >From: "Jamie Jackson" <[EMAIL PROTECTED]> > >To: "CF-Talk" <[EMAIL PROTECTED]> > >Sent: Thursday, July 25, 2002 12:00 PM > >Subject: New to SPROCs... > > > > > >> I've got an MSSQL 2000 sproc that has a lot of repetition that can > >> probably be abstracted. Please let me know if there's a good way to do > >> this: > >> > >> <this is just a snippet> > >> ALTER PROC sp_DAexport > >> > >> AS > >> > >> SELECT > >> ID, > >> cast((round(cast(q7years as decimal) + (cast(q7months as decimal)/12), > >> 1)) as decimal(18,1)) as q7, > >> cast((round(cast(q8years as decimal) + (cast(q8months as decimal)/12), > >> 1)) as decimal(18,1)) as q8 > >> FROM dbo.Respondents > >> > >> I'd like to wrap the math and maybe the aliasing into a sub-sproc > >> funtion, but I don't know if/how I can do it. Here's a _bad_ syntax > >> version of what I'm after: > >> > >> SELECT > >> ID, > >> sp_calcYears('7'), > >> sp_calcYears('8') > >> FROM dbo.Respondents > >> > >> Thanks, > >> Jamie > >> > > > ______________________________________________________________________ This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
