Question

这是我的 SQL 光标的片段, 工作正常。但我现在要将多个参数传送到 < code@sql 。

看到第二代代码片段的评论,我用谷歌搜索过,但我似乎找不到答案。

set @sql = N  use TESTDOMAINDATABASE  + convert(nvarchar, @domainID) + @NewLineChar  +
           N  select @subDomain = (select subDomain from tblDomains (nolock)) 

execute sp_executesql @sql, N @subDomain nvarchar(500) output , @subDomain output

print  subDomain is   + @subDomain +   and the domainID is   + convert(nvarchar,@domainID)

但请让我说,我想从 tblDomains 另选一栏( said domainName )。

我将如何更新上述发言?

set @sql = N  use TESTDOMAINDATABASE  + convert(nvarchar, @domainID) + @NewLineChar  +
           N  select @subDomain = (select subDomain from tblDomains (nolock))  + @NewLineChar  + 
           N select @domainName = (select domainName from tblDomains(nolock)) 

execute sp_executesql @sql, N @subDomain nvarchar(500) output , @subDomain output /* How do I pass multiple paramaters here ? */
execute sp_executesql @sql, N @domainName nvarchar(500) output , @domainName output /*Adding a 2nd line does not do the trick - results in an error that I must declare the scalar variable @domainName */

print  subDomain is   + @subDomain +   and the domainID is   + convert(nvarchar,@domainID) +   +  and the domain name is   @domainName

Answer 1

你的意思是这样吗?是否每个数据库 stblDomains 表格只有一行?

set @sql = N  use TESTDOMAINDATABASE  + convert(nvarchar, @domainID) + @NewLineChar  +
           N  select @subDomain = subDomain, @domainName = domainName 
              from tblDomains (nolock); 

execute sp_executesql @sql, 
    N @subDomain nvarchar(500) output, @domainName nvarchar(500) output , 
    @subDomain output, @domainName output;

print  subDomain is   + @subDomain +   and the domainID is   + convert(nvarchar,@domainID)

Answer 2

您可以将多个逗号分隔参数传递到 sp_ executesql 中。

DECLARE @sql nvarchar(MAX)
DECLARE @x nvarchar(25)
DECLARE @y nvarchar(25)

SET @sql = N SELECT @x =   hello  , @y =   world   

execute sp_executesql @sql, N @x nvarchar(25) output, @y nvarchar(25) output 
       ,@x output
       ,@y output

PRINT @x +     + @y

友情链接