Using Variables in an SQL Script

Question

Hi,

I am completely new to using Variables.

I am trying to set some variables in an SQL script

What am I doing wrong here?

DECLARE @DATABASE AS VARCHAR(50)
DECLARE @TABLE AS VARCHAR(50)
DECLARE @QUERY AS VARCHAR(MAX)
SELECT  @DATABASE = '602'
SELECT  @TABLE = 'Items'

SET @QUERY = 'SELECT TOP 10 * FROM ' + @DATABASE+'.'+@TABLE

EXEC( @QUERY)

I am getting: Incorrect syntax near '602.'.

Furthermore: Is there a way to debug this in SSMS?

Thanks for your help,

Arnold

---

ArnolddG

Answer 1

Where's the schema?

SELECT TOP 10 * FROM 602.dbo.Items

Answer 2

Looks like you're missing the schema name and you'll want to use QUOTENAME to add delimiters to the objects

e.g. 

DECLARE @DATABASE AS VARCHAR(50)
DECLARE @SchemaName as SYSNAME;
DECLARE @TABLE AS VARCHAR(50)
DECLARE @QUERY AS VARCHAR(MAX)
SELECT  @DATABASE = '602'
SELECT  @SchemaName = 'dbo' --change as appropriate
SELECT  @TABLE = 'Items'

SET @QUERY = 'SELECT TOP 10 * FROM ' + QUOTENAME(@DATABASE)+'.' + QUOTENAME(@SchemaName) +  '.'+QUOTENAME(@TABLE)

print @query

EXEC( @QUERY)

Answer 3

Thanks for your help Greg,

You are right. that is what I was missing.

---

ArnolddG

Answer 4

Thanks for helping me out here Prashanth Jayaram ...

This works perfectly.

---

ArnolddG