Error message on SQL Server 2000

  • mick burden

    SSCarpal Tunnel

    Points: 4432

    Can someone help, I've been getting an error meesage when I execute this code

    declare @sDescription as varchar(1000)

    declare @sSkill as varchar(1000)

    declare @sFallback as varchar(1000)

    declare @swmskill as varchar(1000)

    declare @sql as varchar(5000)

    set @sDescription = 'crap2'

    set @sSkill = 'crap4'

    set @sFallback = 'crap1'

    set @swmskill = 'crap5'

     

    set @sql = ' INSERT INTO [TRC]  ([Description2],Skill, Fallback,wmskill) VALUES (' + @sDescription + ',' + @sSkill + ',' + @sFallback + ',' + @swmskill + ')'

    exec (@sql)

     

    The error message is :-

    Server: Msg 128, Level 15, State 1, Line 1

    The name 'crap2' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.

    can anyone help please

  • ganeshmuthuvelu

    SSCrazy

    Points: 2177

    If you had seen the @sql value just before executing it, you will know whats wrong.

    You have to have another single quote in front of each variable. Something like this:

    set @sql = ' INSERT INTO [TRC]  ([Description2],Skill, Fallback,wmskill) VALUES (' + '''' + @sDescription + '''' +  ',' + '''' +  @sSkill + '''' +  ',' +  '''' + @sFallback + '''' +  ',' + @swmskill + '''' + ')'

    Thanks,

    Ganesh

  • Ninja's_RGR'us

    SSC Guru

    Points: 294069

    Why are you using dynamic sql for this???

  • mick burden

    SSCarpal Tunnel

    Points: 4432

    What I posted wasn't the complete code, it was from a stored procedure which allowed me to specify the table name from vb code, I was testing it in the sql query analyzer, but to simplify debugging I hard coded the table name

  • mick burden

    SSCarpal Tunnel

    Points: 4432

    many thanks for the assistance, it works great now

  • Ninja's_RGR'us

    SSC Guru

    Points: 294069

    I strongly suggest you read this :

    The Curse and Blessings of Dynamic SQL

  • mick burden

    SSCarpal Tunnel

    Points: 4432

    thanks for the info, makes an interesting read

  • mick burden

    SSCarpal Tunnel

    Points: 4432

    in one of the reply's it said "If you had seen the @sql value just before executing it, you will know whats wrong." is there a way of "covering" the viable to actually see what it contains?

  • Ninja's_RGR'us

    SSC Guru

    Points: 294069

    print @VariableName

    then execute from QA

  • mick burden

    SSCarpal Tunnel

    Points: 4432

    thanks

Viewing 10 posts - 1 through 10 (of 10 total)

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