Post reply

Question variable substritution

  • September 20, 2011 at 9:38 pm

    #244199

    Hi All,

    I have a question on variable substitution. I have secario-1 and scenario-2

    In scenario-1, the variable substitution works fine and in scenario-2 (Run-1) variable substitution fails.

    Just wanted to know the reason behind it.

    Thanks in Advance.

    -- SCENARIO -1

    USE db1

    GO

    CREATE TABLE DEPT

    (DEPTNO INT,

    DNAME VARCHAR(10)

    )

    INSERT INTO DEPT

    SELECT 10,'ACCOUNTS'

    UNION ALL

    SELECT 20,'SALES'

    UNION ALL

    SELECT 30,'MARKETTING'

    UNION ALL

    SELECT 40,'HR'

    DECLARE @DEPTNO INT

    SET @DEPTNO = 30

    SELECT * FROM DEPT

    WHERE DEPTNO = @DEPTNO

    /*

    30MARKETTING

    */

    SCENARIO-2

    --Run1

    DECLARE @DBNAME VARCHAR(100)

    SET @DBNAME = 'DB1'

    ALTER DATABASE @DBNAME SET READ_ONLY WITH NO_WAIT

    /*

    Msg 102, Level 15, State 1, Line 3

    Incorrect syntax near '@DBNAME'.

    Msg 319, Level 15, State 1, Line 3

    Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.

    */

    --This works

    --Run2

    ALTER DATABASE DB1 SET READ_ONLY WITH NO_WAIT

    -- This to works

    --Run3

    DECLARE @DBNAME VARCHAR(100)

    DECLARE @QRY VARCHAR(100)

    SET @DBNAME = 'DB1'

    SET @QRY ='ALTER DATABASE '+@DBNAME+' SET READ_ONLY WITH NO_WAIT'

    EXEC (@QRY)

  • September 21, 2011 at 2:02 am

    #1384354

    Generally speaking, DDL statements do not accept variables, while DML statements do.

    Using dynamic SQL is a common workaround.

    -- Gianluca Sartori

  • September 22, 2011 at 10:56 am

    #1385325

    Gianluca's right about this in that variable substitution doesn't work here. Made me think about why. Let's look at the code that failed. It defines local variable @DBNAME as a varchar and assigns it value "DB1".

    DECLARE @DBNAME VARCHAR(100)

    SET @DBNAME = 'DB1'

    That's well and good. But then the trouble comes with the next line. It tells SQL Server to ALTER the local variable.

    ALTER DATABASE @DBNAME SET READ_ONLY WITH NO_WAIT

    Since the DBMS cannot alter the varchar variable, you get the error.

    The suggested solution would be to build the ALTER statement with string concatenation, and EXEC the resultant command.

    declare @DBName varchar(100)

    Set @DBName = 'DB1'

    Exec ('ALTER DATABASE ' + @DBNAME + ' SET READ_ONLY WITH NO_WAIT')

  • September 24, 2011 at 8:12 pm

    #1386160

    Yes. you are right. I wanted to know reason for it.

  • September 24, 2011 at 10:41 pm

    #1386164

    Oracle_91 (9/24/2011)

    Yes. you are right. I wanted to know reason for it.

    The only reason I can think of is "that's the way they built it".

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply