将varchar传递给sp [英] Pass varchar in to sp
本文介绍了将varchar传递给sp的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我需要创建一个存储过程并传入一个参数,这里是:
create procedure GetEmployeeRole @ role varchar (< span class =code-digit> 20 )
as
select * 来自员工其中 employeeRole (' Tech',' < span class =code-string> Manager')
GO
我想用@role替换('Tech','Manager'),所以查询将是
选择 * 来自员工 where employeeRole in ( @ role )
所以我可以传入任何我想要的角色,我传入
@ role = ' Tech' + ' ,' + ' 经理'
,它不起作用,所以我做错了什么?谢谢。
解决方案
试试这个
方法1:
create procedure GetEmployeeRole @ role varchar ( 20 )
as
声明 @sqlQuery nvarchar (max)
set @ sqlQuery = ' select * from Employee,其中employeeRole in(''' + replace( @ role ,' ,',' '',''')+ ' ''''
exec sp_executesql @sqlQuery
GO
方法2:
将逗号分隔的参数传递给存储过程 [ ^ ]
希望它有所帮助。
我会有所不同。
首先,创建一个分割输入变量的函数:
创建FUNCTION拆分(
@delimited NVARCHAR(MAX),
@delimiter NVARCHAR(100)
)
RETURNS @t TABLE(id INT IDENTITY(1,1),val NVARCHAR( MAX))
AS
BEGIN
DECLARE @xml XML
SET @xml = N'< t>'+ REPLACE(@ delimited,@ delimiter,'< / t> ;< t>')+'< / t>'
INSERT INTO @t(val)
SELECT r.value('。','varchar(MAX)')项目
FROM @ xml.nodes('/ t')作为记录(r)
RETURN
END
接下来,查询将变为如下所示:
SELECT * INTO #SplitResults FROM dbo.split( @ role ,' ,')
SELECT
*
FROM
员工 INNER JOIN #SplitResults ON
Employee.employeeRole =#SplitResults.Val
DROP TABLE #SplitResults
你可以将@role传递给:
@role =' Tech,Manager'
DECLARE @ role VARCHAR (MAX)
SET @ role = ' ''Tech''' + ' ,' + ' 曼ager'''
SELECT @ role
I need to create a stored procedure and pass in a parameter, here it is:
create procedure GetEmployeeRole @role varchar(20)
as
select * from Employee where employeeRole in ('Tech', 'Manager')
GO
I want to use @role to replace ('Tech', 'Manager'), so the query will be
select * from Employee where employeeRole in (@role)
So I can pass in whatever role I want, I passed in
@role = 'Tech' + ',' + 'Manager'
, It didn't work, so what did I do wrong? Thanks.
解决方案
Try this
Approach 1:
create procedure GetEmployeeRole @role varchar(20) as declare @sqlQuery nvarchar(max) set @sqlQuery = 'select * from Employee where employeeRole in ('''+replace(@role,',',''',''')+''')' exec sp_executesql @sqlQuery GO
Approach 2:
Passing comma delimited parameter to stored procedure[^]
Hope it helps.
I would do it bit differently.
First, create a function to split the input variable:
CREATE FUNCTION split( @delimited NVARCHAR(MAX), @delimiter NVARCHAR(100) ) RETURNS @t TABLE (id INT IDENTITY(1,1), val NVARCHAR(MAX)) AS BEGIN DECLARE @xml XML SET @xml = N'<t>' + REPLACE(@delimited,@delimiter,'</t><t>') + '</t>' INSERT INTO @t(val) SELECT r.value('.','varchar(MAX)') as item FROM @xml.nodes('/t') as records(r) RETURN END
Next, the query would become something like this:
SELECT * INTO #SplitResults FROM dbo.split(@role, ',') SELECT * FROM Employee INNER JOIN #SplitResults ON Employee.employeeRole = #SplitResults.Val DROP TABLE #SplitResults
And you can pass the @role as:
@role = 'Tech,Manager'
DECLARE @role VARCHAR(MAX) SET @role = '''Tech''' + ',' + '''Manager''' SELECT @role
这篇关于将varchar传递给sp的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文