Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696

    Unanswered: one vs. the other

    Can anyone tell me why:

    This is better
    select * from sys.sysconstraints where object_name(id) = 'LP_CS' and
    object_name(constid) = 'LP_CS_PERIOD_VALUE'
    IF (@@ROWCOUNT>0)
    BEGIN
    SET @sql = 'ALTER TABLE LP_CS DROP CONSTRAINT LP_CS_PERIOD_VALUE'
    PRINT @SQL
    EXEC sp_executesql @SQL
    END

    Than this ?
    if (select 1 from sys.sysconstraints where object_name(id) = 'LP_CS' and
    object_name(constid) = 'LP_CS_PERIOD_VALUE') is not null
    BEGIN
    SET @sql= 'ALTER TABLE LP_CS DROP CONSTRAINT LP_CS_PERIOD_VALUE'
    PRINT @SQL
    EXEC sp_executesql @SQL
    END

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    What is your criteria for "betterness" in your evaluation?

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Can I say neither?

    Why are you using Dynamic SQL?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Outside of the D-SQL crappola, it was stated to me that the first is "better" because sys.sysconstraints is a view. I was like, huh?, I don't get it.

    select * from sys.sysconstraints where object_name(id) = 'LP_CS' and
    object_name(constid) = 'LP_CS_PERIOD_VALUE'
    IF (@@ROWCOUNT>0) ...

    Than this ?
    if (select 1 from sys.sysconstraints where object_name(id) = 'LP_CS' and
    object_name(constid) = 'LP_CS_PERIOD_VALUE') is not null ...

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I wouldn't use the first because it returns an uneccessary dataset to the client.
    I would start with the second, but make changes.

    For one, I'd use EXISTS instead of a NULL comparison because you're using a subquery which can (ok, it won't in your case) return more than one record, causing your statement to throw an error.

    Further to this, I would not use the Object_Name() function, instead I would use the Object_ID() one on the literal value. We should avoid putting functions on columns in the WHERE clause wherever possible. Also, don't forget to qualify your object names with their schema (e.g. dbo)!!

    Lastly, do away with your dynamic SQL, there's no need for it!
    Code:
    IF EXISTS ( SELECT NULL FROM sys.sysconstraints WHERE id = Object_ID('dbo.LP_CS') AND constid = Object_ID('LP_CS_PERIOD_VALUE') )
      BEGIN
        ALTER TABLE dbo.LP_CS
          DROP CONSTRAINT LP_CS_PERIOD_VALUE
      END
    George
    Home | Blog

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    George - agree with everything except that if the table exists and that constraint does not the statement will not compile (deferred name resolution). As such, this is not "D-SQL Crappola" - it is necessary Dynamic SQL.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Actually scratch that - apols. The above applies to columns, not constraints.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Funny, I had the second originally, and the client's DBA did not like it because it was going against a system view. I was like what ? okay.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •