Database
|
|
 |
|
Hi,
I am using VS 2012 Free Edition (et al)
I am contemplating using this for the next version of 25 year old successfull software. We need to update various tables in a single transaction. This transaction needs to proceed in a step in which either All succeed, or None succeed. (resulting in a roll-back if any step failed) The database must deal with a high likelyhood that several terminals will at the same time try to write to the same entry in the same table. I handled this in MFC, with my own DB by giving each contentious item a serial-number, The first check for a modify-save would be if the serial number was still the same. if not, the item would be refused, and the transaction rolled back.
How does SQL handle this.
N.B.
The word 'Serial Number' seems to link to some add somewhere. Nothing to do with me! Just, Don't click it on my behalf.
Thankfully that link has disappeared since. TAKE NOTE IF IT HAPPENS TO YOU. The term 'Serial Number' turned into a link to some mobile phone sellers. Its gone now.
Bram van Kampen
|
|
|
|
|
Maybe I don't fully understand the problem, but SQL Server will handle "transactions" the same way. You declare the start of a transaction "Begin Transaction", do your stuff, then either "Commit" or "Rollback" the activity.
SQL server can automatically generate "serial" numbers for you. Look into the column data-type known as "Identity Column".
Did that answer your question?
|
|
|
|
|
Thanks,
I've since learned about the existence of 'Begin-End Transaction' Can you give me a link to a page explaining what it does and how it works?
Regards,#
Bram van Kampen
|
|
|
|
|
I have created a stored procedure which lists all customers that have 7 days left on their membership.
----------
create proc spGetMemReminder
as
select users.fullname, membership.expiryDate from membership
inner join users on membership.uid=users.uid
where convert(varchar(10), expiryDate,105) = convert(varchar(10), (getdate() +7), 105)
-------
I would like to insert this list into another table automatically. How do I achieve this? any suggestions appreciated. Thanks
|
|
|
|
|
|
Just use an insert:
CREATE PROCEDURE spGetMemReminder
AS
INSERT INTO YourTableNameHere (FullName, ExpiryDate)
SELECT users.fullname, membership.expiryDate from membership
inner join users on membership.uid=users.uid
where expiryDate = CAST(DATEADD(day, 7, getdate()) AS DATE
if expiryDate is a date column it is better to do date compare than to convert both sides.
|
|
|
|
|
|
Use an insert statement, that is not why I am posting a reply.
There are many ways to compare dates, comparing string (varchar) is probably the worst possible method. Do some research into the datetime object, you could have used datediff or dateadd neither of which require a convert.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
|
Hai.... You can do it by table varible.
create procspGetMemReminder
as
select users.fullname, membership.expiryDate from membership
inner join users on membership.uid=users.uid
where convert(varchar(10), expiryDate,105) = convert(varchar(10), (getdate() +7), 105)
end
GO
CREATE PROCEDURE InsertMember
AS
BEGIN
SET NOCOUNT ON
DECLARE @TABLE TABLE(fullname nvarchar(10), expiryDate datetime)
insert into @TABLE
exec spGetMemReminder
insert into yourothertable
select * from @TABLE
SET NOCOUNT OFF
END
|
|
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Rant 
Admin
Submit an article or tip
Post your Blog
