How to choose between SQL parameters based on parameter sent from C#

Question

I got stuck trying to find the solution to this one, maybe someone can help?

Based on a int parameter sent from C#, the parameter used in our query should change. Pseudo code:

DECLARE @parameterFromC# int = 1

DECLARE @city1 nvarchar = 'London'
DECLARE @city2 nvarchar = 'Paris'
DECLARE @city3 nvarchar = 'New York'

DECLARE @mainParameter nvarchar

SELECT * from Customers
WHERE City = @mainParameter

When @parameterFromC# is 1, @mainParameter should be = @city1,
when @parameterFromC# is 2, @mainParameter should be = @city2 etc

My solution was to make @parameterFromC# nvarchar, send the number as string and then concatenate it with '@city'. In pseudo code it was something like this:

@mainParameter = '@city' + @parameterFromC#

I was told this is unsafe and to find some other way.

Why not have C# just pass the value for @mainParameter and bin @parameterFromC# and @City1, etc? — Thom A
– Thom A ♦, Commented Mar 25, 2019 at 17:10
To simplify you should create a table with a city name and an Id column. Then when you are supplied with the Id which i believe that's what you are trying to do, You can simply do a join with a where clause to have a cleaner query. — Franck
– Franck, Commented Mar 25, 2019 at 17:13
If the problem is more complex, why not give us the full problem? — Thom A
– Thom A ♦, Commented Mar 25, 2019 at 17:15
Sounds like an XY Problem to me (especially based on the fact that you haven't/won't share the "bigger picture" with us). — Thom A
– Thom A ♦, Commented Mar 25, 2019 at 18:08

Erik Philips · Accepted Answer · 2019-03-25 17:24:22Z

2

Another and maybe more readable way might be to separate the logic for determining the sql parameter value vs the actual usage of the sql parameter. It's highly likely there is no performance gain or lost, the sql compiler is very smart.

DECLARE @parameterFromC int = 1

DECLARE @city1 nvarchar = 'London'
DECLARE @city2 nvarchar = 'Paris'
DECLARE @city3 nvarchar = 'New York'

DECLARE @mainParameter nvarchar

SET @mainParameter = CASE
  WHEN @parameterFromC = 1 THEN @city1
  WHEN @parameterFromC = 2 THEN @city2
  ELSE @city3
END

SELECT * from Customers
WHERE City = @mainParameter

answered Mar 25, 2019 at 17:24

Erik Philips

54.8k11 gold badges133 silver badges158 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Sean Lange Over a year ago

You could also just skip city1, city2, city3 and set the value of mainparmeter using this case expression.

Erik Philips Over a year ago

@SeanLange I totally agree. My goal was just to add the logic to the existing code base.

Cristi Priciu Over a year ago

This example worked on my home computer, thank you. However, the task had to be done on a old 2008 version of SQL server where SQL Switch does not work, so in the end another solution was used.

Franck Over a year ago

@CristiPriciu you did something wrong. CASE does exist in MSSQL 2008 and up.

Brandon · Accepted Answer · 2019-03-25 17:50:19Z

2

I agree with Larnu - I think you should pass in the city via C#, however I don't know your circumstances. You should be able to use a CASE/WHEN.

SET @mainParameter=CASE 
               WHEN @parameterFromC=1 THEN @city1 
               WHEN @parameterFromC=2 THEN @city2
               ELSE @city3
             END

Use this line after you declare the variable and before you do your select on the Customers table.

You don't necessarily need @mainParameter. You could use the CASE/WHEN in the where clause.

SELECT * 
FROM Customers
WHERE City = CASE 
               WHEN @parameterFromC=1 THEN @city1 
               WHEN @parameterFromC=2 THEN @city2
               ELSE @city3
             END

edited Mar 25, 2019 at 17:50

answered Mar 25, 2019 at 17:15

Brandon

3,2563 gold badges30 silver badges51 bronze badges

Collectives™ on Stack Overflow

How to choose between SQL parameters based on parameter sent from C#

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related