Thanks that did it! Now to understand what was going on.:-D

Shawn,

He may mean how to pass multiple values through a single parameter via a split list function. Just a guess though as the wording is confusing.

Great article!

I'm still new but from what I understand, if it is relational there is never a guaranteed order. There however is a illusion of order based on the optimizer choosing...

Okay, I removed the CTE that was being used for Immune denom and set it to a regular query update and now it ran in 24 seconds.. So confused.:w00t:

Here is the Immunization function. This is one of the spots it seems to hang up on when I'm watching the execution plan update.

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE FUNCTION [dbo].[cusUDSImmunOBSData_2014](@OBSCategory...

Lynn, it is. This is my redo. It's still better than the original which would run for hours. I'll see what other information I can provide.

Thanks Luis QUOTENAME() put me on the right path. Was able to get the script complete quickly. Thanks:-)

My apologies Luis, I have already had a beer or two for the night. The Inserts come from scripting the table similar to the image that you showed me as...

I am hoping you much smarter gentlemen than myself know better ways of doing this. Other wise I have a long weekend ahaead...:crazy:

right after that first line I showed in the code. That's my problem Idont want to go through that whole thing putting in ' ' ' ' ' to get...

Thanks Luis for your reply,

this is what that gives me IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[cusUDSImmunizationCategoryProcedures_2014]') AND type in (N'U'))

but I need to check if...

I just did that and I am currently running a 50-55 second query vs 8+ minutes. I just don't understand why such a small thing made such a huge difference....

Oddly when I remove the OR (r.Trimester >0) it runs in 50-55 seconds not the 8+ minutes.. Now very, very confused.

When I hover over the object name in SSMS it tells me table or view in the query window. Not sure if I have something extra turned on ?

Unfortunately there is no ability to change visibility of a parameter dynamically. However, you can populate the choices in the Parameter selection dynamically via "Cascading Parameters".

You could do something like...