Post reply

dynamic sql problem

  • May 6, 2005 at 5:35 am

    #76301

    Hi All,

       When i execute the below piece of code in query analyzer its throwing error

     declare @vtbl table (colone int)

     declare @strsql varchar(1000)

     set @strsql='insert into @vtbl values (15)'

     execute (@strsql)

     select * from @vtbl

    Error:

    Server: Msg 137, Level 15, State 2, Line 1

    Must declare the variable '@vtbl'.

    Any ideas ?

     

  • May 6, 2005 at 5:41 am

    #557311

    Try

    set @strsql='insert into ' + @vtbl + ' values (15)'



    Good Hunting!

    AJ Ahrens


    webmaster@kritter.net

  • May 6, 2005 at 5:45 am

    #557312

    It's a scope issue. The variable only exists at the outer level (where it was declared) so when the dynamic SQL executes, the table variable is not visible.

    See BoL under 'Transact-SQL Variables'

    As you code stands, there's no need to use dynamic SQL.

    What are you trying to do?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • May 6, 2005 at 5:49 am

    #557314

    Don't think that's going to work, since @vtbl is a table type variable. If it was a string would be fine.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • May 6, 2005 at 6:06 am

    #557316

    Hi,

       i tried the otherway too..ie @strsql='insert into'+@vtbl+'values (15)'

       Yes..ofcourse this is just a sample piece..but i have a scenario where i need to take a  variable of table type and i need to build dynamic insert statement becoz the values i will know only at runtime...

     

    nsr

  • May 6, 2005 at 6:20 am

    #557321

    Why do you need a dynamic statement? I assume you're doing this in a stored proc, if not, adjust or whatever...

    I'm also assuming that you know the structure of the table at development time

    CREATE PROCEDURE InsertIntoTable @var1 type, @var2 type, ... @varN type

    AS

    DECLARE @vtbl TABLE (field1 TYPE, field2 TYPE...fieldN TYPE)

    INSERT INTO @vtbl ([fieldList]) VALUES (@var1, @var2, @var3,...@varN)

    SELECT * FROM @vtbl

    I hope I'm not been too simpistic here, if so please post more details of your problem and I'll help in any way I can.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

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

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