I know that we have a lot of answers for this problem but I still don't understand what really the cause of this error.
我知道对于这个问题我们有很多答案,但是我仍然不明白这个错误的真正原因是什么。
I've created a stored procedure like this.
我创建了这样的存储过程。
CREATE PROCEDURE [dbo].[storedProc_dataPull]
@serverName nvarchar(30),
@dbName nvarchar (30),
@tblName nvarchar(30)
AS BEGIN
DECLARE @sql nvarchar(500)
DECLARE @ds nvarchar(500)
SET @ds = 'Data Source=phmnldb16\eaudit;user id=YYYY;password=XXXX'
SET @sql = 'SELECT @serverName, @dbName, sdb1.* from
OPENDATASOURCE(''SQLOLEDB'', '+Char(39)+ @ds +Char(39)+ ').AUDIT_FSA_170_001.AUD170.Workflow sdb1)'
INSERT INTO sampleDatabase.dbo.WorkFlowCopy
([ServerName]
,[DBName]
,[ID]
,[ActivityDefinitionID]
,[ParentID]
,[Caption]
,[Description]
,[ShortDescription]
,[Name]
,[Order]
,[ReferenceNumber]
,[ShowOnNavigation]
,[Status]
,[InUseBy])
EXEC (@sql)
And when I tried to execute it..
当我试图执行它的时候。
EXEC storedProc_dataPull 'serverName', 'dbName', 'tblName'
I always got this error:
我总是犯这样的错误:
Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "serverName"Msg 137,第15层,状态2,第1行必须声明标量变量“serverName”
2 个解决方案
#1
3
Use SP_EXECUTESQL
to pass values to parameters inside dynamic sql
使用SP_EXECUTESQL将值传递给动态sql中的参数。
SET @sql = 'SELECT @serverName, @dbName, sdb1.* from
OPENDATASOURCE(''SQLOLEDB'', '
+ Char(39) + @ds + Char(39)
+ ').AUDIT_FSA_170_001.AUD170.Workflow sdb1' -- unwanted close parenthesis
INSERT INTO sampleDatabase.dbo.WorkFlowCopy
([ServerName],
[DBName],
[ID],
[ActivityDefinitionID],
[ParentID],
[Caption],
[Description],
[ShortDescription],
[Name],
[Order],
[ReferenceNumber],
[ShowOnNavigation],
[Status],
[InUseBy]) -- Missing close parenthesis
EXEC Sp_executesql
@sql,
N'@serverName nvarchar(30), @dbName nvarchar (30)',
@serverName=@serverName,
@dbName=@dbName
Notes :
注:
- In
Select
list you have used,*
make sure the select list returns the same number of columns asInsert
column list and in same order - 在您使用的选择列表中,*确保选择列表返回与插入列列表相同的列数,并且顺序相同
- There is a missing close parenthesis at the end of
Insert
column list and a unwanted close parenthesis at the end ofOPENDATASOURCE
query - 在插入列列表的末尾有一个缺失的闭括号,在OPENDATASOURCE查询的末尾有一个不想要的闭括号
-
@tblName
input parameter is not used inside the procedure. - 在过程中不使用@tblName输入参数。
#2
1
You're using @serverName
as part of the string. Try
您正在使用@serverName作为字符串的一部分。试一试
SET @sql = 'SELECT ' + @serverName + ',' + @dbName + ', sdb1.* from
OPENDATASOURCE(''SQLOLEDB'','+Char(39)+ @ds + Char(39)+ ').AUDIT_FSA_170_001.AUD170.Workflow sdb1'
instead of
而不是
SET @sql = 'SELECT @serverName, @dbName, sdb1.* from
OPENDATASOURCE(''SQLOLEDB'', '+Char(39)+ @ds +Char(39)+ ').AUDIT_FSA_170_001.AUD170.Workflow sdb1)'
I've had to remove an extra bracket at the end of @sql
as well.
我还不得不在@sql末尾删除一个额外的括号。
#1
3
Use SP_EXECUTESQL
to pass values to parameters inside dynamic sql
使用SP_EXECUTESQL将值传递给动态sql中的参数。
SET @sql = 'SELECT @serverName, @dbName, sdb1.* from
OPENDATASOURCE(''SQLOLEDB'', '
+ Char(39) + @ds + Char(39)
+ ').AUDIT_FSA_170_001.AUD170.Workflow sdb1' -- unwanted close parenthesis
INSERT INTO sampleDatabase.dbo.WorkFlowCopy
([ServerName],
[DBName],
[ID],
[ActivityDefinitionID],
[ParentID],
[Caption],
[Description],
[ShortDescription],
[Name],
[Order],
[ReferenceNumber],
[ShowOnNavigation],
[Status],
[InUseBy]) -- Missing close parenthesis
EXEC Sp_executesql
@sql,
N'@serverName nvarchar(30), @dbName nvarchar (30)',
@serverName=@serverName,
@dbName=@dbName
Notes :
注:
- In
Select
list you have used,*
make sure the select list returns the same number of columns asInsert
column list and in same order - 在您使用的选择列表中,*确保选择列表返回与插入列列表相同的列数,并且顺序相同
- There is a missing close parenthesis at the end of
Insert
column list and a unwanted close parenthesis at the end ofOPENDATASOURCE
query - 在插入列列表的末尾有一个缺失的闭括号,在OPENDATASOURCE查询的末尾有一个不想要的闭括号
-
@tblName
input parameter is not used inside the procedure. - 在过程中不使用@tblName输入参数。
#2
1
You're using @serverName
as part of the string. Try
您正在使用@serverName作为字符串的一部分。试一试
SET @sql = 'SELECT ' + @serverName + ',' + @dbName + ', sdb1.* from
OPENDATASOURCE(''SQLOLEDB'','+Char(39)+ @ds + Char(39)+ ').AUDIT_FSA_170_001.AUD170.Workflow sdb1'
instead of
而不是
SET @sql = 'SELECT @serverName, @dbName, sdb1.* from
OPENDATASOURCE(''SQLOLEDB'', '+Char(39)+ @ds +Char(39)+ ').AUDIT_FSA_170_001.AUD170.Workflow sdb1)'
I've had to remove an extra bracket at the end of @sql
as well.
我还不得不在@sql末尾删除一个额外的括号。