EXEC (select... ) problem Help!

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!
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)
|||Here's an example on how to build a dynamic SQL string and run it with
EXECUTE or SP_EXECUTESQL stored procedure
http://www.dandyman.net/sql/samples/dynamicsql.txt
Dandy Weyn
[MCSE-MCSA-MCDBA-MCDST-MCT]
http://www.dandyman.net
Check my SQL Server Resource Pages at http://www.dandyman.net/sql
"Scagnetti" <Scagnetti@.discussions.microsoft.com> wrote in message
news:7363AFA0-2098-4014-9BEB-FFF6F5D697DC@.microsoft.com...
> 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!
sql