May 26, 2010 at 2:24 am
Hi,
I am trying to insert into a temp table using Dynamic SQL and need to use CAST operator for displaying field values. The value for fields used in CAST are retrived from seperate SELECT stat as givn below.
Calculate values
SET @table = '#Temptable'
SELECT @TrackCount2 = SUM(Tracks) FROM #Temptable WHERE @Location = Location
SELECT @LongestTime2 = MAX(CONVERT(NVARCHAR,LongestTime,120)) FROM #TempMobSer WHERE @Location = Location
SELECT @AverageTime2 = convert(nvarchar,convert(datetime,avg(convert(real, convert(datetime,AverageTime))), 108 )) FROM #TempMobSer WHERE @Location = Location
Insert into table dynamically.
SET @sql = 'INSERT INTO ' + @table + ' VALUES(
'CAST(@Location AS NVARCHAR) + ' Total''
, ''''
, ''''
, ' + CAST(@TrackCount2 AS NVARCHAR) + '
, ''''
, ''''
, ' + CAST(@LongestTime2 AS NVARCHAR) + '
, ' + CAST(@AverageTime2 AS NVARCHAR)' )'
EXEC(@sql)
When executing the above I am getting the below error at the first CAST statement...
Incorrect syntax near 'CAST'.
Could someone assist me here please...
Thanks.
May 26, 2010 at 3:06 am
May 26, 2010 at 3:15 am
Thanks for the reply.
I edited the script to include '+' and edited code is..
SET @sql = 'INSERT INTO ' + @table + ' VALUES(
' + CAST(@Depot AS NVARCHAR) + ' Total''
, ''''
, ''''
, ' + CAST(@TrackCount2 AS NVARCHAR) + '
, ''''
, ''''
, ' + CAST(@LongestTime2 AS NVARCHAR) + '
, ' + CAST(@AverageTime2 AS NVARCHAR)' )'
After executing, now im getting the below error at last line..
Incorrect syntax near ' )'.
May 26, 2010 at 3:27 am
Does logic from the last response not point this out to you? I'm guessing: -
) + ' )'
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply