none
Passed schema as parameter then update schema.table using dynamic sql but encounter [schema] not permitted error

    Question

  • I'm using dynamicSQL. Here's part of the sql query.

    UPDATE '+@Schm+'.Location
            SET IsActive ='+CONVERT(VARCHAR(10),1)+'
            WHERE LocId ='+CONVERT(VARCHAR(100),@LocID)+'
            IF @@ERROR <> 0
                 BEGIN
                    -- Rollback the transaction
                    ROLLBACK                    
                    RETURN
                 END

    @schm is the schema name passed as parameter, while the Location is the name of the table. When I tried to execute it, this is the error.

     

    The name "GTA" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

    The "AB" is the schema name.

    Apparently @schm.Location was being read as table.column instead of schema.table.

    Any idea what I should do?


    I Believe In Me

    Wednesday, August 28, 2013 4:00 AM

Answers

  • Hi,

    I've figured things out. Apparently the error occurred at another part of the code. It's solved now.

    INSERT INTO REF.LocLog
    			VALUES ('+CONVERT(VARCHAR(100),@LocID)+','''+@schm+''', '+CONVERT(VARCHAR(10),1)+', '+CONVERT(VARCHAR(100),@CreatedBy)+', '+CONVERT(VARCHAR(100),@CreatedDate)+')
    			

    I need to put 3 apostrophes on either side of the +@schm+ in order for it to work.

    • Marked as answer by CY-L Wednesday, August 28, 2013 7:07 AM
    Wednesday, August 28, 2013 7:07 AM

All replies

  • Hi,

    If you want to use schema to be used with table then you have 2 ways for this.

    1. @schm.dbo.Location

    2. @schm..Location


    Sandeep J. Sharma (eZee Technosys)

    Wednesday, August 28, 2013 4:32 AM
  • Hi, thank you for answering but it's having the same error.

    I Believe In Me

    Wednesday, August 28, 2013 4:34 AM
  • This is not Dynamic SQL. Please try something like this one:

    USE AdventureWorks2008R2;
    GO
                 
    DECLARE @Schm sysname;
    DECLARE @SQL NVARCHAR(MAX);
    
    SET @Schm = N'Person';
    
    SET @SQL = N'          
    UPDATE ' + @Schm + N'.Address
    SET AddressLine1 = ''''
    WHERE AddressID = 2 ';
    
    PRINT @SQL  
    
    EXEC sp_executesql @SQL;       

    In the above script we do these steps to create and run Dynamic SQL Query:

    Declare a variable with NVARCHAR(MAX) data type.

    Produce the Query Statement that we want to run and save it in the above @SQL variable.

    Execute it Dynamically.

    The Print is just to see if the statement is correct. For example the above print statement print this:

    UPDATE Person.Address
    SET AddressLine1 = ''
    WHERE AddressID = 2 

    For more information please refer this article:

    The Curse and Blessings of Dynamic SQL 


    The most important motivation for the research work that resulted in the relational model was the objective of providing a sharp and clear boundary between the logical and physical aspects of database management. - E. F. Codd


    My blog

    Wednesday, August 28, 2013 5:01 AM
  • Hi,

    If you want to use schema to be used with table then you have 2 ways for this.

    1. @schm.dbo.Location

    2. @schm..Location


    Sandeep J. Sharma (eZee Technosys)

    This is wrong!

    He has the Schema and it's might not the default schema. In addition The Schema cannot be nested so this a mistake. 


    The most important motivation for the research work that resulted in the relational model was the objective of providing a sharp and clear boundary between the logical and physical aspects of database management. - E. F. Codd


    My blog

    Wednesday, August 28, 2013 5:04 AM
  • In addition please use TRY/CATCH block to handle errors like this pattern:

    BEGIN TRY
       --<regular code>
    END TRY
    BEGIN CATCH
       --<error handling>
    END CATCH
    

    For more information, please see this link:

    Error Handling in SQL 2005 and Later


    The most important motivation for the research work that resulted in the relational model was the objective of providing a sharp and clear boundary between the logical and physical aspects of database management. - E. F. Codd


    My blog

    Wednesday, August 28, 2013 5:11 AM
  • The code I posted was just a part of the whole sql query.  It's the 

    UPDATE '+@Schm+'.Location

    part that's encountered the error. Because it read it as Table.Column instead of Schema.Table, hence the "Column names are not permitted" error.

    The statement itself is correct, but it won't execute.

    It's just this update part that gets the error. Select queries work fine.

    • Edited by CY-L Wednesday, August 28, 2013 6:00 AM
    Wednesday, August 28, 2013 5:57 AM
  • Hi,

    I've figured things out. Apparently the error occurred at another part of the code. It's solved now.

    INSERT INTO REF.LocLog
    			VALUES ('+CONVERT(VARCHAR(100),@LocID)+','''+@schm+''', '+CONVERT(VARCHAR(10),1)+', '+CONVERT(VARCHAR(100),@CreatedBy)+', '+CONVERT(VARCHAR(100),@CreatedDate)+')
    			

    I need to put 3 apostrophes on either side of the +@schm+ in order for it to work.

    • Marked as answer by CY-L Wednesday, August 28, 2013 7:07 AM
    Wednesday, August 28, 2013 7:07 AM