Post reply

Select CASE

  • November 30, 2005 at 12:41 pm

    #89982

    Is it possible to do the following using SELECT CASE:

    EXEC ('If NOT exists(Select * from NotificationTemplate_ Where OrganizationID_ = 2)

     Insert into NotificationTemplate_ (OrganizationID_, Subject_, Text_) Values(2, ''Subject'', ''Text'')

    Else

     Update NotificationTemplate_ Set Text_ = ''SomeText'', Subject_ = ''SomeSubject'' Where OrganizationID_ = 2')

    Thanks in Advance

  • November 30, 2005 at 12:54 pm

    #606848

    because you are using two distinct actions 'update' and 'insert' you will need to have separated statements at least until SQL Server implement Oracle UPSERT or IBM Merge

    by the way, why are you using dynamic sql for that ?

      


    * Noel

  • November 30, 2005 at 1:29 pm

    #606854

    Hi Noeld

    Thanks for the reply. I am using dynamic SQL because our developer asked me if this operation is possible from .net application by touching the db layer only once.

    -Gary

     

  • November 30, 2005 at 1:30 pm

    #606855

    why not create a stored procedure then ?!?!?!







    **ASCII stupid question, get a stupid ANSI !!!**

  • November 30, 2005 at 1:31 pm

    #606857

    Yeah, I wanted that but Architects want to implement their own persistence layer and they are against Stored procedures.

  • November 30, 2005 at 1:44 pm

    #606860

    And they call themselves "Architects" ... Would they preffer dynamic SQL ?

    You can still send the batch in one roundtrip but SP is the key


    * Noel

  • November 30, 2005 at 1:53 pm

    #606863

    CREATE PROCEDUERE dbo.<Name>

    @Text nvarchar(1000), @Subject nvarchar(50), @OrgId int

     Update NotificationTemplate

     Set Text = @Text,

     Subject = @Subject

     Where OrganizationID = @OrgId

     Insert into dbo.NotificationTemplate (OrganizationID, Subject, Text)

    SELECT @OrgId,  @Subject, @Text

    WHERE NOT EXISTS (select 1 from dbo.NotificationTemplate where OrganizationID = @OrgId)

    GO

    EXECUTE dbo.<Name> "SomeText", "Object", 2

    --------------

    And fire your "Architects" immediately. At least don't let them design SQL scripts.

    And hire one good SQL developer. Who read at least one book about relational databases.

    _____________
    Code for TallyGenerator

  • November 30, 2005 at 2:00 pm

    #606865

    Hey Noel - Gagandeep didn't say they call themselves "good" architects...

    Dim sSQL As String
sSQL = 'IF NOT EXISTS(SELECT * from NotificationTemplate WHERE OrganizationID = 2)
INSERT INTO NotificationTemplate(OrganizationID, Subject, Text) Values(2, 'Subject', 'Text') 
ELSE UPDATE NotificationTemplate Set Text = 'SomeText', Subject = 'SomeSubject' WHERE OrganizationID = 2'

    I'm a little curious - why does your table name and all field names end with an underscore - is it a typo (cut & paste)

    or is that a specific naming convention..







    **ASCII stupid question, get a stupid ANSI !!!**

  • November 30, 2005 at 2:09 pm

    #606870

    hehe good! point sushila but I usually assume that to be a software architect you should be good. Apparently I am wrong

    I would use something like what sergyi posted and add a @@rowcount check between the Update and the insert to optimize the proc.

     

     


    * Noel

  • November 30, 2005 at 2:11 pm

    #606872

    Hey Guys

    I agree from the database performance and scalability point of view that stored procedures are the way to go, but I have no say in that. Table names and column names with (_) is also one of their inventions in which I have no say. Anyway, Thanks for all your replies.

    Gary

  • November 30, 2005 at 2:14 pm

    #606873

    Have you not been listening ?!?! The "Architects" are "against Stored procedures"....







    **ASCII stupid question, get a stupid ANSI !!!**

  • November 30, 2005 at 2:19 pm

    #606877

    Gary,

    Two stored procedures for your consideration:

    exec sp_update_resume

    exec sp_distribute_resume

    Does not looks good after what you just said ( you should take care of your self )

    Cheers,


    * Noel

  • November 30, 2005 at 2:22 pm

    #606879

    Good heavens! I know this sounds facetious but I'm in dead earnest when I say that people like these should be behind bars - at least they shouldn't be anywhere near databases..







    **ASCII stupid question, get a stupid ANSI !!!**

  • December 1, 2005 at 5:46 am

    #607007

    The only 'good' thing about people as clueless as this, is that they do provide for a neverending source of work-opportunities for those not-so-clueless...

    /Kenneth

  • December 1, 2005 at 9:56 am

    #607121

    Excellent point Kenneth


    * Noel

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply