I have a stored procedure with a very simple query...  Accept an ID and perform and return a count based on that ID.  I have an identical SProc that does this on another table and does it correctly.  But another related table always returns 0, even though the SELECT statement itself is indeed returning a valid count.

Example Table Structure:
CREATE TABLE {databaseOwner}[{objectQualifier}wns_lightbox_image] (
[ImageId] INT IDENTITY(1,1) NOT NULL 
   CONSTRAINT [PK_{objectQualifier}wns_lightbox_image] PRIMARY KEY CLUSTERED ON [PRIMARY], 
[LightboxId] INT NOT NULL 
   CONSTRAINT [FK_{objectQualifier}wns_lightbox_image_LightboxId] FOREIGN KEY 
   REFERENCES {databaseOwner}[{objectQualifier}wns_lightbox] ([LightboxId]) ON DELETE CASCADE NOT FOR REPLICATION, 
[FileName] NVARCHAR(250) NOT NULL, 
[Title] NVARCHAR(50) NULL, 
[Description] NVARCHAR(500) NULL, 
[DisplayOrder] INT NULL 
);

Example Data:
INSERT INTO {databaseOwner}[{objectQualifier}wns_lightbox_image] ([LightboxId],[FileName],[Title],[Description],[DisplayOrder]) VALUES (3,N'DSCF3347.JPG',N'DSCF3347.JPG',N'',1);
INSERT INTO {databaseOwner}[{objectQualifier}wns_lightbox_image] ([LightboxId],[FileName],[Title],[Description],[DisplayOrder]) VALUES (3,N'DSCF3348.JPG',N'DSCF3348.JPG',N'',2);
INSERT INTO {databaseOwner}[{objectQualifier}wns_lightbox_image] ([LightboxId],[FileName],[Title],[Description],[DisplayOrder]) VALUES (3,N'DSCF3349.JPG',N'DSCF3349.JPG',N'',3);
INSERT INTO {databaseOwner}[{objectQualifier}wns_lightbox_image] ([LightboxId],[FileName],[Title],[Description],[DisplayOrder]) VALUES (3,N'DSCF3350.JPG',N'DSCF3350.JPG',N'',4);
INSERT INTO {databaseOwner}[{objectQualifier}wns_lightbox_image] ([LightboxId],[FileName],[Title],[Description],[DisplayOrder]) VALUES (4,N'IMG_20110609_171133.jpg',N'IMG_20110609_171133.jpg',N'',1);
INSERT INTO {databaseOwner}[{objectQualifier}wns_lightbox_image] ([LightboxId],[FileName],[Title],[Description],[DisplayOrder]) VALUES (4,N'IMG_20110609_171226.jpg',N'IMG_20110609_171226.jpg',N'',2);

Original Stored Procedure:
CREATE PROCEDURE {databaseOwner}[{objectQualifier}wns_lightbox_GetImageCount] 
@LightboxId INT 
AS 
BEGIN 
SET NOCOUNT ON;


SELECT COUNT(i.[ImageId]) 
FROM {databaseOwner}[{objectQualifier}wns_lightbox_image] i 
WHERE i.[LightboxId] = @LightboxId;
END
GO

Another I tried:
CREATE PROCEDURE {databaseOwner}[{objectQualifier}wns_lightbox_GetImageCount] 
@LightboxId INT 
AS 
BEGIN 
SET NOCOUNT ON;


DECLARE @ReturnValue INT;


SELECT @ReturnValue = COUNT(i.[ImageId]) 
FROM {databaseOwner}[{objectQualifier}wns_lightbox_image] i 
WHERE i.[LightboxId] = @LightboxId;


SELECT @ReturnValue;
END
GO

Any ideas why this is happening?

---

Will Strohl

Upendo Ventures DNN experts since 2003 Official provider of the Hotcakes Commerce Cloud and SLA support

I've tried your scripts and the first stored procedure works for me. So you could try what Cathal says or look at your database configuration.

you can also use select (1) from ... -- a constant, instead of (*) which feels like 'too much'.
Maybe it's the same, but I like '1' better. Like avoiding 'select *' when you don't need all the rows.

btw, but just in case verify you are referencing the right {databaseOwner}{objectQualifier} database objects.
Sometimes you have both dbo.something and blahabla.something objects at the same time. One table could be empty and the other not, that could be a reason for the count to fail even if you see rows (because they are on the 'other' table). Sometimes, this happens...