| Hugo Kornelis 2005-08-06, 1:23 pm |
| 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)
|