May 16, 2011 at 11:58 am
Hello all,
I am inserting records into a Table "VersionTbl".
Table Definition
CREATE TABLE dbo.VersionTbl
(VersionTbl int not null Identity(1,1),
VersionName [nvarchar](200) NULL,
VersionNumber DECIMAL(2,1) NOT NULL,
)
VerisonName and VersionNumber together UNIQUE KEY.
I wrote a SP to insert the data into this Table. with 2 input parameters VersionNumber and VersionName
The Question is ::
If I execute the SP with VersionNumber= 1.0, VersionName= "First Version" It has to execute normally.
If I execute the SP again with the same parameters; It has to give a message saying that " this version already exists. If you want to overwrite the Version Please execute Again "
If I press "Execute" with the same parameters again It has to execute and overwrite the Version 1.0 and VersionName "FirstVersion".
It is kind of asking the user to "Overwrite or not".
How can I achieve this in a SP ??
Please help , Thanks in advance
May 16, 2011 at 12:48 pm
That is a really bizarre requirement. If the table you showed us is the whole table what does it matter if you delete it and recreate it? Wouldn't you loose your foreign keys to this table? I just can't figure out why you would want to see if a record exists, if it does, don't allow the insert. If they try again to delete the original and insert an exact copy with a new key (and loose foreign key relationships).
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 16, 2011 at 1:10 pm
You theoretically could that in the table and procedure.
Add a "flagged for update/replacement" type column. If the query finds a row exists with the requested values, it flags that row for update and fails with an error message that asks about the existing data. If it finds a row with the same data, which has been flagged, then it replaces the row data.
HOWEVER, why would you do that in a proc? Stored procedures should very rarely, if ever, be used as a UI.
Also, in this particular example, why not just fail the insert and keep on going? It could check for existing data, and if it finds it, just skip the whole insert step. Replacing data with the exact same data doesn't seem all that useful to me. So, I'm going to assume your sample table and proc are just simplified versions of a more complex solution.
But, again, you're putting UI features in a stored procedure. Generally not a good idea.
I'd be more inclined, in a case that actually warrants a replacement, to add a "replace existing" parameter to the proc. Have it return an error if that's set to 0/false and it finds an existing match, and have the UI catch that error and prompt for an overwrite. If the user clicks/whatever "yes" on the overwrite, run the proc again, but with a 1 in that parameter value, and it will then overwrite/replace/re-insert/whatever. Put the request for an overwrite in the UI, not the proc, and just tell the proc what to do via parameters.
If you want to accomplish this in a single run of the proc, you really can't. Stored procedures don't have a means of sending prompts and handling them.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 16, 2011 at 3:37 pm
GSquared (5/16/2011)
You theoretically could that in the table and procedure.Add a "flagged for update/replacement" type column. If the query finds a row exists with the requested values, it flags that row for update and fails with an error message that asks about the existing data. If it finds a row with the same data, which has been flagged, then it replaces the row data.
HOWEVER, why would you do that in a proc? Stored procedures should very rarely, if ever, be used as a UI.
Also, in this particular example, why not just fail the insert and keep on going? It could check for existing data, and if it finds it, just skip the whole insert step. Replacing data with the exact same data doesn't seem all that useful to me. So, I'm going to assume your sample table and proc are just simplified versions of a more complex solution.
But, again, you're putting UI features in a stored procedure. Generally not a good idea.
I'd be more inclined, in a case that actually warrants a replacement, to add a "replace existing" parameter to the proc. Have it return an error if that's set to 0/false and it finds an existing match, and have the UI catch that error and prompt for an overwrite. If the user clicks/whatever "yes" on the overwrite, run the proc again, but with a 1 in that parameter value, and it will then overwrite/replace/re-insert/whatever. Put the request for an overwrite in the UI, not the proc, and just tell the proc what to do via parameters.
If you want to accomplish this in a single run of the proc, you really can't. Stored procedures don't have a means of sending prompts and handling them.
Yes, thats true, the information I provided here is a simplified version of a more complex process.
I am going to Remove all records in the Tables which have references from this table. (kind of CASCADE DELETE option ) And I am going to add records which are different from the Previous Version records (except the VersionNumber and Name are same). Records in other Tables will be different from the previous data.
and you mentioned to do this.
"Add a "flagged for update/replacement" type column. If the query finds a row exists with the requested values, it flags that row for update and fails with an error message that asks about the existing data. If it finds a row with the same data, which has been flagged, then it replaces the row data. "
where I should add this ? Should I add a physical Column in the VersionTbl ?
May 16, 2011 at 4:11 pm
Another option (or expanding one of the alternatives Gus mentioned) would be to use a variable when calling the sproc indicating whether it's the first run or a subsequent call.
In your sproc you could use something like
IF NOT EXISTS (SELECT 1 FROM MyTable WHERE VersionNumber= 1.0 AND VersionName= "First Version") OR @ins_flag =1
THEN INSERT
ELSE PRINT "Error Message"
The advantage of this solutions: there's no additional data change in between.
But both solutions (the one Gus suggested as well as mine) will get much more complicated if you need to deal with concurrency.
May 17, 2011 at 6:58 am
If you want to flag a row for replacement, you'd do that in the table itself.
Add a column "UpdateFlag" to the table. You could either make it a bit field, with 0 = do not update, and 1 = an update has been requested.
This adds some fairly complex logic to the process. You'd need to keep that row locked so that another user wouldn't then automatically update it if they call the proc, for example.
You could put something like a connection identifier into that field (would probably be a varbinary datatype column in that case), which would only allow the established connection to update it on a second call.
Most of the logic will still be a higher layer than the database/proc.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 20, 2011 at 8:51 pm
Thanks for your suggestions.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply