locked
Using Variables in an SQL Script RRS feed

  • 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

    Thursday, March 12, 2015 5:06 PM

Answers

  • Where's the schema?

    SELECT TOP 10 * FROM 602.dbo.Items

    Thursday, March 12, 2015 5:19 PM
  • 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)
    

    Thursday, March 12, 2015 5:22 PM

All replies

  • Where's the schema?

    SELECT TOP 10 * FROM 602.dbo.Items

    Thursday, March 12, 2015 5:19 PM
  • 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)
    

    Thursday, March 12, 2015 5:22 PM
  • Thanks for your help Greg,

    You are right. that is what I was missing.


    ArnolddG

    Thursday, March 12, 2015 5:36 PM
  • Thanks for helping me out here Prashanth Jayaram ...

    This works perfectly.


    ArnolddG



    • Edited by ArnolddG Thursday, March 12, 2015 7:32 PM
    Thursday, March 12, 2015 5:37 PM