this seems really simple but...

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

this seems really simple but...

Author

Topic

kiddoOnSQL
Starting Member

16 Posts

Posted - 2011-04-11 : 20:21:09

Hi guys

I am sending parameters to a web service from my .net application, that isnt returning the values I expect.
So I started executing each of the stored procs that the web service uses internally to process the request. One of the stored procs is this one below:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create Procedure [dbo].[BookingWebService_EmployeeExists]
@EmployeeNo varchar(50)
AS
SELECT count(*)
FROM Employee
where (EmployeeNo = @EmployeeNo)

GO

Now when I execute the stored proc using my SQL client and pass my employee code as a parameter, it returns 2 results - the first one has 'No Column Name' in the column header and has a value of 1. The second one below it has 'Return Value' as the column name, but has the value 0.
Now 0 is the value the web service keeps returning because of which I cant proceed to the next step.
The web service is not something I have programmed - it is by someone else and is in production already.

My employee definitely exists, because when I use the following sql statement "select * from Employee where EmployeeNo='TTBAKK'" it returns my employee record to me.

Why do you think I get the 0 value when I execute the stored proc even though I have the employee? Why does it return 2 values - a 1 followed by a 0?

Thanks in advance.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-04-11 : 20:27:13

Add SET NOCOUNT ON after AS. Then put COUNT(*) into a variable and use that in an OUTPUT variable.

Like this:

create Procedure [dbo].[BookingWebService_EmployeeExists]
(@EmployeeNo varchar(50), @cnt int OUTPUT)
AS
SET NOCOUNT ON

SELECT @cnt = count(*)
FROM Employee
where EmployeeNo = @EmployeeNo

GO

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

kiddoOnSQL
Starting Member

16 Posts

Posted - 2011-04-12 : 00:29:23

Thanks for that. I still get similar results though. When I execute the stored proc and pass emp number as the paramter, I get a SQL query window opened - that window has the following statements automatically entered into it :

DECLARE @return_value int,
@cnt int

EXEC @return_value = [dbo].[BookingWebService_EmployeeExists2]
@EmployeeNo = N'TTBAKK',
@cnt = @cnt OUTPUT

SELECT @cnt as N'@cnt'

SELECT 'Return Value' = @return_value

GO

The results windows below this are split into 2 windows - the first one has column name as '@cnt' and value 1 and the 2nd window has column name 'Return Value' as value as 0.

McDebil
Starting Member

23 Posts

Posted - 2011-04-12 : 03:36:06

Remove the usage of return value, use the output param only:

DECLARE @cnt int

EXEC [dbo].[BookingWebService_EmployeeExists2]
@EmployeeNo = N'TTBAKK',
@cnt = @cnt OUTPUT

SELECT @cnt as N'@cnt'

McDebil

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2011-04-13 : 11:49:37

If employeeNo is primary key and the procedure is expected just to check existence, from performance point of view next code would be better:

create Procedure [dbo].[BookingWebService_EmployeeExists]
@EmployeeNo	 varchar(50)
AS
if exists(select * FROM Employee where EmployeeNo = @EmployeeNo) return 1
else return 0

Mirko

My blog: http://mirko-marovic-eng.blogspot.com/

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-04-13 : 12:19:58

The kludge is to add "Add SET NOCOUNT ON;" in your code.
But the better question is "why do this at all?" The answer is that your mindset is still locked in procedural code and not yet in SQL. When you can handle declarative coding, you will simply write a predicate:

EXISTS (SELECT *
FROM Personnel
WHERE emp_nbr = @in_emp_nbr)

Now use it where you need it and let the optimizer do its job.

Notice that the table name is a collective noun in my repair job; tables are sets. Notice teh parameter has an attribute role prefixed to its base data element.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

kiddoOnSQL
Starting Member

16 Posts

Posted - 2011-04-14 : 20:59:51

Thank you all for your responses

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources