Styjun

Why can't i use !(single pattern) in zsh even after i turn on kshglob?

Robots in a spaceship

Simplify the code

How useful would a hydroelectric power plant be in the post-apocalypse world?

Is it normal for professors to hold their graduate students "hostage?"

Why am I getting an electric shock from the water in my hot tub?

Why doesn't SpaceX land boosters in Africa?

Book about a new laser rifle built by a new inventor

What is this fluorinated organic substance?

How far can gerrymandering go?

Does Dhp 256-257 condone judging others?

Aligning arrays within arrays within another array

Old story where computer expert digitally animates The Lord of the Rings

Installed software from source, how to say yum not to install it from package?

Why was Pan Am Flight 103 flying over Lockerbie?

My mom helped me cosign a car and now she wants to take it

Angular: Using ComponentFactoryResolver for dynamic instantiation of the components, rendering inside SVG

What is the function of const specifier in enum types?

Are there advantages in writing by hand over typing out a story?

Can I deep fry food in butter instead of vegetable oil?

Find the closest three-digit hex colour

Is it OK to throw pebbles and stones in streams, waterfalls, ponds, etc.?

Classify 2-dim p-adic galois representations

Sentences with no verb, but an ablative

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

How do I calculate someone's age in C#?What is the difference between String and string in C#?Calling the base constructor in C#How do I enumerate an enum in C#?How to return only the Date from a SQL Server DateTime datatypeHow to check if a column exists in a SQL Server table?How to concatenate text from multiple rows into a single text string in SQL server?How do I get a consistent byte representation of strings in C# without manually specifying an encoding?Get int value from enum in C#How do I UPDATE from a SELECT in SQL Server?

0

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.

c# sql-server

share|improve this question

asked Mar 25 at 17:08

Cristi PriciuCristi Priciu

3477 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Why not have C# just pass the value for @mainParameter and bin @parameterFromC# and @City1, etc?
  
  – Larnu
  Mar 25 at 17:10
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  the problem is actually more complex, this is a part of it
  
  – Cristi Priciu
  Mar 25 at 17:12
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  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
  Mar 25 at 17:13
  
  

  
  
  
  


• 
  
  
  

  
  4
  

  
  
  
  
  
  

  
  
  If the problem is more complex, why not give us the full problem?
  
  – Larnu
  Mar 25 at 17:15
  

  
  
  
  


• 
  
  
  

  
  2
  

  
  
  
  
  
  

  
  
  Sounds like an XY Problem to me (especially based on the fact that you haven't/won't share the "bigger picture" with us).
  
  – Larnu
  Mar 25 at 18:08
  
  

  
  
  
  

 | 
show 2 more comments

0

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.

c# sql-server

share|improve this question

asked Mar 25 at 17:08

Cristi PriciuCristi Priciu

3477 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Why not have C# just pass the value for @mainParameter and bin @parameterFromC# and @City1, etc?
  
  – Larnu
  Mar 25 at 17:10
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  the problem is actually more complex, this is a part of it
  
  – Cristi Priciu
  Mar 25 at 17:12
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  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
  Mar 25 at 17:13
  
  

  
  
  
  


• 
  
  
  

  
  4
  

  
  
  
  
  
  

  
  
  If the problem is more complex, why not give us the full problem?
  
  – Larnu
  Mar 25 at 17:15
  

  
  
  
  


• 
  
  
  

  
  2
  

  
  
  
  
  
  

  
  
  Sounds like an XY Problem to me (especially based on the fact that you haven't/won't share the "bigger picture" with us).
  
  – Larnu
  Mar 25 at 18:08
  
  

  
  
  
  

 | 
show 2 more comments

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.

c# sql-server

share|improve this question

asked Mar 25 at 17:08

Cristi PriciuCristi Priciu

3477 bronze badges

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

c# sql-server

c# sql-server

share|improve this question

asked Mar 25 at 17:08

Cristi PriciuCristi Priciu

3477 bronze badges

share|improve this question

asked Mar 25 at 17:08

Cristi PriciuCristi Priciu

3477 bronze badges

