Executing Dynamic SQL Statement 1

[ninelgorb]

I have the following dynamic statement:

SET @sSQL =  'INSERT INTO ' + @Table + ' (sCalldate, sAgentid, sProject, sLogIn, sLogOut, sCallTime, dCallSec) ' + ' VALUES ( ''' +  @sCalldate + ''', ''' +  @cursorAgentId + ''', ''' +  rtrim(ltrim(@sProject)) + 'z' + ''', ''' +  @sStartTime + ''', ''' + @sEndTime  + ''', ''' + convert(char(8), convert(datetime, @sEndTime) - @sStartTime, 108)  + ''', ' + Convert(varchar(10),( datediff(ss, 0, convert(char(8), convert(datetime, @sEndTime) - @sStartTime, 108))) ) +  ')'

The actual statement looks like this:

INSERT INTO tmTaskTimeDetailsNonVoiceNet (sCalldate, sAgentid, sProject, sLogIn, sLogOut, sCallTime, dCallSec)  VALUES ( '20050714', '4358', 'jhtc0z', '10:00:00', '11:30:00', '01:30:00', 5400)

When I execute it manually the record gets inserted, but when I use EXEC @sSQL I get the following error:
"INSERT INTO tmTaskTimeDetailsNonVoiceNet (sCalldate, sAgentid, sProject, sLogIn, sLogOut, sCallTime, dCallSec) VALUES ('20050714', '4358', 'jhtc0z', '10:00:00', '11:30:00', '01:30:00', 5400)' is not a valid identifier."

What does this error mean? What am I doing wrong??

Thanks,
Ninel

 

[SQLDenis]

you need parentheses
exec (@sSQL ) not exec @sSQL

http://sqlservercode.blogspot.com/

 

[ninelgorb]

Thank you so much. I love it when it's such a simple fix.

 

[ninelgorb]

Can I select into a #TEMP table using dynamic sql?

For example:

SET @sSQL =  'SELECT * INTO #TEMP FROM ' + @sTable

EXEC (@sSQL)

I'm getting an invalid object name error when trying to select from #TEMP.

Thanks,
Ninel

 

[gmmastros]

You can create a temp table first, and then insert exec.

Ex.

Create Table #Temp(Field1 int, Field2 VarChar(20), etc..)

Insert Into #Temp
Exec (@sSQL)

You need to build the table structure for the #Temp table so that it matches the returned table structure of your dynamic SQL string.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom