Using declared variables in place of table names

[ljwilson]

How can I use declared variables in the place of table names? What I am trying to do is write a stored proc that will take data from a linked server and put it into tables in a local DB. There are several tables that I have to deal with and was just wanting to assign the current local table name and the linked table name to variables and use them.

Here is a snippet

@CURRENT_LOCAL_TABLE = 'ARIALOCAL.dbo.users'
	@CURRENT_ARIA_TABLE = '[ARIA-MYSQL]...users'
	SELECT @ROWCNT = (SELECT COUNT(*) FROM @CURRENT_ARIA_TABLE)
	IF (@ROWCNT > 0)
		BEGIN
			BEGIN TRAN
			-- Truncate the table if it exists locally
			IF OBJECT_ID (@CURRENT_LOCAL_TABLE,'U') IS NOT NULL
				BEGIN
					TRUNCATE table @CURRENT_LOCAL_TABLE
				END

Right now it is showing me an error saying it is expecting a QuotedID.

Any advice?

 

[bborissov]

You can't do that. But you can use so called Dynamic SQL:

@CURRENT_LOCAL_TABLE = 'ARIALOCAL.dbo.users'
@CURRENT_ARIA_TABLE = '[ARIA-MYSQL]...users'
DECLARE @sql Nvarchar(1000)
SET @sql = 'SELECT @ROWCNT = COUNT(*) FROM '+
                             @CURRENT_ARIA_TABLE
EXEC sp_executesql @sql, '@ROWCNT int OUTPUT', @ROWCNT = @ROWCNT OUTPUT

IF (@ROWCNT > 0)
   BEGIN
        BEGIN TRAN
        -- Truncate the table if it exists locally
            IF OBJECT_ID (@CURRENT_LOCAL_TABLE,'U') IS NOT NULL
                BEGIN
                    SET @sql = 'TRUNCATE TABLE '+ 
                                @CURRENT_LOCAL_TABLE
                    EXEC(@sql)
                END

NOT TESTED!!!

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[ljwilson]

I am getting the following error message when trying to execute the SP

Msg 214, Level 16, State 3, Procedure sp_executesql, Line 1
Procedure expects parameter '@parameters' of type 'ntext/nchar/nvarchar'.

Here is my code:

USE [ARIALOCAL]
GO
/****** Object:  StoredProcedure [dbo].[sp_Create_ARIA_Data]    Script Date: 06/25/2009 09:24:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		LJ Wilson
-- Create date: 2009-06-24
-- Description:	This will get all the necessary data from the ARIA-MYSQL Linked Server and
--              create tables and then insert records in the ARIALOCAL DB. 
-- =============================================
ALTER PROCEDURE [dbo].[sp_Create_ARIA_Data] 
AS
BEGIN
	--
	DECLARE @ROWCNT INT
	DECLARE @ERRORCODE INT
	DECLARE @SQL NVARCHAR(1000)
	DECLARE @CURRENT_LOCAL_TABLE NVARCHAR(50)
	DECLARE @CURRENT_ARIA_TABLE NVARCHAR(50)

	
	-- The users table (we do not want the password field)
	SET @CURRENT_ARIA_TABLE = '[ARIA-MYSQL]...users'
	SET @CURRENT_LOCAL_TABLE = 'ARIALOCAL.dbo.users'
	
	SET @SQL = 'SELECT @ROWCNT = COUNT(*) FROM '+
                             @CURRENT_ARIA_TABLE
	EXEC sp_executesql @SQL, '@ROWCNT int OUTPUT', @ROWCNT = @ROWCNT OUTPUT

	IF (@ROWCNT > 0)
		BEGIN
			BEGIN TRAN
			-- DROP the table if it exists locally
			IF OBJECT_ID (@CURRENT_LOCAL_TABLE,'U') IS NOT NULL
			BEGIN
					SET @SQL = 'DROP TABLE '+ 
                                @CURRENT_LOCAL_TABLE
                    EXEC(@SQL)

									END
			
			SET @SQL = 'SELECT ID,  
		           username ,  
		           sapid ,  
		           privileges ,  
		           userid ,  
		           divisionid ,  
		           deptid ,  
		           subdeptid ,  
		           networkname ,  
		           [action groups] ,  
		           isirbcalc ,  
		           irb_status ,  
		           groups ,  
		           pi_serial ,  
		           mailnickname ,  
		           first_name ,  
		           last_name ,  
		           title   
		    INTO ' + @CURRENT_LOCAL_TABLE + ' FROM ' + @CURRENT_ARIA_TABLE
			EXEC(@SQL)
			 
			SELECT  @ERRORCODE = @@ERROR
			IF ( @ERRORCODE <> 0 ) 
				BEGIN
						PRINT 'Error Occurred with the '+ @CURRENT_LOCAL_TABLE +' table'
						ROLLBACK TRAN
				END
			ELSE
				BEGIN
					COMMIT TRAN
				END
		END
		

       
END

Thanks!