Help needed to avoid dynamic SQL.

Question

Help needed to avoid dynamic SQL.

Kari Suresh

Hall of Fame

Points: 3712
More actions
January 9, 2009 at 5:41 am

#204850

Hi All,
I have written below query in which I don't want to use dynamic SQL. So i written like this. I tried to use IF and CASE statements even though i got the errors:
1. Incorrect syntax near the keyword 'LEFT'.
2. Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
INSERT CXwork.dbo.Work_Period_Schedule_Col_Items
(
active_user_key,
active_consultant_key,
col_index,
fiscal_period_key,
fiscal_year,
fiscal_period,
fiscal_period_name,
fiscal_period_start_date,
fiscal_period_end_date,
closed_ind,
period_variance,
fiscytd_variance,
utilized_time,
capacity_adjustments,
initial_capacity,
period_utilization_pct,
available_hours
)
SELECT
@p_iActiveUserKey,
@p_iActiveConsultantKey,
'FY' + CAST(fyp.fiscal_year AS varchar(20)) + '_' + REPLACE(STR(fyp.fiscal_period, 3, 0), ' ', '0'),
fyp.fiscal_period_key,
fyp.fiscal_year,
fyp.fiscal_period,
fyp.name,
fyp.start_date,
fyp.end_date,
CASE
WHEN fyp.close_date <= GETDATE() THEN 1
ELSE 0
END,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
CASE
WHEN @p_iWindow = 1THEN ISNULL(pws.available_hours, 0)
ELSE NULL
END
FROM CXmain.dbo.Fiscal_Year_Period fyp
--CASE
--WHEN @p_iWindow = 1THEN
--LEFT OUTER JOIN CXmain.dbo.Consultants_PWS pws WITH --(READUNCOMMITTED)
--ON fyp.fiscal_period_key = pws.fiscal_period_key
--AND pws.consultant_key = @p_iConsultantKey
--END
IF @p_iWindow = 1
BEGIN
LEFT OUTER JOIN CXmain.dbo.Consultants_PWS pws WITH (READUNCOMMITTED)
ON fyp.fiscal_period_key = pws.fiscal_period_key
AND pws.consultant_key = @p_iConsultantKey
END
WHERE
fyp.room = @sRoom
Please help me out to escape from these errors without using dynamic sql.
KB.
KSB
-----------------------------
Thousands of candles can be lit from a single candle, and the life of the candle will not be shortened. Knowledge and happiness never decreases by being shared.” - Buddha

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

SimonD-978595 Mr or Mrs. 500 Points: 598 More actions · Answer 1

This looks like 2 INSERT statements, one with a LEFT OUTER JOIN required and one without so a simple way around this is to put the IF condition at the top and repeat the INSERT where the LEFT OUTER JOIN is required.

Do you see what I mean?

IF @p_iWindow = 1

INSERT with LEFT OUTER JOIN

ELSE

INSERT withut LEFT OUTER JOIN.

I feel sure there is a more elegant solution, I just don't have time to give it to you 😉

Regards,

Simon

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Use the query with the left outer join always there, and put the conditional into the SELECT list instead. If performance is a problem when the left outer join is included when it's not needed, then separate the query into two, one with and the other without.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Kari Suresh Hall of Fame Points: 3712 More actions · Answer 3

Hi,

Performance is big problem in my sproc. Thats why I just wanted to avoid dynamic sql and now adding 2 INSERT statements.

This looks like 2 INSERT statements, one with a LEFT OUTER JOIN required and one without so a simple way around this is to put the IF condition at the top and repeat the INSERT where the LEFT OUTER JOIN is required.

Thanks for help.

KB

KSB
-----------------------------
Thousands of candles can be lit from a single candle, and the life of the candle will not be shortened. Knowledge and happiness never decreases by being shared.” - Buddha

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 4

dynamic sql isn't always a curse, true it will tend to cause recompiles but sql 2005 and sql 2008 have assisted. Don't beat yourself up trying to avoid a solution which works.

Checking the query plan may help as may useful indexes.

( I'm talking in general terms here rather than specifically about your query which I can't analyse without data e.g. is this millions of rows or maybe 6 ? )

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/