Using Variables in Query 1

[Lrnmore]

Hello,

I have a question regarding using variables. My environment is SQL Server 2005. I know the variable is set because it only breaks the query if I use it in certain areas. Can someone point out my error?

USE SampleCode

Declare @myTableName varchar(45)
Set @myTableName = 'TestTable';
 IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @myTableName) 
			DROP TABLE TestTable  --error if using @myTableName here

 CREATE TABLE TestTable (  --error if using @myTableName here
	CodeId int identity(1,1) NOT NULL PRIMARY KEY,
	CodeFileName varchar(250),
	Description varchar(500),
	CodeText varchar(max),
	Tags varchar(250)
) 
SELECT COUNT(t.TABLE_NAME) AS [Exists], 
	@myTableName [Test Set]
FROM INFORMATION_SCHEMA.TABLES t WHERE TABLE_NAME = @myTableName

Thanks for help with this.

 

[bborissov]

You can't use variable as a name of the table, database or schema. You should use so called Dynamic SQL:

USE SampleCode

Declare @myTableName varchar(45)
Set @myTableName = 'TestTable';
DECLARE @sql varchar(max)
SET @sql = '
 IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '''+@myTableName+''')
            DROP TABLE'+@myTableName+'
 CREATE TABLE '+@myTableName+'(
    CodeId int identity(1,1) NOT NULL PRIMARY KEY,
    CodeFileName varchar(250),
    Description varchar(500),
    CodeText varchar(max),
    Tags varchar(250)
)'
EXEC (@sql)

SELECT COUNT(t.TABLE_NAME) AS [Exists],
    @myTableName [Test Set]
FROM INFORMATION_SCHEMA.TABLES t WHERE TABLE_NAME = @myTableName

Not tested!

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[Lrnmore]

Thanks for the explanation bborissov, I understand.



Thanks,
--Mark