Re: EXEC (select... ) problem Help!

On Sat, 6 Aug 2005 08:20:03 -0700, Scagnetti wrote:

>How can I get this to work?
>declare @WkEmpID
>declare @sql varchar(1000)
>
>set @sql = 'select distinct @WkEmpID = EmpID from Employee'
>exec (@sql)
>
>Now this is a simplified version of a more comples query which is forcing me
>to use this method rather than a simple SQL query. The big problem is getting
>the 'EmpID' value into the local variable of '@WkEmpID'. Please help!

Hi Scagnetti,

First: do try to solve this without dynamic SQL. There are often other
ways to get the same result. Consider posting your real problem here;
maybe someone sees a solution you didn't think of. For a discussion and
the pro's and (mainly) con's of dynamic SQL, visit Erland Sommarskog's
site: http://www.sommarskog.se/dynamic_sql.html.

The answer to your question is to use the stored procedure sp_executesql
instead of exec (@sql). Details in Books Online (and on Erland's page).
But as I said - doing it without dynamic SQL is the best solution.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
.