Must declare the table variable (dynamic query)

Question

I have a stored procedure like this.

ALTER PROCEDURE [dbo].[storedProc_dataPull] 
    @serverName nvarchar(30),
    @dbName nvarchar(30),
    @tblName nvarchar(30),
    @schemaName nvarchar (30),
    @userID nvarchar (30), 
    @password nvarchar (30),
    @sampleTbl nvarchar (30)
AS 
BEGIN
    DECLARE @schemaAndTbl nvarchar (39)

    SET @schemaAndTbl = @dbName + '.' + @schemaName + '.' +@tblName

    EXEC('INSERT INTO @sampleTbl 
       ([ID]
      ,[ActivityDefinitionID]
      ,[ParentID]
      ,[Caption]
      ,[Description]
      ,[ShortDescription]
      ,[Name]
      ,[Order]
      ,[ReferenceNumber]
      ,[ShowOnNavigation]
      ,[Status]
      ,[InUseBy]
      ,[ExpectedStartDate]
      ,[ActualStartDate]
      ,[ExpectedEndDate]

    SELECT * 
    FROM OPENDATASOURCE(''SQLOLEDB'',''Data Source=' + @serverName+ ';User ID='+@userID+';Password=' +@password+''').'+@schemaAndTbl+' sdb1')

    UPDATE @sampleTbl 
    SET ServerName = ''+@serverName+'', DBName = ''+@dbName+'' 
    WHERE ServerName IS NULL AND DBName IS NULL
END

When I'm trying to execute, I always get an error:

Must declare the table variable

My next problem is to pass column name as parameter as well.

Is it possible with SQL Server? I just want to make a dynamic stored procedure for my application.

Answer 1

You have not declared @sampleTbl in your dynamic Query.

And Table Variables scope is limited to the batch of execution. And the Exec statement will execute the statements in a batch. Hence that Declaring, Inserting, Selecting & Updating of Table variables must and should be in the same batch. Means It should contain inside of EXEC Statement.

2nd Question:My next problem is to pass column name as parameter as well.

Yes it is possible with sp_executesql

Have a look at sp_executesql (Transact-SQL) for the same.

Answer 2

you can modify your statement, or you can define a custom data type base the struct of table variable. than can you in sp_executesql

INSERT INTO @sampleTbl 
EXEC(' SELECT
   ([ID]
  ,[ActivityDefinitionID]
  ,[ParentID]
  ,[Caption]
  ,[Description]
  ,[ShortDescription]
  ,[Name]
  ,[Order]
  ,[ReferenceNumber]
  ,[ShowOnNavigation]
  ,[Status]
  ,[InUseBy]
  ,[ExpectedStartDate]
  ,[ActualStartDate]
  ,[ExpectedEndDate]

 SELECT * FROM OPENDATASOURCE(''SQLOLEDB'',''Data Source=' + @serverName+ ';User ID='+@userID+';Password=' +@password+''').'+@schemaAndTbl+' sdb1')

Answer 3

The sample table @sampleTbl is a parameter in your stored procedure. Also this is with type nvarchar (30). This is not correct. I don't understand why you want to pass the table here as a parameter. If you want to pass table as a parameter, then you should declare it as a TYPE and the table should be passed as READONLY. You will not be able to write values in that table. Remove it from the parameter and modify the query as mentioned in the above answers. It will work..

Happy Coding:)