本文介绍了TSQL查询:将参数传递给sp for in子句的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧! 问题描述 29岁程序员,3月因学历无情被辞! 我有一个sp我需要传递一个使用IN子句的参数 以下查询工作正常 选择名称来自 tbl 其中 Id = ' 001' 和 locationId IN ( 1 , 2 , 3 )) 但是这个查询给出错误 声明 @ var NVARCHAR ( 100 )= ' 1,2,3'; 声明 @ whereClause NVARCHAR ( 100 ); SET @ whereClause = ' 其中Id =' 001 ' 和locationId IN(' + @ Var + ' )'; 从 tbl + @ whereClause 名称 / span> 这是我得到的错误 转换nvarchar值IN时转换失败(1 ,2,3)'到数据类型int。 请帮我解决我错误的地方 预付款感谢您的帮助 我尝试了什么: 我尝试的代码 声明 @ var NVARCHAR ( 100 )= ' 1,2,3'; 从 tbl中选择名称 其中 Id = ' 001' 和 locationId IN ( @ var ) 解决方案 如果要以这种方式尝试,则需要使用动态SQL。 查看本CP帖子上发布的解决方案和链接... 如何将SQL参数中的字符串数组传递给SQL中的IN子句 [ ^ ]选择解决方案 你不能以你试过的方式在sql和动态sql之间混合搭配。 更改所需内容声明@var NVARCHAR(100)='1,2,3'; 声明@whereClause NVARCHAR(100); SET @whereClause ='其中Id =''001''和locationId IN('+ @Var +')'; 声明@sql nvarchar(max)='从tbl中选择名称'+ @whereClause print @sql - 注意这打印选择来自tbl的名称,其中Id ='001',locationId IN(1,2,3) EXEC sp_executesql @sql 有更好的例子在我上面给出的链接 这样做的简单方法是在语句中使用SQL SPLIT函数。此功能随SQL Server 2016一起添加,以前的版本需要安装用户定义函数。如果是这种情况,Sql Server Central等网站上有很多可用的内容 好​​读:数字或理货表:它是什么以及它如何取代循环 [ ^ ] 此声明使用2016+内置功能: DECLARE @ ID NVARCHAR ( 3 )= ' 001' DECLARE @ LocationIDs NVARCHAR ( 100 )= ' 1 ,2,3' SELECT 名称 FROM 表 WHERE Id = @ ID AND LocationID IN ( SELECT value FROM STRING_SPLIT( @ LocationIDs ,' ,')) 第二个选项稍微复杂一些,需要SQL用户定义类型以及ADO调用中的不同语法。优点是你可以传入更复杂的类型,如DataTables,DataReaders或iEnumerables SQL部分 CREATE TYPE dbo.UserTableType_INTs AS TABLE (IntValue INT NOT NULL ) GO CREATE PROCEDURE DBO.usp_MyProcedure( @ ID NVARCHAR ( 100 ), @ LocationIDs dbo.UserTableType_INTs READONLY ) AS BEGIN SELECT 名称 FROM 表 WHERE Id = @ ID AND LocationID IN @ LocationIDs END GO 并通过ADO在C中调用它# // 示例数据表,我通常不会这样做,我的语法可能会关闭 DataTable LocationIDs = new DataTable(); LocationIDs.Clear(); LocationIDs.Columns.Add( IntValue); LocationIDs.Rows.Add( 1 ); LocationIDs.Rows.Add( 2 ); LocationIDs.Rows.Add( 3 ); SqlCommand cmd = new SqlCommand(sqlInsert,connection); cmd.CommandType = CommandType.StoredProcedure; // 标准输入参数快捷方式 cmd.Parameters。 AddWithValue( @ ID, 001); // 使用SQL用户类型时添加不同的参数 SqlParameter tvpParam = cmd.Parameters.AddWithValue( @ LocationIDs,LocationIDs); tvpParam.SqlDbType = SqlDbType.Structured; tvpParam.TypeName = dbo.UserTableType_INTs; 你可以在IN语句中使用@var,除非它是动态SQL。 声明@var NVARCHAR( 100)='1,2,3'; 声明@sql varchar(300)='SELECT * FROM TBL WHERE ID IN('+ @var +');'; exec(@sql) Orelse你可以使用string的split函数拆分@var并直接使用。 I have a sp where i need to pass a parameter which is using IN Clausebelow query is working fineselect name from tbl where Id='001' and locationId IN (1,2,3))but this query is giving errorDeclare @var NVARCHAR(100) = '1,2,3';Declare @whereClause NVARCHAR(100);SET @whereClause = 'where Id='001' and locationId IN ('+@Var+')';Select name from tbl + @whereClauseThis is error i am gettingConversion failed when converting the nvarchar value IN (1,2,3) ' to data type int.please help me out that where i made mistakeadvance thanks for helpWhat I have tried:the code which i tried toDeclare @var NVARCHAR(100) = '1,2,3';Select name from tbl where Id='001' and locationId IN (@var) 解决方案 If you are going to try it that way you need to use dynamic SQL.Have a look at the solutions and links posted on this CP post ... How to pass string array in SQL parameter to IN clause in SQL[^] for a selection of solutions[EDIT] You can't mix and match between sql and dynamic sql in the way you have tried.Change what you have to Declare @var NVARCHAR(100) = '1,2,3';Declare @whereClause NVARCHAR(100);SET @whereClause = 'where Id=''001'' and locationId IN (' + @Var + ')';Declare @sql nvarchar(max) = 'Select name from tbl '+ @whereClauseprint @sql -- Note this prints "Select name from tbl where Id='001' and locationId IN (1,2,3)"EXEC sp_executesql @sqlThere are better examples in the link I gave aboveThe easy way to do this would be to use an SQL SPLIT function in your statement. This function was added with SQL Server 2016, previous versions would require a User Defined Function to have been installed. If this is the case there are plenty available on sites such as Sql Server CentralGood Read: The "Numbers" or "Tally" Table: What it is and how it replaces a loop[^]This statement is using the 2016+ built in function:DECLARE @ID NVARCHAR(3) = '001'DECLARE @LocationIDs NVARCHAR(100) = '1,2,3'SELECT NameFROM TableWHERE Id = @IDAND LocationID IN (SELECT value FROM STRING_SPLIT(@LocationIDs, ','))The second option is a little more complicated, requiring SQL User Defined Types as well as different syntax within the ADO call. The advantage is you can pass in more complex types such as DataTables, DataReaders, or iEnumerablesSQL PortionCREATE TYPE dbo.UserTableType_INTs AS TABLE( IntValue INT NOT NULL)GOCREATE PROCEDURE DBO.usp_MyProcedure ( @ID NVARCHAR(100), @LocationIDs dbo.UserTableType_INTs READONLY) ASBEGIN SELECT Name FROM Table WHERE Id = @ID AND LocationID IN @LocationIDsENDGOAnd calling it via ADO in C#// Sample datatable, I generally don't do this and my syntax may be offDataTable LocationIDs = new DataTable();LocationIDs.Clear();LocationIDs.Columns.Add("IntValue");LocationIDs.Rows.Add(1);LocationIDs.Rows.Add(2);LocationIDs.Rows.Add(3);SqlCommand cmd = new SqlCommand(sqlInsert, connection);cmd.CommandType = CommandType.StoredProcedure;// standard input parameter shortcutcmd.Parameters.AddWithValue("@ID", "001");// different parameter addition when using an SQL User TypeSqlParameter tvpParam = cmd.Parameters.AddWithValue("@LocationIDs", LocationIDs);tvpParam.SqlDbType = SqlDbType.Structured;tvpParam.TypeName = "dbo.UserTableType_INTs";You canot use @var in IN statement unless its dynamic SQL.Declare @var NVARCHAR(100) = '1,2,3';declare @sql varchar(300) = 'SELECT * FROM TBL WHERE ID IN (' + @var + ');';exec(@sql)Orelse you can use split function of string to split @var and use directly. 这篇关于TSQL查询:将参数传递给sp for in子句的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持! 上岸,阿里云!
08-26 08:08
查看更多