Post reply

dynamic sql

  • October 9, 2006 at 8:38 am

    #114726

    I run this dynamic SQL:

    DECLARE @DB varchar(25)

    SET @DB = 'pmidba'

    DECLARE @Tablename varchar(50)

    SET @TableName = 'test'

    DECLARE @strSQL varchar(8000)

    SET @strSQL = 'Create table '+@DB+'.dbo.'+@TableName+' ([clmnt_idno] varchar (255) NULL)'

    EXEC @strSQL

    and get this:

    Server: Msg 203, Level 16, State 2, Line 7

    The name 'Create table pmidba.dbo.test ([clmnt_idno] varchar (255) NULL)' is not a valid identifier.

    What am I doing wrong?

  • October 9, 2006 at 8:51 am

    #664825

    never mind...forgot the parens around @strSQL...

  • October 10, 2006 at 2:05 am

    #664935

    I thought it was

    exec sp_executesql @strSQL

  • October 10, 2006 at 2:57 am

    #664940

    DECLARE @DB varchar(25)

    SET @DB = 'tempdb'

    DECLARE @Tablename varchar(50)

    SET @TableName = 'test12'

    DECLARE @strSQL nvarchar(4000)

    SET @strSQL = 'Create table '+@DB+'.dbo.'+@TableName+' ([clmnt_idno] varchar (255) NULL)'

    exec sp_executesql @strSQL

     

    This may solve ur problem.

    Thanks & Regards,
    9989069383
    Katakam.

  • October 10, 2006 at 6:50 am

    #664985

    If you use the sp_executesql stored procedure, then yes, that will work too. Need to be careful with dynamic SQL as it's not for every situation.  See link: http://www.sommarskog.se/dynamic_sql.html 

  • October 10, 2006 at 8:19 am

    #665015

    ...and if any of your variables can be populated by user data you should take steps to prevent potential career altering SQL injections...


    PeteK
    I have CDO. It's like OCD but all the letters are in alphabetical order... as they should be.

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

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