September 27, 2020 at 5:29 pm
Hi,
i am a beginner to SQL server and wanted to write a PROC where i can traverse through a table. The scenario is that I have table name book_reservation which holds the book_id, member_id, and reservation_To_date. now I want to write a proc where I can check every record of this table and compare the current system date and time with the reservation_To_date field. as per requirement, a member can reserve a book for 24 hrs only, so I am checking that if 24 hrs are elapsed so that member reservation should expire for that particular book
how to achieve this, as this is my ist proc to write in SQL server
regards
September 27, 2020 at 6:30 pm
You would probably do something like this:
DECLARE @LatestExpiredDate DATETIME = DATEADD(d,-1,GETDATE())
SELECT * FROM Book_Reservation br
WHERE @LatestExpiredDate >= br.reservation_To_date
This sets a temporary variable to the date/time of 'now' less 1 day.
The SELECT query compares this value with reservation_To_date. The logic may need tweaking a little bit depending on exactly what reservation_To_date holds (the reservation expiry date/time, the date/time the reservation was made etc)
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
September 28, 2020 at 7:54 pm
Thank You Phil for your time
why are you subtracting one day from current date and time.?
secondly to add some more requirement to my issue is that if date and time is expired then I have to update 2 x relations on the basis of boook_id and memeber_id.
Can i avoid cursor and use subqueries so as i can traverse through a table where i check every row of that recordset which is based on fol query or the query u have stated like "
select book_id,memeber_id from reserve_to_tbl where getdate() > reserve_To_Date;
"
can i avoid cursor as they are costly to hire as this procedure will run twice in 24 hrs using Sql Job
Reagrds
September 28, 2020 at 8:18 pm
Sameer,
if you post some DDL and sample data, it would be easier to show you in a query. The simple answer is yes, you can avoid a cursor by joining the tables.
i.e.
update a
set a.available = 1
from tablea a
join tableb b
on a.key = b.key
where b.Date > getdate()
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 28, 2020 at 9:22 pm
i came up with the following sol using cursor inside a procedure.
sample data is that i have to engage 2 x tables. one is book_master_tbl having book_id as the primary key and it has all the book information like name, author, language, genre, current_stock and the other table is book_reserve_tbl having book_id, member_id, reserve_To_Date and is_reserve. now as per policy a member can reserve a book only for 24 hrs from the date and time he reserve and with this reservation current_stock of the book is deducted by 1. now after reservation expiry this current_stock will be updated by adding one to the current stock and also is_reserve flag is set to No.
so if i can avoid cursor then plz guide if not then pl check my below cursor inside a proc, i putt inside proc for the only reason which i assume that if I don't do like this then I won't be able to call it using SQL Job? can I call cursor in SQL JOB? many questions plz guide
Create proc [dbo].[Reservation] as
Begin
Declare CheckReservation CURSOR local FOR Select book_id from
dbo.book_reserve_tbl where GETDATE() > reserve_to_date and is_reserve='Yes';
Open CheckReservation
Declare @book_id varchar(50)
Fetch Next from CheckReservation into @book_id
-
While(@@FETCH_STATUS = 0)
Begin
update book_master_tbl set current_stock=current_stock+1 where
book_id=@book_id;
update book_reserve_tbl set is_reserve='No' where book_id=@book_id
Fetch Next from CheckReservation into @book_id
End
CLOSE CheckReservation
DEALLOCATE CheckReservation
End;
September 28, 2020 at 10:18 pm
It appears you're looking for something that either works completely or fails and completely rolls back. Something like this try/catch script which doesn't have error handling or parameters. The reserved book_id's are read into a temporary table called @B. Then @b-2 is used to update reserved book table and set is_reserved to 'No'.
Create proc [dbo].[Reservation]
as
set nocount on;
set xact_abort on;
begin transaction
begin try
declare @b table(book_id int unique not null);
update bmt
set current_stock=current_stock+1
output inserted.book_id into @b
from dbo.book_master_tbl bmt
join dbo.book_reserve_tbl brt on bmt.book_id=brt.book_id
where brt.reserve_to_date < getdate()
and is_reserve='Yes';
update brt
set is_reserve='No'
from book_reserve_tbl brt
join @b b on brt.book_id=b.book_id;
commit transaction
end try
begin catch
/* raiserror or rethrow or log here */
rollback transaction
end catch
go
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
September 29, 2020 at 8:22 am
Awesome Steve...u got it.
i have some question about fol. is it part of that update statement where you use "OUTPUT inserted.book_id into @b-2" and that declaration part where u declare b and it data type as a table?? never seen that. I need some concept on this plz if u can drop a link or comments on it that what are these.
Thank You
declare @b table(book_id int unique not null);----
--and fol
output inserted.book_id into @b
September 29, 2020 at 9:34 am
if i do it like this . it will give the same result
update bmt
set current_stock=current_stock+1
from dbo.book_master_tbl bmt
join dbo.book_reserve_tbl brt on bmt.book_id=brt.book_id
where brt.reserve_to_date < getdate()
and is_reserve='Yes';
update brt
set is_reserve='No'
from book_reserve_tbl brt where getdate() < brt.reserve_to_date;
September 29, 2020 at 12:07 pm
Sameer,
the difference between yours and Steve's is that the output statement will give you the IDs you updated. The one you wrote could possibly return more records on the second update if some data has changed between the updates.
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 29, 2020 at 1:29 pm
yes, Thank you Mike. I got it
Now I tried to l00k at te google that how can I schedule the above PROC to be run every 8 hrs but i didn't get any clue that how to use the above PROC in SQL JOB using SQL agent. any idea how to schedule it, please.
Regards
September 29, 2020 at 2:16 pm
SQL Agent should be pretty straight forward. In the schedules section you can select the interval to run it in
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 29, 2020 at 2:27 pm
Awesome Steve...u got it.
i have some question about fol. is it part of that update statement where you use "OUTPUT inserted.book_id into @b-2" and that declaration part where u declare b and it data type as a table?? never seen that. I need some concept on this plz if u can drop a link or comments on it that what are these.
Thank You
declare @b table(book_id int unique not null);----
--and fol
output inserted.book_id into @b
The first line is declaring a temporary 'table variable' with 1 column and 2 constraints: 1) unique, and 2) not null. Table variables are an alternative to temp tables and/or physical tables. Instead of using the table variable either of these alternatives would work as well
/* locally scoped temporary table */
create table #b(
book_id int unique not null);
/* physical table */
create table dbo.b(
book_id int unique not null);
Generally speaking, and I've seen other SSC members write similar "rule of thumb", table variables are for holding a small number of rows, up to in the hundreds. If there are thousands of rows and an index is needed then I use temp tables. Since SQL 2014 table variables ARE permitted non unique indexes but I never switched to using them. If there's a compelling reason to prefer temp variables over temp tables, which have been around forever, I'm not aware of it. Vice versa also.
https://www.sqlshack.com/when-to-use-temporary-tables-vs-table-variables/
The OUTPUT clause of the UPDATE statement returns the rows affected by the update. Because it's UPDATE there are 2 'virtual tables' created, INSERTED and DELETED, which allow access to the new and old values involved in the execution of the DML. The same virtual tables are accessible in triggers (which is the old way this situation would've been handled). Using the OUTPUT is intended to preclude the possibility of changes in the underlying physical table(s) messing up your transaction. Mike01 explained it exactly right
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
September 29, 2020 at 3:57 pm
Yes, Mike, i have seen it but i couldn't find the facility or option where it asks me that here you can give your PROC name to call it. the schedule and steps are easy but what should i select as so it represents a PROC . e,g i see operating System Commands, T-SQL, power Shell, Replication Distributor, Replication Merge, etc but I didn't see any thing saying " Procedure" so I don't know which one to select and then i supply my PROCEDURE name "Reservation".
please Guide
Regards
September 29, 2020 at 4:03 pm
those are just ways to categorize the jobs, but a proc is T-SQL
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 29, 2020 at 4:04 pm
Thank You, Steve. it was important for me to be new to the SQL server. Thanks a lot
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply