Products

Solutions

Resources

Partners

Community

About

New Community Website

Ordinarily, you'd be at the right spot, but we've recently launched a brand new community website... For the community, by the community.

Yay... Take Me to the Community!

Welcome to the DNN Community Forums, your preferred source of online community support for all things related to DNN.
In order to participate you must be a registered DNNizen

HomeHomeDevelopment and...Development and...SQL and SQL Ser...SQL and SQL Ser..."Count" Stored Procedure Always Returns 0"Count" Stored Procedure Always Returns 0
Previous
 
Next
New Post
7/15/2011 2:40 PM
 
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 Upendo Ventures
DNN experts since 2003
Official provider of the Hotcakes Commerce Cloud and SLA support
 
New Post
7/15/2011 2:51 PM
 
you can use select count(*) as you're constraining the list via your where clause - take care using non count(*) functions as it's not always obvious e.g. listing a column gives a count of the unique values (which may not always be a count of the rows if some rows share the same values - which isn't the case with an identity column which is why you'll sometimes see that usage) - see http://msdn.microsoft.com/en-us/library/ms175997.aspx for the correct ALL and DISTINCT usages also.

Buy the new Professional DNN7: Open Source .NET CMS Platform book Amazon US
 
New Post
7/15/2011 3:01 PM
 
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.
 
New Post
7/15/2011 3:06 PM
 
sorry if I wasn't being clear, but your script should work as is -though as you're just using a column name and not a full expression there is a risk that a later version of sql might not work so perhaps you're seeing that e.g. try using SELECT COUNT(i.[ImageId] is not null)

Buy the new Professional DNN7: Open Source .NET CMS Platform book Amazon US
 
New Post
7/15/2011 3:39 PM
 
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...
 
Previous
 
Next
HomeHomeDevelopment and...Development and...SQL and SQL Ser...SQL and SQL Ser..."Count" Stored Procedure Always Returns 0"Count" Stored Procedure Always Returns 0


These Forums are dedicated to discussion of DNN Platform and Evoq Solutions.

For the benefit of the community and to protect the integrity of the ecosystem, please observe the following posting guidelines:

  1. No Advertising. This includes promotion of commercial and non-commercial products or services which are not directly related to DNN.
  2. No vendor trolling / poaching. If someone posts about a vendor issue, allow the vendor or other customers to respond. Any post that looks like trolling / poaching will be removed.
  3. Discussion or promotion of DNN Platform product releases under a different brand name are strictly prohibited.
  4. No Flaming or Trolling.
  5. No Profanity, Racism, or Prejudice.
  6. Site Moderators have the final word on approving / removing a thread or post or comment.
  7. English language posting only, please.
What is Liquid Content?
Find Out
What is Liquid Content?
Find Out
What is Liquid Content?
Find Out