Dynamic field in SQL Server Express

Question

So I've found myself with a need to have a field in my table that is a count of all the 'Shops' in another table. The table should therefore consist of Centre_Name, Location and Shop_Count. I've already created the table, so how do I add such a field?

The field should also obviously update when a shop is added or deleted in the other table. Also, each shop has a corresponding Centre_Name.

The table is in a SQL Server Express database, linked to my MVC 4 project in Visual Studio.

Thanks!

score 2 · Answer 1 · edited May 23 '17 at 12:28

It's pretty heavy denormalization, but you could make a function to get the count and then use that function as part of a computed column. See: define a computed column reference another table

So you'd want to do:

CREATE FUNCTION dbo.CountShops (@Centre_Name VARCHAR[x])
RETURNS INT 
AS BEGIN
    DECLARE @ShopCount INT

    SELECT @ShopCount = COUNT(*) FROM dbo.Shops WHERE Centre_Name = @Centre_Name

    RETURN @ShopCount
END

And then call that as part of your column:

ALTER TABLE dbo.Shops
ADD Shop_Count AS dbo.CountShops(Centre_Name)

...this is assuming Centre_Name is the defining attribute of what you're counting shops by. What are you counting shops by? If it's just counting rows in the shops table, you could drop the parameter and do:

CREATE FUNCTION dbo.CountShops ()
    RETURNS INT 
    AS BEGIN
        DECLARE @ShopCount INT

        SELECT @ShopCount = COUNT(*) FROM dbo.Shops

        RETURN @ShopCount
    END

Dynamic field in SQL Server Express

1 Answers1