I have a section of a stored procedure that needs to run a dynamic
select statement then assign the results of this statement to a
variable for use later in the SProc.  When I use the following code,
it errors with: "Incorrect syntax near the keyword 'EXEC'."  How am
incorrectly using EXEC?  (All variables are declared and if I "PRINT
@tempSQL", the dynamic sql displays correctly.)

-- build dynamic SELECT statement
SET @tempSQL = 'SELECT TOP 1 '
SET @tempSQL = @tempSQL + @srcTable
SET @tempSQL = @tempSQL + '.[part number] FROM '
SET @tempSQL = @tempSQL + @srcTable
SET @tempSQL = @tempSQL + ' LEFT OUTER JOIN products ON '
SET @tempSQL = @tempSQL + @srcTable
SET @tempSQL = @tempSQL + '.[part number] = products.pother1 WHERE
(products.ccode IS NULL)'

SET @tempResult = EXEC @tempSQL

dear garthbrown-ga,
i have tried to solve your question in the following way.
Use buit-in stored procedure "sp_executesql" to execute the dynamic
query which takes different parameters first one is the query string
itself, second is the variable declaration that you pass into the
query,and third is the value of the parameter u passed.Take this
result value in desired variable like @tempResult. I think it will
solve your problem and answers the question.

declare @str as nvarchar(100)
declare @tempResult as nvarchar(100)

set @str=N'select @n=Name from Patient Where PIN=1'

exec sp_executesql @str,N'@n as varchar(20) output',@n=@tempResult output

select @tempResult

After reading your answer about 10 times, it finally clicked!  I
should have read it early in the morning and not late at night :)

My SProc is now working great without all the temp tables everyone
else suggested.  Thanks for the help!  How do we get you paid?