如何为SQL select查询传递multisearch参数..？ [英] How to pass multisearch parameter for SQL select query..?

问题描述

DECLARE @EmpIDtte AS varchar（50）

--SET @EmpIDtte = null

SET @EmpIDtte = '42,40'

打印@EmpIDtte

从MulitSearch_Portal中选择*（@EmpIDtte IS NULL OR（LegalTypeID IN（@EmpIDtte）））



我尝试了什么：



我使用上面的查询，他们使用传递参数@EmpIDtte = null和@EmpIDtte = '42 '....同时我传递@EmpIDtte = '42,40'mulit参数。，他们得到错误plz帮助..

错误：转换varchar值时转换失败'42,40'到数据类型int

解决方案

有几种方法可以做到这一点。由于您已经将参数作为逗号分隔列表（'42,40'）传递，因此您可以使用动态SQL，例如

  DECLARE   @ EmpIDtte   AS   varchar （ 50 ）
 
  SET   @ EmpIDtte  = '  42,40' 
 
  DECLARE   @ sql   nvarchar （ max）
  SET   @ sql  = '  select * from MulitSearch_Portal' 
  IF   @EmpIDtte   IS   NOT   NULL  
  SET   @sql  =  @sql  + '  WHERE LegalTypeID IN（' +  @ EmpIDtte  + ' ）' 
 
  EXEC  sp_sqlexec  @sql  

或者你可以传递表变量中所需的ID，例如

  CREATE   TYPE  [dbo]。[ItemList]  AS   TABLE （
 [ItemID] [ int ]  NOT   NULL  
）
  go  
  DECLARE   @ ids  ItemList 
  INSERT   INTO   @ ids （ItemID） VALUES （ 42 ），（ 40 ）
 
 选择 * 来自 MulitSearch_Portal 其中 LegalTypeID < span class =code-keyword> in （ SELECT  ItemID  FROM   @ ids ）

更多信息 - SQL Server 2008和.NET中的表值参数（C＃） [< a href =https://www.mssqltips.com/sqlservertip/2112/table-value-parameters-in-sql-server-2008-and-net-c/target =_ blanktitle =New Window > ^ ]

 DECLARE @sql nvarchar （max）
 SET @sql ='select * from MulitSearch_Portal'
 IF @EmpIDtte IS NOT NULL 
 SET @sql = @sql +'WHERE LegalTypeID IN（'+ @EmpIDtte +'） '
 IF @CompanyCategoryID IS NOT NULL 
 SET @sql = @sql + CASE WHEN @EmpIDtte IS NULL那么'''''''和END +CompanyCategoryID IN（'+ @ CompanyCategoryID +'）' 

DECLARE @EmpIDtte AS varchar(50)
--SET @EmpIDtte = null
SET @EmpIDtte = '42,40'
print @EmpIDtte
select * from MulitSearch_Portal where (@EmpIDtte IS NULL OR ( LegalTypeID IN (@EmpIDtte)))

What I have tried:

I m using above this query they working with passing parameter "@EmpIDtte = null" and "@EmpIDtte = '42' " ....and the same time i m passing "@EmpIDtte = '42,40' " mulit parameter., they getting error plz help..
Error: Conversion failed when converting the varchar value '42,40' to data type int

解决方案

There are a couple of ways of doing this. As you are already passing in the parameter as a comma-separated list ('42,40') then you can use Dynamic SQL e.g.

DECLARE @EmpIDtte AS varchar(50)
 
SET @EmpIDtte = '42,40'
 
DECLARE @sql nvarchar(max)
SET @sql  = 'select * from MulitSearch_Portal '
IF @EmpIDtte IS NOT NULL 
	SET @sql = @sql + 'WHERE LegalTypeID IN (' + @EmpIDtte + ')'

EXEC sp_sqlexec @sql

Alternatively you could pass the IDs required in a table variable e.g.

CREATE TYPE [dbo].[ItemList] AS TABLE(
 [ItemID] [int] NOT NULL
)
go
DECLARE @ids ItemList 
INSERT INTO @ids (ItemID) VALUES (42), (40)

select * from MulitSearch_Portal where LegalTypeID in (SELECT ItemID FROM @ids)

More information - Table Value Parameters in SQL Server 2008 and .NET (C#)[^]

[EDIT - Option 1 with additional parameters]

DECLARE @sql nvarchar(max)
SET @sql  = 'select * from MulitSearch_Portal'
IF @EmpIDtte IS NOT NULL
	SET @sql = @sql + ' WHERE LegalTypeID IN (' + @EmpIDtte + ')'
IF @CompanyCategoryID IS NOT NULL
	SET @sql = @sql + CASE WHEN @EmpIDtte IS NULL THEN ' WHERE ' ELSE ' and ' END + ' CompanyCategoryID IN (' + @CompanyCategoryID + ')'

这篇关于如何为SQL select查询传递multisearch参数..？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！