Ruben Lopez wrote:
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.
That's just it... It normally works. This is a very trivial query. :(
If it's a database configuration issue, this should happen to all similar queries, right? The same exact query in another SProc works fine - the only difference being that it's pointing to another table. It will returns a count of the table, based on the WHERE clause.
Cathal Connolly wrote:
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.
I use this method of count mostly for performance. There's less of a hit on the DB when using the PK field, because there aren't any additional look-ups required on the table. This may not still be true, as I first learned this during the SQL Server 2000 era. :)
Cathal Connolly wrote:
try using SELECT COUNT(i.[ImageId] is not null)
I've never tried this before, but it wouldn't execute. SQL Server was complaining to me, saying it wasn't valid... "Incorrect syntax near the keyword 'IS'."