asked Mar 25 at 17:08

Cristi PriciuCristi Priciu

3477 bronze badges

asked Mar 25 at 17:08

Cristi PriciuCristi Priciu

3477 bronze badges

2 Answers
2

active

oldest

votes

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

share|improve this answer

answered Mar 25 at 17:24

Erik PhilipsErik Philips

42.6k66 gold badges9797 silver badges132132 bronze badges

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  You could also just skip city1, city2, city3 and set the value of mainparmeter using this case expression.
  
  – Sean Lange
  Mar 25 at 18:40
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @SeanLange I totally agree. My goal was just to add the logic to the existing code base.
  
  – Erik Philips
  Mar 25 at 19:00
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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.
  
  – Cristi Priciu
  Mar 27 at 9:47
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @CristiPriciu you did something wrong. CASE does exist in MSSQL 2008 and up.
  
  – Franck
  May 6 at 19:26
  

  
  
  
  

add a comment | 

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

share|improve this answer

edited Mar 25 at 17:50

answered Mar 25 at 17:15

JamesJames

1,8731717 silver badges3535 bronze badges

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55343079%2fhow-to-choose-between-sql-parameters-based-on-parameter-sent-from-c-sharp%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

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

share|improve this answer

answered Mar 25 at 17:24

Erik PhilipsErik Philips

42.6k66 gold badges9797 silver badges132132 bronze badges

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  You could also just skip city1, city2, city3 and set the value of mainparmeter using this case expression.
  
  – Sean Lange
  Mar 25 at 18:40
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @SeanLange I totally agree. My goal was just to add the logic to the existing code base.
  
  – Erik Philips
  Mar 25 at 19:00
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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.
  
  – Cristi Priciu
  Mar 27 at 9:47
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @CristiPriciu you did something wrong. CASE does exist in MSSQL 2008 and up.
  
  – Franck
  May 6 at 19:26
  

  
  
  
  

add a comment | 

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

share|improve this answer

answered Mar 25 at 17:24

Erik PhilipsErik Philips

42.6k66 gold badges9797 silver badges132132 bronze badges

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  You could also just skip city1, city2, city3 and set the value of mainparmeter using this case expression.
  
  – Sean Lange
  Mar 25 at 18:40
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @SeanLange I totally agree. My goal was just to add the logic to the existing code base.
  
  – Erik Philips
  Mar 25 at 19:00
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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.
  
  – Cristi Priciu
  Mar 27 at 9:47
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @CristiPriciu you did something wrong. CASE does exist in MSSQL 2008 and up.
  
  – Franck
  May 6 at 19:26
  

  
  
  
  

add a comment | 

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

share|improve this answer

answered Mar 25 at 17:24

Erik PhilipsErik Philips

42.6k66 gold badges9797 silver badges132132 bronze badges

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

share|improve this answer

answered Mar 25 at 17:24

Erik PhilipsErik Philips

42.6k66 gold badges9797 silver badges132132 bronze badges

answered Mar 25 at 17:24

Erik PhilipsErik Philips

42.6k66 gold badges9797 silver badges132132 bronze badges

answered Mar 25 at 17:24

Erik PhilipsErik Philips

42.6k66 gold badges9797 silver badges132132 bronze badges

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

share|improve this answer

edited Mar 25 at 17:50

answered Mar 25 at 17:15

JamesJames

1,8731717 silver badges3535 bronze badges

add a comment | 

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

share|improve this answer

edited Mar 25 at 17:50

answered Mar 25 at 17:15

JamesJames

1,8731717 silver badges3535 bronze badges

add a comment | 

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

share|improve this answer

edited Mar 25 at 17:50

answered Mar 25 at 17:15

JamesJames

1,8731717 silver badges3535 bronze badges

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

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

share|improve this answer

edited Mar 25 at 17:50

answered Mar 25 at 17:15

JamesJames

1,8731717 silver badges3535 bronze badges

answered Mar 25 at 17:15

JamesJames

1,8731717 silver badges3535 bronze badges

answered Mar 25 at 17:15

JamesJames

1,8731717 silver badges3535 bronze badges