Then which values are best used for comparing queries?

SET STATISTICS IO ON & SET STATISTICS TIME ON?

Using IF statements also seems to generate 1 plan. Is this what you wish achieve with the SP call?

This test is with different tables instead of columns.

---------------------------------------------------

-- Test 2 CASE Multiple TABLES

---------------------------------------------------

CREATE TABLE Products1 (ProductNr Int PRIMARY KEY, Price Money)

CREATE TABLE Products2 (ProductNr Int PRIMARY KEY, Price Money)

CREATE TABLE Products3 (ProductNr Int PRIMARY KEY, Price Money)

CREATE TABLE Products4 (ProductNr Int PRIMARY KEY, Price Money)

CREATE TABLE Products5 (ProductNr Int PRIMARY KEY, Price Money)

GO

--Parameters

DECLARE@InputValChar(1),

@ProdNrInt

SELECT@InputVal= '2',

@ProdNr= 10

-- Variables

DECLARE@PriceMoney

-- Execution plan returns 1 index seek

IF @InputVal = '1'

SET @Price = (SELECT Price FROM Products1 WHERE ProductNr = @ProdNr)

ELSE IF @InputVal = '2'

SET @Price = (SELECT Price FROM Products2 WHERE ProductNr = @ProdNr)

ELSE IF @InputVal = '3'

SET @Price = (SELECT Price FROM Products2 WHERE ProductNr = @ProdNr)

ELSE IF @InputVal = '4'

SET @Price = (SELECT Price FROM Products2 WHERE ProductNr = @ProdNr)

ELSE

SET @Price = (SELECT Price FROM Products2 WHERE ProductNr = @ProdNr)

SELECT @Price

GO

DROP TABLE Products1

DROP TABLE Products2

DROP TABLE Products3

DROP TABLE Products4

DROP TABLE Products5

Cheers,