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

Exception Handling

Hi All

I m new to the sqlserver.In Oracle we can handle exceptions like this

declare
name varchar(20);
begin
select ename into name from emp where eno=&eno;
dbms_output.put_line(name);
exception
when no_data_found then
dbms_output.put_line('No Entry');
end;
/

We will get the message No Entry When corrsponding employee number dosent exists.

In Sqlserver how to handle these things.

Specifically my requirement is with select statement(Like above program)
Waiting for valuable suggestions

Baba

You can do in sqlserver using @.@.Rowcount

select ename into name from emp where eno=&eno

if((select @.@.Rowcount)<= 0)

print 'No data'

The following explains the errorhandling in simple terms

http://www.sqlteam.com/article/handling-errors-in-stored-procedures

|||

In SQL Server this situation never treated as exception, There is a workaround available - here it is,

Code Snippet

Declare @.Sno as int;

Declare @.name as varchar(100)

Set @.Sno=4

Select @.name=name from sysobjects Where id=@.Sno

If @.@.RowCount = 0

Print 'No Entry'

Else

Print @.Name

|||Hi manivannan

I tried like this before only . I got the output

Thank u for u r valuable reply.

Baba

EXCEPT not working

TIA. Here is my situation: I have two tables that I need to find the perform an EXCEPT op on.

Table1: ToBeAddedCodes
CodeID - varchar(14)

Table2: ExistingCodes
ExistingCodeID - varchar(14)
DateIssued - datetime
Active - bit
...&c

I perform the following command, to no avail:

select CodeID
from ToBeAddedCodes
intersect
select ExistingCodeID
from ExistingCodes

Specifically, the following error appears:

Msg 156, Level 15, State 1, Line 40
Incorrect syntax near the keyword 'intersect'.

I don't understand what the issue is... Please help. Thanks.

The syntax should be valid if you are on SQL Server 2005, any version prior 2005 won′t support the Intersect keyword. I think thats your problem. Seems that you are connected to a SQL Server 2000 instance.

HTH, Jens SUessmeyer.

-
http://www.sqlserver2005.de
-

|||Or the database is running in 8.0 compatibility level.|||The new set operators will work in 80 and other compatibility modes also. So that is not the problem. User is either running on a older version of SQL Server or old CTP releases of SQL Server 2005. The set operators EXCEPT/INTERSECT was added late in the development cycle only.|||Please post the version of SQL Server (@.@.version) that you are running this code against.|||

Thanks for the replies ppl. Here's the requested information:

Information obtained from Help/About (about indicates "MS SQL Server 2005":

Microsoft SQL Server Management Studio 9.00.1399.00
Microsoft Analysis Services Client Tools 2005.090.1399.00
Microsoft Data Access Components (MDAC) 2000.085.1117.00 (xpsp_sp2_rtm.040803-2158)
Microsoft MSXML 2.6 3.0 5.0 6.0
Microsoft Internet Explorer 6.0.2900.2180
Microsoft .NET Framework 2.0.50727.42
Operating System 5.1.2600

Information obtained from "select @.@.version":

Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

|||

>> Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation

>> Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

The server version is 8.* which means it is SQL Server 2000. You are running SQL Server Management Studio though which ships with SQL Server 2005. The client doesn't have anything to do with the server language features. So you need to create your tables on a SQL Server 2005 server and try the EXCEPT query.

|||Did you happen to install SQL Server 2005 on a machine with an existing SQL Server 2000 installed? You might have mistaken the installation to be an upgrade (just like what I did a couple of months back )|||Well, I have no control over the infrastructure or anything in fact... I am just stepping in with the .NET development. Unfortunately, it does seem as if they are a bit out of date. Yes, on my client machine I am using 2005 but the database is in 2000.