asked on

Execute sql text in SQL Server 2005

The following code give an error
"Msg 2812, Level 16, State 62, Line 5
Could not find stored procedure 'select * from Lumbartest'."
Is it possible to execute code that has been placed in a varchar variable?

declare @cmd varchar(100)

set @cmd = 'select * from Lumbartest'
exec @cmd

ASKER CERTIFIED SOLUTION

Roger Alcindor

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Roger Alcindor

ASKER

I solved the issue myself

Guy Hengel [angelIII / a3]

or:

exec(@cmd)

Roger Alcindor

ASKER

Hello angellll,
Unfortunatelt your solution doesn't work.
The code below illustrates this, giving an error message
Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@testdata".

      declare @testdata float
      declare @testnum int
      declare @secondlabel char(20)
      declare @badgenum int
      declare @statement nvarchar(200)
      declare @dtnow datetime

      set @dtnow = GetDate()
      set @badgenum = 24
      set @secondlabel = 'secondlabel'
      set @testnum = 1
      set @testdata = 2468
      set @statement = 'update LumbarTest set test1result = @testdata,DateTimeTest1 = GetDate(),operator = @badgenum where Label2 like @secondlabel'

      print @statement
      if @testnum = 1
            exec (@statement)