SQL Query with case statement

Question

0.00/5 (No votes)

See more:

Hi Everyone,

I want to create a store procedure that returns some rows based on the arguments passed.

ex: i've a table which has columns like Mastercode,mastername,mastertype .
This table contains Mastercode is the GUID() column,MasterName is the names of all states and countries and MasterType is the type like if 1 then country and if 2 then state.

Now, I need to create a stored procedure that should return the rows based on the argument passed, like If I pass 2 mastertype then it should select the all state names and IF I pass 1 then should return all country names.

I pass only one parameter, the CASE name like 'Country' then it should get all rows from Country table.

Example:

SQL

CREATE PROCEDURE GetData
  @Input Nvarchar(40)
As
Begin
   If @Input ='Country'
        Select CountryNames From TableName
   Else if @Input='State'
        Select StateNames From TableName
   Else
        Select Columns from Table

End

I want to achieve the same above example with CASE statement...

Posted 28-Jul-13 20:27pm

Rockstar_

Updated 28-Jul-13 22:43pm

v4

Add a Solution

Comments

Rockstar_ 29-Jul-13 3:14am

I want to write a SP like this

case 'country'
then select * from countrytable

case 'state'
then select * from statetable
..
...

Maciej Los 29-Jul-13 6:22am

See my solution ;)

4 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2013-07-28T21:38:00

Rockstar_ wrote:

I want to write a SP like this

SQL

case 'country'
then select * from countrytable

case 'state'
 then select * from statetable

..
...

I would suggest you to search for: how to build dynamic queries[^] ;)

Example:

SQL

CREATE PROCEDURE usp_GetMyData
    @selectList VARCHAR(2000)
    @tableName VARCHAR(50),
    @whereStatement VARCHAR(1000) NULL
AS
BEGIN
    DECLARE @sql VARCHAR(MAX)

    SET @sql = 'SELECT ' + @selectList + 
               ' FROM ' + @tableName 
    IF (NOT @whereStatement IS NULL)
        SET @sql = @sql + ' WHERE ' + @whereStatement

    EXEC (@sql)
END

Dholakiya Ankit · Answer 2 · 2013-07-28T21:22:00

Solution 3

VB

Hello,

Try following code

SQL

case when( @mastertype==2)then
(select state from table)
else
select country from table end

regards.....(:)

Posted 28-Jul-13 21:22pm

Dholakiya Ankit

Updated 28-Jul-13 23:40pm

v3

Comments

Rockstar_ 29-Jul-13 4:37am

The same i want to achieve with case statements...

Dholakiya Ankit 29-Jul-13 5:39am

ok see updated

Prasad Khandekar · Answer 3 · 2013-07-28T21:04:00

Solution 2

Hello,

Try following code.

SQL

CREATE PROCEDURE GetCountryOrStates(@pmstType int) 
AS
   SELECT mastername 
   FROM SomeTable
   WHERE masterType = @pmstType

Please refer to this[^] link for a detailed example.

Regards,

Posted 28-Jul-13 21:04pm

Prasad Khandekar

Raja Sekhar S · Answer 4 · 2013-07-28T20:35:00

Solution 1

SQL

USE [DataBase_Name]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE GetData
	@MasterType Int
AS
BEGIN
   SET NOCOUNT ON;
    -- Statements for procedure here
   Select MasterName From TableName Where MasterType=@MasterType 
	
END
GO

If you want to pass more than one parameter u can pass them.. if you want them to be optional parameters u can use like...

SQL

Select ColumnNames From TableName Where MasterType=@MasterType and Country=ISNULL(@Country,Country) -- u have to Declare @Country Where we declared @MasterType

If you pass a String like 'Country' or 'State' then you can use

SQL

CREATE PROCEDURE GetData
  @Input Nvarchar(40)
As
Begin
   If @Input ='Country'
        Select CountryNames From TableName
   Else if @Input='State'
        Select StateNames From TableName 
   Else
        Select Columns from Table

End

Check this Link:
http://msdn.microsoft.com/en-us/library/ms187926.aspx[^]

Posted 28-Jul-13 20:35pm

Raja Sekhar S

Updated 28-Jul-13 21:18pm

v11

Comments

Rockstar_ 29-Jul-13 2:38am

Hi Raj Sekhar u r correct..

Rockstar_ 29-Jul-13 2:39am

suppose If i want to get the data from multiple tables then?

Raja Sekhar S 29-Jul-13 2:41am

U can use the select Statement in the Procedure....

Rockstar_ 29-Jul-13 2:42am

I mean, i'm working on a asp .net c# project, I want to create a master stored procedure for getting data for all controls like dropdownlists,listbox etc . So that in future if any bugs then it will be easy to locate the errors in a single stored procedures instead of creating store procedures for every master type.

Rockstar_ 29-Jul-13 2:44am

if my code when i call store procedure i pass strings like 'Country' or 'State' then based on the string passed it should match the CASE in stored procedure then execute the select statement .

Rockstar_ 29-Jul-13 2:51am

I want to write multiple select querries, bcoz i have some querries which are joined with other tables.

Raja Sekhar S 29-Jul-13 2:56am

In that case u can write if statement or case statement...
First u have to be clear about how many parameters you have to pass to the procedure.. then based on that we can write if or case statements...
Tell me ur requirement exactly.. How many parameters you want to pass and what are optional parameters and what is the op required... use Improve Question widget...

Raja Sekhar S 29-Jul-13 3:03am

Check the Updated Solution...

Raja Sekhar S 29-Jul-13 3:20am

Check the Updated Solution..

Rockstar_ 29-Jul-13 4:38am

Hi Raja Sekhar, CREATE PROCEDURE GetData
@Input Nvarchar(40)
As
Begin
If @Input ='Country'
Select CountryNames From TableName
Else if @Input='State'
Select StateNames From TableName
Else
Select Columns from Table

End

The same i want to achieve with CASE statements....

Rockstar_ 29-Jul-13 4:40am

I have around 20 conditions like above if's, and in future how many we will add we don't know, so writing too many if's will decrease the performance, so that i want to write the same using CASE.

Raja Sekhar S 29-Jul-13 7:07am

in that case i Suggest u use Maciej Los Solution...

Raja Sekhar S 29-Jul-13 7:11am

Do you Know what is the Problem when u use Case Statement in Select Query... U have to Hard-Code the Column Name... so i suggest u use if statement or a Dynamic Query...

SQL Query with case statement

4 solutions

Solution 4

Solution 3

Solution 2

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us