带有case语句的SQL Query [英] SQL Query with case statement

问题描述

大家好，



我想创建一个基于传递的参数返回一些行的存储过程。



ex：我的表格中包含Mastercode，mastername，mastertype等列。

此表包含Mastercode是GUID（）列，MasterName是所有州和国家/地区的名称和MasterType类似，如果1然后是国家，如果是2然后状态。



现在，我需要创建一个应该根据参数返回行的存储过程通过，如果我通过2 mastertype然后它应该选择所有州名称，如果我通过1然后应该返回所有国家名称。



我只传递一个参数， CASE名称如'Country'那么它应该从Country表中获取所有行。



示例：

  CREATE   PROCEDURE  GetData 
 输入@   Nvarchar （ 40 ）
 正如 
 开始 
 如果  @输入 = ' 国家' 
 选择 CountryNames 从 TableName 
 否则  if  @ Input = '  State' 
 < span class =code-keyword>选择 StateNames 从 TableName 
 其他 
 从 表中选择列 
 
 结束 

我想用CASE语句实现相同的上述例子...

解决方案

  USE  [DataBase_Name] 
  GO  
  SET   ANSI_NULLS   ON  
  GO  
  SET   QUOTED_IDENTIFIER   ON  
  GO  
 
  CREATE   PROCEDURE  GetData 
  @ MasterType   Int  
 < span class =code-keyword> AS  
  BEGIN  
  SET   NOCOUNT   ON ; 
   -   此处的程序声明 
 选择 MasterName 来自 TableName 其中​​ MasterType = @ MasterType 
 
  END  
  GO  

如果你想传递多个参数，你可以传递它们..如果你想让它们成为可选参数，你可以使用...

 选择 ColumnNames 从 TableName 其中 MasterType = @ MasterType 和 Country = ISNULL（ @ Country ，Country）  -   你必须声明@Country我们声明的地方@MasterType  

如果你传递像'国家这样的字符串或状态然后你可以使用

 创建 程序 GetData 
  @输入  Nvarchar （ 40 ）
 作为 
 开始 
 < span class =code-keyword>如果  @ Input  = '  Country' 
 选择 CountryNames 来自 TableName 
 其他 如果 @ Input = '  State' 
 选择 StateNames 来自 TableName 
 其他 
 选择列来自 表 
 
 结束 

查看此链接：

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

您好，



请尝试以下代码。

  CREATE   PROCEDURE  GetCountryOrStates（ @ pmstType   int ）
  AS  
  SELECT  mastername 
  FROM  SomeTable 
  WHERE  masterType = <跨班=code-sdkkeyword> @ pmstType  

请参阅 this [ ^ ]链接以获取详细示例。



问候，

Rockstar_写道：

我想写一个像这样的SP

  case  '  country' 
   选择 * 来自 countrytable 
 
  case  '  state' 
 然后 选择 * 来自 statetable 

..

...



我建议您搜索：如何构建动态查询 [ ^ ];）



示例：

  CREATE  程序 usp_GetMyData 
  @ selectList   VARCHAR （ 2000 ）
  @ tableName   VARCHAR （ 50 ），
  @ whereStatement   VARCHAR （< span class =code-digit> 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  

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:

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...

解决方案

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...

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

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[^]

Hello,

Try following code.

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

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

Regards,

Rockstar_ wrote:

I want to write a SP like this

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:

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

这篇关于带有case语句的SQL Query的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！