如何在SQL Server存储过程中向单个参数传递两个值?

时间:2021-01-01 10:09:23

This is my Store Procedure :

这是我的店铺流程:

 ALTER PROC [dbo].[Details]

 @name nvarchar(20)

 AS

  BEGIN

 SELECT Id FROM Client WHERE name in (@Name)

 END

If i am passing one value output is Coming:

如果我传递一个值,输出如下:

Like this i am passing:

我就这样走过:

EXEC [dbo].[Details] 'Monday'

For Two values Like this :

对于这样的两个值:

If i am passing two value output not is Coming:

如果我传递的是两个值输出,则不是:

To get this in which way i need to send parameter currently I am passing Two parameter like This

为了达到这个目的,我现在需要发送参数,我传递了两个这样的参数

EXEC [dbo].[Details] ''Monday','Wipro'';

Thank u in advance

提前谢谢你

2 个解决方案

#1


3  

You can try this code to extract table from your parameter:

您可以尝试此代码从您的参数中提取表:

declare @sql nvarchar(max)=
'abc,def,ghi,jkl'
set @sql = replace(@sql,',','''),(''')
set @sql = 'select *
from (values ('''+@sql+''')
) a ( Value )
'
PRINT(@sql)
exec sp_executesql @sql

Then use execution result with IN-condition. You can configure this part with any delimiter.

然后使用执行结果与IN-condition。您可以使用任何分隔符配置此部分。

#2


1  

Try this one:

试试这个:

 ALTER PROCEDURE dbo.[Details]
    @name NVARCHAR(30)
    AS
        DECLARE @Sql VARCHAR(MAX)
        SET @name = REPLACE(@name,',',''',''')
        SET @Sql = 'SELECT
                    *
                    FROM table1
                    WHERE username IN
                    (
                         ''' + @name + '''
                    )'
        EXEC (@Sql)
        EXEC [dbo].[Details] 'mohan,prashant'

#1


3  

You can try this code to extract table from your parameter:

您可以尝试此代码从您的参数中提取表:

declare @sql nvarchar(max)=
'abc,def,ghi,jkl'
set @sql = replace(@sql,',','''),(''')
set @sql = 'select *
from (values ('''+@sql+''')
) a ( Value )
'
PRINT(@sql)
exec sp_executesql @sql

Then use execution result with IN-condition. You can configure this part with any delimiter.

然后使用执行结果与IN-condition。您可以使用任何分隔符配置此部分。

#2


1  

Try this one:

试试这个:

 ALTER PROCEDURE dbo.[Details]
    @name NVARCHAR(30)
    AS
        DECLARE @Sql VARCHAR(MAX)
        SET @name = REPLACE(@name,',',''',''')
        SET @Sql = 'SELECT
                    *
                    FROM table1
                    WHERE username IN
                    (
                         ''' + @name + '''
                    )'
        EXEC (@Sql)
        EXEC [dbo].[Details] 'mohan,prashant'