Dynamic query? 2

[jararaca]

Hi, I have a stored procedure that resembles the following structure:

SELECT projectionA

FROM TableA

JOIN

(SELECT projectionB
FROM TableB

UNION

SELECT projectionC
FROM TableC)

RIGHT JOIN TableD

The issue is this: On some occasions TableC will not be necessary. My question is this: Is there a way to deal with this so that I don't have to duplicate most of this procedure or create any new parameters?

Thank you.

 

[gmmastros]

TableC is used in a UNION, so if you don't want to get the records from TableC, you could 'trick it' like this

Declare @IncludeTableC Integer
Set @IncludeTableC = 1

(SELECT projectionB
FROM TableB

UNION

SELECT projectionC
FROM TableC
Where 1 = @IncludeTableC)

When you want to include TableC Set @IncludeTableC = 1 If you want to exclude records from TableC, set @IncludeTableC to any value that is NOT 1


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[jararaca]

Cool. Very cool! Thanks. But it looks like I would have to use a parameter in this case. I wanted to avoid this because there are apps and other procedures that are using this stored procedure already and I want to avoid having to go into those things to add a parameter... I wonder if there is a way of creating some kind of "optional" parameter that could be used by new apps but ignored by old ones... thanks, again, though. This is a neat trick that I can actually use in a few other places, too!

 

[gmmastros]

Optional parameters? Yep. Like this...

Alter  Procedure TestParameters
	@RequiredParameter Int,
	@OptionalParameter Int = NULL
AS
SET NOCOUNT ON

Select	@RequiredParameter, @OptionalParameter

go

Exec TestParameters [green]-- This one errors[/green]
Exec TestParameters 1
Exec TestParameters 1,2

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[jbenson001]

Cool trick George.

 

[gmmastros]

I suspect that in your case, you would want the Optional Parameter to equal 1 so that it supports legacy stuff, so you would probably want to replace = NULL with [!]= 1[/!]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[jararaca]

I'm beginning to understand. The equal sign after the parameter declaration is a default value that is overridden if a parameter is passed in?

 

[gmmastros]

Yes.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[jararaca]

Thank you for all your help!

 

[gmmastros]

No problem.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom