Contents tagged with SQL

  • Find stored procedures that reference a table column

    1 Comment

    We recently moved away from SQL Server replication and the database still has all of the rowguid columns and their associated indexes and constraints in it. We wanted to gain all of that disk space back so we went ahead with scripting out the delete of the indexes, constraints, and columns.

    One thing though is that there are many stored procedures in use and we needed to make sure none of these referenced the rowguid column.  The stored procedures really should not have used the rowguid column at all since it is solely created and used for SQL Replication, but you never know so we needed to confirm.

    The below script is what I created to search all of the stored procedures in the database for the rowguid column.  It could easily be modified to find any information within the stored procedures of a database.  (Note: One thing to watch out for is encrypted stored procedures.  This wouldn’t find anything in those so you would need to handle it through another method.)

    SELECT p.name, c.text FROM syscomments c
    JOIN sys.procedures p ON p.object_id=c.id
    WHERE c.text LIKE '%rowguid%'
    ORDER BY p.name

  • How to implement ISNULL(SUM(ColumnName),0) in Linq2SQL

    6 Comments

    I had a Linq2Sql statement like this:

    var q = from o in db.OrderItem
                      where o.UserId == UserId
                      select o.Amount;

    And then I was trying to get the sum of the OrderItem.Amount column by doing this:

    total = q.Sum();

    This worked fine as long as there were rows for this UserId in the OrderItem table.  But if there are no rows in the table for this UserId I was getting this exception:

    System.InvalidOperationException: The null value cannot be assigned to a member with type System.Decimal which is a non-nullable value type.

    Linq2Sql was creating the following SQL query:

    SELECT SUM([t0].[Amount]) AS [value]
    FROM [dbo].[OrderItem] AS [t0]
    WHERE [t0].[UserId] = @p0

    which is fine but I really want this:

    SELECT ISNULL(SUM([t0].[Amount]),0) AS [value]
    FROM [dbo].[OrderItem] AS [t0]
    WHERE [t0].[UserId] = @p0

    Notice the ISNULL in the statement above that will return 0 if there are no rows found for this UserId.

    Eventually I was able to track it down to var q being an IQueryable in the Linq2Sql statement above.  By using ToArray() like this:

    var q = (from o in db.OrderItem
                      where o.UserId == UserId
                      select o.Amount).ToArray();
    total = q.Sum();

    var q is now an IEnumerable (because ToArray() returns an IEnumerable<Decimal>).  And instead of IQueryable.Sum() trying to apply an expression to the Null value,   IEnumerable.Sum() will just loop through all of the items in the array and sum the values.  In this case there are no items in the array so 0 is returned. 

    I could have also explicitly declared q as an IEnumerable<Decimal>:

    IEnumerable<Decimal> q = from o in db.OrderItem
                      where o.UserId == UserId
                      select o.Amount;
    total = q.Sum();

    Just to note, the SQL statement is still not using ISNULL() but I am getting the result I want from within C#.

     

    UPDATE (2010-07-22):  Another solution from RichardD:

    var q = from o in db.OrderItem 
              where o.UserId == UserId 
              select o.Amount;
    Decimal total = q.Sum(a => (Decimal?)a) ?? 0M;     

    OR 

    IQueryable<Decimal?> q = from o in db.OrderItem
                      where o.UserId == UserId
                      select (Decimal?)o.Amount;
    Decimal total = q.Sum() ?? 0;     

     

     

  • ISO 3166-1 Country Data SQL Script

    3 Comments

    I am creating a standard sign up form with one of the fields being a country drop down. So I created my Country database table and then needed to fill it with information. I found the ISO list of countries here, but then the issue was how to get that list into my Country database table. Luckily someone has already done that for me: http://vidmar.net/weblog/archive/2008/05/23/database-of-country-names-numeric-alpha-2-and-alpha-3-iso-codes.aspx

    But I also like to be able to check a script like that into source control to be able to track changes to it. So I updated the insert script to be a rerunnable insert/update script.  The script will check if a country id exists and if it does not exist, then it will insert the country otherwise it will just do an update. The insert sql script from http://vidmar.net/weblog/archive/2008/05/23/database-of-country-names-numeric-alpha-2-and-alpha-3-iso-codes.aspx really got me most of the way there so I need to give a lot of credit to him.

    The link below will get you my updated version of the country data update sql script. I also updated it to 2010 from the ISO 3166 code lists plus any changes that they have posted so it is up to date as of May 15, 2010. I will try (but no promises) to keep it updated on a regular basis. Feel free to contact me if you want to try and keep it updated for me and I will post it here.

    Click here for the country data update sql script (up-to-date as of May 15, 2010).

    You can use this sql to create the Country table:

    CREATE TABLE [dbo].[Country]
        (
            CountryId int NOT NULL,
            Iso2 char(2) NOT NULL,
            Iso3 char(3) NOT NULL,
            Name nvarchar(64) NOT NULL,
            DateCreated datetimeoffset(7) NOT NULL CONSTRAINT [df__Country__DateCreated] DEFAULT (sysdatetimeoffset()),
            DateModified datetimeoffset(7) NOT NULL  CONSTRAINT [df__Country__DateModified] DEFAULT (sysdatetimeoffset())
        ) 

    ALTER TABLE dbo.Country ADD CONSTRAINT
        pk__Country__CountryId PRIMARY KEY CLUSTERED
        (
            CountryId ASC
        ) WITH FILLFACTOR = 100 ON [PRIMARY]