November 30, 2010 at 12:50 pm
Our company stores temporary/non employees is a seperate table from regular employees and gives them negative employee numbers to distinguish them from regular employees whose employee numbers are positive. I'm trying to write a stored procedure that will allow me to get employee details (custom view) for a non employee by passing in the negative employee number as an INT parameter. If I take the SELECT statement that I wrote and run it outside the stored procedure it works fine and returns the desired view record - but I execute the stored procedure (in SSMS) and set the parameter value as the negative employee number it does not work, nothing is returned. I'm sure I'm missing something simple here so any assistance would be much appreciated.
November 30, 2010 at 1:01 pm
I'd have to at least see the code for the stored proc before I'd be able to begin helping on this.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 30, 2010 at 1:05 pm
CREATE PROCEDURE [dbo].[GetNonEmployeeByEmpID]
-- Add the parameters for the stored procedure here
@empID int,
@active int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT *
FROM
dbo.vw_ceo_NonEmployees
WHERE
EmpID = @empID
AND
((Active = 0) OR
(Active = @active))
END
November 30, 2010 at 1:09 pm
Is it possible the @Active parameter is interfering with the query? What did you set that to when you ran the query outside the proc?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 30, 2010 at 1:09 pm
can you send an example of how you are executing the proc?
Also how you actually execute the SELECT manually might point out the problem. I'm curious if the @Active parameter is being passed the appropriate value.
--------------------------------------------------------------------------
When you realize you've dug yourself into a hole....Step 1...stop digging.
November 30, 2010 at 1:11 pm
Duh! I screwed up the "Active" part of the WHERE clause - it should be ((@active = 0) OR (Active = @active)) - that is the problem. Thanks for the extra pair of eyes!
December 1, 2010 at 6:38 am
You're welcome.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply