编写存储过程以在一个参数中传递一个值,在sql server中传递另一个参数中的两个值 [英] write a stored procedure to pass one value in one parameter and two values in another parameter in sql server
本文介绍了编写存储过程以在一个参数中传递一个值,在sql server中传递另一个参数中的两个值的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
亲爱的,
写一个存储过程,在一个参数中传递一个值,在sql server中传递另一个参数中的两个值。
我尝试使用以下代码。
当我只传递param1(多个值)时我得到了结果
我添加另一个时的BUt param(@name)。,没有得到结果。
请更正我
Dear all,
write a stored procedure to pass one value in one parameter and two values in another parameter in sql server.
I was tried with the below code.
I got result when i pass only param1 (multiple values)
BUt when i add another param (@name)., not getting result.
Please correct me
ALTER PROCEDURE [dbo].[sp_Sample] @param1 VARCHAR(100),@name varchar(30)
AS
Begin
DECLARE @Sql NVARCHAR(MAX)
SET @param1 = Replace(@param1, ',', ''',''')
SET @Sql = '(select * from tblsample where name=@name and [Escalate to] IN ('''
+ @param1 + '''))'
exec sp_executesql @Sql
End
EXEC [sp_Sample]
@param1 = 'SIM2,SIM3'
<pre>
谢谢..............
Thanks..............
推荐答案
使用分割功能分割变量所需的任务。
喜欢这个
从fn_Split中选择值('par1,par2 ',',')
使用上面这样的查询
IN(从fn_Split中选择值(@ param1,','))
首先执行此
创建FUNCTION [dbo]。[fn_Split](@ sText varchar(8000),@ sselim varchar(20)='')
RETURNS @retArray TABLE(idx smallint主键,值varchar( 8000))
AS
BEGIN
DECLARE @idx smallint,
@value varchar(8000),
@bcontinue位,
@iStrike smallint,
@iDelimlength tinyint
一世F @sDelim ='Space'
BEGIN
SET @sDelim =''
END
SET @idx = 0
SET @sText = LTrim(RTrim(@sText))
SET @iDelimlength = DATALENGTH(@sDelim)
SET @bcontinue = 1
如果不是((@ iDelimlength = 0)或(@sDelim ='空'))
BEGIN
WHILE @bcontinue = 1
BEGIN
- 如果你能在文本中找到分隔符,检索第一个元素并且
- 将其索引插入到返回表中。
如果CHARINDEX(@sDelim,@ sText)> 0
BEGIN
SET @value = SUBSTRING(@ sText,1,CHARINDEX( @ sDelim,@ sText)-1)
BEGIN
INSERT @retArray(idx,value)
VALUES(@ idx,@ value)
END
- 从字符串的前面修剪元素及其分隔符。
- 增加索引和循环。
SET @iStrike = DATALENGTH(@value)+ @ iDelimlength
SET @idx = @idx + 1
SET @sText = LTrim(对(@ sText,DATALENGTH(@sText) - @iStrike))
结束
ELSE
BEGIN
- 如果你在文本中找不到分隔符,@ sText是
中的最后一个值 - @ retArray。
SET @value = @sText
BEGIN
INSERT @retArray(idx,value)
VALUES(@ idx,@ value)
END
- 退出WHILE循环。
SET @bcontinue = 0
END
END
END
ELSE
BEGIN
WHILE @ bcontinue = 1
BEGIN
- 如果分隔符为空字符串,请检查剩下的文字
- 而不是分隔符。将第一个字符插入
--retArray表中。修剪字符串前面的字符。
- 增加索引和循环。
IF DATALENGTH(@sText)> 1
BEGIN
SET @value = SUBSTRING(@ sText,1,1)
BEGIN
INSERT @retArray(idx,value)
VALUES(@ idx,@ value)
END
SET @idx = @ idx + 1
SET @sText = SUBSTRING(@ sText,2,DATALENGTH(@sText)-1)
END
ELSE
BEGIN
- 剩下的一个角色。
- 插入字符,然后退出WHILE循环。
INSERT @retArray(idx,value)
VALUES(@ idx,@ sText)
SET @bcontinue = 0
结束
结束
结束
返回
结束
Hi,
to achive the task you need to split the variable using split function.
like this
select value from fn_Split ('par1,par2',',')
use the above query like this
IN(select value from fn_Split (@param1,','))
first execute this
create FUNCTION [dbo].[fn_Split](@sText varchar(8000), @sDelim varchar(20) = ' ')
RETURNS @retArray TABLE (idx smallint Primary Key, value varchar(8000))
AS
BEGIN
DECLARE @idx smallint,
@value varchar(8000),
@bcontinue bit,
@iStrike smallint,
@iDelimlength tinyint
IF @sDelim = 'Space'
BEGIN
SET @sDelim = ' '
END
SET @idx = 0
SET @sText = LTrim(RTrim(@sText))
SET @iDelimlength = DATALENGTH(@sDelim)
SET @bcontinue = 1
IF NOT ((@iDelimlength = 0) or (@sDelim = 'Empty'))
BEGIN
WHILE @bcontinue = 1
BEGIN
--If you can find the delimiter in the text, retrieve the first element and
--insert it with its index into the return table.
IF CHARINDEX(@sDelim, @sText)>0
BEGIN
SET @value = SUBSTRING(@sText,1, CHARINDEX(@sDelim,@sText)-1)
BEGIN
INSERT @retArray (idx, value)
VALUES (@idx, @value)
END
--Trim the element and its delimiter from the front of the string.
--Increment the index and loop.
SET @iStrike = DATALENGTH(@value) + @iDelimlength
SET @idx = @idx + 1
SET @sText = LTrim(Right(@sText,DATALENGTH(@sText) - @iStrike))
END
ELSE
BEGIN
--If you can’t find the delimiter in the text, @sText is the last value in
--@retArray.
SET @value = @sText
BEGIN
INSERT @retArray (idx, value)
VALUES (@idx, @value)
END
--Exit the WHILE loop.
SET @bcontinue = 0
END
END
END
ELSE
BEGIN
WHILE @bcontinue=1
BEGIN
--If the delimiter is an empty string, check for remaining text
--instead of a delimiter. Insert the first character into the
--retArray table. Trim the character from the front of the string.
--Increment the index and loop.
IF DATALENGTH(@sText)>1
BEGIN
SET @value = SUBSTRING(@sText,1,1)
BEGIN
INSERT @retArray (idx, value)
VALUES (@idx, @value)
END
SET @idx = @idx+1
SET @sText = SUBSTRING(@sText,2,DATALENGTH(@sText)-1)
END
ELSE
BEGIN
--One character remains.
--Insert the character, and exit the WHILE loop.
INSERT @retArray (idx, value)
VALUES (@idx, @sText)
SET @bcontinue = 0
END
END
END
RETURN
END
Alter procedure sp_sample3
@Escalation varchar(30),@InClause NVARCHAR(100)
as
DECLARE @SafeInClause NVARCHAR(100)
SET @SafeInClause = ',' + @InClause + ','
SELECT * FROM tblSample WHERE [Escalate To]=@Escalation and CHARINDEX(',' + [Owner Dept] + ',', @SafeInClause) > 0
exec sp_sample3
@Escalation ='SIM5',
@ InClause ='6SG,HR,Fin'
exec sp_sample3
@Escalation='SIM5',
@InClause='6SG,HR,Fin'
检查这个希望这对你有用。
Check this hope this will work for you.
ALTER PROCEDURE [dbo].[sp_Sample] @param1 VARCHAR(100),@name varchar(30)
AS
Begin
DECLARE @Sql NVARCHAR(MAX)
---- SET @param1 = Replace(@param1, ',', ''',''')
SET @Sql = '(select * from tblsample where name='''+ @name +''' and [Escalate to] IN ('''+ @param1 + '''))'
exec sp_executesql @Sql
End
这篇关于编写存储过程以在一个参数中传递一个值,在sql server中传递另一个参数中的两个值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文