December 4, 2014 at 9:48 am
I have a stored procedure on a SQL Server 2008 database. The stored procedure is very simple, just a SELECT statement. When I run it, it returns 422 rows. However, when I run the SELECT statement from the stored procedure, it returns 467 rows. I've tried this by running both the stored procedure and the SELECT statement in the same SSMS window at the same time, and the behavior is the same. The stored procedure is:
USE [REMS]
GO
/****** Object: StoredProcedure [mobile].[GetAllMobileDeviceUsers] Script Date: 12/04/2014 */
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [mobile].[GetAllMobileDeviceUsers]
AS
SET NOCOUNT ON
SELECT
ee.EmployeeID,
EmployeeName = LastName + ', ' + FirstName
FROM EmployeeInvData ee
--UNION
--SELECT
--m.EmployeeID,
--EmployeeName = LastName + ', ' + FirstName
--FROM mobile.MiscPersonnel m
INNER JOIN Employee e
ON ee.EmployeeID = e.EmployeeID
UNION
SELECT
'-1',
'- Select An Employee -'
ORDER BY EmployeeName
When I do this in the same SSMS window:
exec mobile.GetAllMobileDeviceUsers
SELECT
ee.EmployeeID,
EmployeeName = LastName + ', ' + FirstName
FROM EmployeeInvData ee
--UNION
--SELECT
--m.EmployeeID,
--EmployeeName = LastName + ', ' + FirstName
--FROM mobile.MiscPersonnel m
INNER JOIN Employee e
ON ee.EmployeeID = e.EmployeeID
UNION
SELECT
'-1',
'- Select An Employee -'
ORDER BY EmployeeName
I get two result sets. The first is 422 rows; the second is 467 rows. Why?
December 4, 2014 at 10:02 am
I got the answer (from StackOverFlow, ahem). I need to fully qualify my object names. We have a mobile.Employee table and a dbo.Employee table. Three of us were staring at this and didn't see it.
December 4, 2014 at 12:03 pm
Glad to hear you got the answer.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 5, 2014 at 3:10 am
Melanie Peterson (12/4/2014)
I got the answer (from StackOverFlow, ahem). I need to fully qualify my object names. We have a mobile.Employee table and a dbo.Employee table. Three of us were staring at this and didn't see it.
Could you please clarify ,Do you mean to say that SP execution was using Mobile schema and the simple Select query was using dbo schema ?
December 5, 2014 at 7:50 am
Yes, that's exactly what I mean.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply