如何在stroed过程中使用'in'原因编写查询 [英] how to write the query using 'in' cause in stroed procedures

问题描述

大家好，



i我在sql server中编写查询工作正常，但如何使用程序实现相同的查询。



这样的sql查询，

  SELECT  RECORDID，AVAILABILITY，EMPLOYEMENTSTATUS，CLIENTNAME，DOB，SKILLS，EMAILID，VISA，CREATEDDATE，LOCATION，CITY + ' ， ' + C_STATE  as  LOCATION  FROM  dbo.RECORDDETAILS 其中 CLIENTNAME  （'  newclient' ，'  gdgf'，'  ibm'，'  pp' ）

现在创建过程''sp_cl ient''

 创建  procedure  sp_client （ @ clientname ）
  as  
 开始 
  SELECT  RECORDID，AVAILABILITY，EMPLOYEMENTSTATUS，CLIENTNAME，DOB，SKILLS，EMAILID，VISA，CREATEDDATE，LOCATION，CITY + ' ，' + C_STATE  as  LOCATION  FROM  dbo.RECORDDETAILS 其中​​ CLIENTNAME  in （ @ clientname ）
  end  

如何执行我的程序，我只有一个参数，但如何在单个参数中传递多个客户名称



请帮助我，

谢谢并问候

解决方案

一种方法是使用动态sql。是这样的：

  DECLARE   @ query   varchar （ 2000 ）
  SET   @ query  = '  SELECT RECORDID，AVAILABILITY，EMPLOYEMENTSTATUS，CLIENTNAME，DOB，SKILLS，EMAILID，VISA，CREATEDDATE，LOCATION，CITY +'，'  + C_STATE作为位置来自dbo.RECORDDETAILS，其中CLIENTNAME IN（' +  @ clientname  +  ' ）' 
  EXECUTE （ @query ）

希望这会有所帮助！

 创建  p rocedure  sp_client（ @ clientname ）
  as  
 开始 
 声明  @ Str   as   Nvarchar （Max）
 设置 < span class =code-sdkkeyword> @ Str  = '  SELECT RECORDID，AVAILABILITY，EMPLOYEMENTSTATUS，CLIENTNAME， DOB，SKILLS，EMAILID，VISA，CREATEDDATE，LOCATION，CITY +''，''+ C_STATE作为位置来自dbo.RECORDDETAILS，其中CLIENTNAME位于（' +  @ clientname  + ' ）' 
  Print （ @ Str ）
  Exec （ @Str ）
 结束 

请记住，你的@clientname必须是以下形式。



clientname =''value1''，''value1''，''value3''，''value4''



我的意思是每个值必须有单个cot。

你可以将客户端名称作为逗号分隔字符串传递如下

''AA，BBB，CCC' '



将其拆分为表并用于IN查询。



您可以使用以下表格函数从逗号分隔的字符串中拆分和创建表格

 创建  FUNCTION  [dbo]。[FT_CSVToTable] 
（ @ CSVList   varchar （ 8000 ））
 退货 
 < span class =code-sdkkeyword> @ tIDList   TABLE （ClientName  varchar （ 100 ））
  AS  
  BEGIN  
 
  WITH  CSVCte（StartPos，EndPos） AS  
（ SELECT   1   AS  StartPos，CHARINDEX（' ，'， @ CSVList  + ' ，'） AS  EndPos 
  UNION   ALL  
  SELECT  EndPos +  1   AS  StartP os，CHARINDEX（' ，'， @ CSVList  + ' ，'，EndPos +  1 ） AS  EndPos  FROM  CSVCTE 
  WHERE  CHARINDEX（' ，'， @ CSVList  + ' ，'，EndPos +  1 ）<>  0 ）
 
  INSERT   INTO   @ tIDList  
  SELECT  SUBSTRING（ @CSVList ，StartPos，EndPos-StartPos） FROM  CSVCte 
 
  RETURN  
  END  

然后将您的查询更改为关注

  SELECT  RECORDID，AVAILABILITY，EMPLOYEMENTSTATUS，CLIENTNAME，DOB，SKILLS，EMAILID，VISA，CREATEDDATE， LOCATION，CITY + ' ，' + C_STATE  as  LOCATION 
  FROM  dbo.RECORDDETAILS 其中 CLIENTNAME 在（ SELECT  ClientName  FROM  dbo.FT_CSVToTable（ @ clientname ））

hi all,

i am write the query in sql server working fine, but how to implement the same query using procedures.

my sql query like this,

SELECT RECORDID,AVAILABILITY,EMPLOYEMENTSTATUS,CLIENTNAME,DOB,SKILLS,EMAILID,VISA,CREATEDDATE,LOCATION,CITY+', '+C_STATE as LOCATION FROM dbo.RECORDDETAILS where CLIENTNAME in ('newclient','gdgf','ibm','pp')

now am create the Procedure ''sp_client''

create procedure sp_client(@clientname)
as
begin
SELECT RECORDID,AVAILABILITY,EMPLOYEMENTSTATUS,CLIENTNAME,DOB,SKILLS,EMAILID,VISA,CREATEDDATE,LOCATION,CITY+', '+C_STATE as LOCATION FROM dbo.RECORDDETAILS where CLIENTNAME in (@clientname)
end

how to execute my procedure, i have only one parameter but how to pass multiple client names in single parameter

please help me,
thanks and regards

解决方案

One way is to use dynamic sql. Goes like this:

DECLARE @query varchar(2000) 
SET @query = 'SELECT RECORDID,AVAILABILITY,EMPLOYEMENTSTATUS,CLIENTNAME,DOB,SKILLS,EMAILID,VISA,CREATEDDATE,LOCATION,CITY+', '+C_STATE as LOCATION FROM dbo.RECORDDETAILS where CLIENTNAME IN (' + @clientname + ')'
EXECUTE (@query)

Hope this helps!

create procedure sp_client(@clientname)
as
begin
Declare @Str as Nvarchar(Max)
Set @Str = 'SELECT RECORDID,AVAILABILITY,EMPLOYEMENTSTATUS,CLIENTNAME,DOB,SKILLS,EMAILID,VISA,CREATEDDATE,LOCATION,CITY+'', ''+C_STATE as LOCATION FROM dbo.RECORDDETAILS where CLIENTNAME in (' + @clientname +')'
Print(@Str)
Exec(@Str)
end

Remember that your @clientname must be in below form.

clientname = ''value1'',''value1'',''value3'',''value4''

I mean each value must having single cot.

you can pass the clientname as comma seperated string as follows
''AA,BBB,CCC''

split that as a table and use to the IN query.

You can use following table function to split and create the table from your comma separated string

CREATE FUNCTION [dbo].[FT_CSVToTable] 
(@CSVList varchar(8000) )
RETURNS 
    @tIDList TABLE  (ClientName varchar(100) )
AS
BEGIN

          WITH CSVCte (StartPos, EndPos) AS 
                   ( SELECT 1 AS StartPos, CHARINDEX(',' , @CSVList + ',') AS EndPos
                             UNION ALL
                                      SELECT EndPos + 1 AS StartPos , CHARINDEX(',',@CSVList + ',' , EndPos + 1) AS EndPos FROM CSVCTE 
                                                WHERE CHARINDEX(',', @CSVList + ',', EndPos + 1) <> 0)

          INSERT INTO @tIDList  
                   SELECT SUBSTRING(@CSVList, StartPos,EndPos-StartPos) FROM CSVCte

   RETURN 
END 

Then change your query as follows

SELECT RECORDID,AVAILABILITY,EMPLOYEMENTSTATUS,CLIENTNAME,DOB,SKILLS,EMAILID,VISA,CREATEDDATE,LOCATION,CITY+', '+C_STATE as LOCATION 
FROM dbo.RECORDDETAILS where CLIENTNAME in (SELECT ClientName  FROM dbo.FT_CSVToTable(@clientname))

这篇关于如何在stroed过程中使用'in'原因编写查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！