Select CASE
-
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
-
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 -
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
-
why not create a stored procedure then ?!?!?!
**ASCII stupid question, get a stupid ANSI !!!** -
Yeah, I wanted that but Architects want to implement their own persistence layer and they are against Stored procedures.
-
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 -
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 -
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 !!!** -
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 -
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
-
Have you not been listening ?!?! The "Architects" are "against Stored procedures"....
**ASCII stupid question, get a stupid ANSI !!!** -
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 -
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 !!!** -
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
-
Excellent point Kenneth
* Noel
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply