Must declare the scalar variable "@serverName"

Question

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"

You can't access `@serverName` (or any of the other variables) from inside `EXEC(@sql)`, because they are declared outside of it. Dynamic SQL doesn't work like that. — Blorgbeard, Dec 07 '16 at 01:21
See: http://stackoverflow.com/questions/28481189/exec-sp-executesql-with-multiple-parameters — Blorgbeard, Dec 07 '16 at 01:22
If i can't access @serverName from inside EXEC(@sql), then what is the best solution for that? — Jhe, Dec 07 '16 at 01:25

Pரதீப் · Answer 1 · 2016-12-07T02:13:45.683

Use SP_EXECUTESQL to pass values to parameters inside dynamic 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 as Insert 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 of OPENDATASOURCE query
@tblName input parameter is not used inside the procedure.

Ash · Answer 2 · 2016-12-07T01:53:06.253

You're using @serverName as part of the string. Try

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.

Must declare the scalar variable "@serverName"

2 Answers2