Click here to Skip to main content
15,867,686 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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
Updated 28-Jul-13 22:43pm
v4
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 ;)

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[^]
 
Share this answer
 
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 .
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,
 
Share this answer
 
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
 
Share this answer
 
v2
Comments
Raja Sekhar S 29-Jul-13 7:08am    
Nice one... +5!
Maciej Los 29-Jul-13 7:45am    
Thank you, Raja ;)
Raja Sekhar S 29-Jul-13 7:51am    
You are Welcome...
VB
Hello,

Try following code


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

regards.....(:)
 
Share this answer
 
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

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900