can this be done in a CASE?

[sparkbyte]

If @ID_Number = N'' 
	Begin
		CASE 
			When @ID <> N'' Then (SELECT * FROM [NSC_SQL].[dbo].[fn_IDLookup] (@ID) Order By [ID],[CREATED_ON])
			When @A_Number <> N'' Then (SELECT * FROM [NSC_SQL].[dbo].[fn_IDLookup] (@A_Number) Order By [ID],[CREATED_ON])
			When @Reciept_Number <> N'' Then (SELECT * FROM [NSC_SQL].[dbo].[fn_IDLookup] (@Reciept_Number) Order By [ID],[CREATED_ON])
		Else
			Print N'Please enter a valid Number for the lookup.'
		End
	End;

Thanks

John Fuhrman

 

[markros]

No. CASE EXPRESSION must be part of the select statement, it can not be used as programming flow mechanism.

PluralSight Learning Library

 

[sparkbyte]

Darn!

Thanks

Thanks

John Fuhrman

 

[Qik3Coder]

There's no need for a case in this case (no pun)

SELECT * 
FROM [NSC_SQL].[dbo].[fn_IDLookup] 
[tab]([!]Coalesce(@ID, @A_Number, @Reciept_Number)[/!])
Order By [ID],[CREATED_ON]

Lodlaiden

You've got questions and source code. We want both!

 

[markros]

The simplest solution for your case will be to construct the query dynamically (with parameters) and then execute using sp_executeSQL

See

Do you use ISNULL(...). Don't, it does not perform- short blog by Denis Gobo
Dynamic Search Conditions in T-SQL Version for SQL 2008 (SP1 CU5 and later)- long and comprehensive article by Erland Sommarskog
Catch All Queries- short blog by Gail Shaw
Sunday T-SQL tip: How to select data with unknown parameter set Nice blog by Dmitri Korotkevitch


PluralSight Learning Library

 

[sparkbyte]

Sorry for the late response. I had to go out of town.

I'll do some reading and try the other.

THANKS!!!

Thanks

John Fuhrman