T-SQL (SS2K8) :: Run Block In Stored Procedure Only During Specific Time Frame
May 11, 2015
I have a stored procedure that runs every 5 minutes. I have one block in the procedure that will only run if there are records in a temp table. In addition, I would like this block to run only if the current time is between 0 and 5 minutes past the hour or between 30 and 35 minutes past the hour.
Currently, my block looks like this:
IF OBJECT_ID('tempdb..#tmpClosedPOs') IS NOT NULL
BEGIN
I can get the current minutes of the current time by using:
Select DATEPART(MINUTE,GetDate())
I know that it should be simple, but I'm pretty new at Stored Procedures. How do I alter the IF statement to check for the time and only run the block if it's between the times I stated? I started to DECLARE @Minutes INT, but wasn't sure where to go from there.
The sql listed above does execute the stored procedure called [dbo].[sproom] successfully and returns all the data all the rows from the stored procedure multiple times. However can you tell me the following:
1. How can I have the stored procedure return distinct rows? 2. I want the stored procedure to return selected columns. I tried using the OUTPUT parameter for some of the columns, but I got the error message, "Procedure or function spHomeroom has too many arguments specified.".
A solution might be to change the stored procedure, but I would prefer not to since this is a generic stored procedure that I believe a lot of t-sqls and stored procedures will use.
I have the following stored procedure to test scope of variables
alter proc updatePrereq @pcntr int, @pmax int as begin
[Code] ....
In the above script @i is declare in the if block only when the @pcntr value is 1. Assume the above stored procedure is called 5 times from this script
declare @z int set @z = 1 declare @max int set @max = 5 while @z <= @max begin exec dbo.updatePrereq @z, @max set @z = @z + 1 end go
As i said earlier `@i` variable exists only when `@pcntr` is `1`. Therefore when i call the stored procedure for the second time and so forth the control cannot enter the if block therefore @i variable wouldn't even exist. But the script prints the value in `@i` in each iteration, How comes this is possible should it throw an error saying `@i` variable does not exist when `@pcntr` values is greater than `1`?
I am trying to add the hours between each time block stored in a database.
In this database a user enters the begin time and the end time. For example the course MATH0001 would start at 8am and end at 10am. Therefore the user would enter 0810 in the start field and 1000 in the end field. The course MATH0001 doesn't run the entire semester it may only run from 8th Jan - 15th March and the course is scheduled in a room called GR4. Now because a course can be scheduled modularly, one room could have several courses scheduled in this manner.
The problem: I need to find out how many hours GR4 is used but it contains the following courses
A day only has 13 hours. Therefore the total hours spent in GR4 should be 12 hours. This is calculated by adding the hours between 8am and 5pm = 9 hours and 5pm an 8pm = 3 hours. I would not include 9am to 1pm because it is a subset of the 8am to 5pm slot.
Now, how to accomplish this but below is the code that i have thus far:
I forgot to mention that this code was to just test my 'final code' results and it outputs the table shown above. Anyway for testing purposes I have limited the search to the room GR4 and the day Tuesdays.
Code: select DISTINCT ssrmeet_room_code, ssrmeet_start_date, ssrmeet_end_date, ssrmeet_crn, ssrmeet_begin_time, ssrmeet_end_time, (((CAST(M.SSRMEET_END_TIME AS INT))-(CAST(M.SSRMEET_BEGIN_TIME AS INT)))+10)/100 As HoursPerClass,
What is the easiest way to write a query statemet to find two or more instances that occur in a 15 minute period? Any help would be greatly appreciated. Thanks.
Hi guys,This might be simplest thing, but I am newbie to databases.I need to find out only rows modified within certain time period from adatabase. As I undertand a way out could be adding an where clause forthe time period might be an option, I might be wrong here again.But, wanted to know is there any other option. Can triggers or anyother things help me in this matter.Regards,Abhijeet
i explain what i mean is if i have stored procedure with 7 conditions for evry day in the week one condition like this i have 7 code blocks for Sunday,Monday , .....Saturday
Code Block CREATE PROC YourProc @StartDate datetime = NULL, @EndDate datetime = NULL AS SET @StartDate = COALESCE(@StartDate,DATEADD(d,DATEDIFF(d,0,GETDATE()),0))--defaulting to todays date if not supplied SET @EndDate=COALESCE(@EndDate,DATEADD(m,1,@StartDate))--defaults to 1 month from today
WHILE @StartDate <= @EndDate BEGIN SELECT CASE WHEN DATENAME( dw,@StartDate)='Sunday' AND
empid IN (SELECT empid FROM v_un WHERE (shift =51 ) THEN 1 WHEN DATENAME( dw,@StartDate)='Monday' AND empid IN (SELECT empid FROM v_un WHERE (shift =11 ) THEN 2 ............ WHEN DATENAME( dw,@StartDate)='Saturday' AND ... THEN .. END SET @StartDate=DATEADD(d,1,@StartDate) END GO
Hello, I have stored procedure that when executed it will check to see if a given name is found in the database, if the name is found, I would like to have it continue on to do its work, however if the name is not found, I would like it to raise an error and then stop execution at that point, however, the way it is currently working is, if the name is not found, it catches the error, raises it and then continues on and tries to do its work, which then it bombs out because it can't. I wasn't sure if there was a way to stop the execution of the procedure in the catch statement. I don't think I want to raise the error level to 20-25 because I don't want to drop the connection to the database per say, I just want to halt execution.
Here is a simple example of what I have:
Code Snippet begin try
if not exists (select * from sys.database_principals where [name] = 'flea')
raiserror('flea not found', 16, 1) end try begin catch
@ErrorMessage = error_message(), @ErrorSeverity = error_severity(); raiserror(@ErrorMessage, @ErrorSeverity, 1); end catch go
begin
print 'hello world' end
At this point, if the user name (flea) is not found, I don't want it ever to get to the point of 'Hello World', I would like the error raised and the procedure to exit at this point. Any advice would be appreciated on how to best handle my situation!
I have created a stored procedure in the master database that can be run against any database on the server. I call this procedure from a sql server agent job. In this job I specify the database I want the procedure to run against but it always seems to run against the master database (presumably because the procedure is in this database). I can't switch database inside the procedure so what can I do other than creating a copy of the procedure in every database?
I encountered a werid bug that I can't figure it out in my stored procedure.Here's some sample code the can represent the scenario
Create Proc sp_test @DeptID Int as Begin Declare @i int=0 Declare @Count int=(Select count(*) from Total) while(@i<@Count)
[code]....
In the above code Total is a table that has employee name and its department ID and row_number info.The above code should list all employee info that belongs to one DEpt.but after I placed a try catch block the select statement returns no records.If I removed the try catch block it behaves correct.For example If three records reside in the Total table for a certain DeptID.
I expect the outPut will be Name age salary Mike 35 $60006 Tom 50 $75000 Frank 55 $120000
Is there a way to keep track in real time on how long a stored procedure is running for? So what I want to do is fire off a trace in a stored procedure if that stored procedure is running for over like 5 minutes.
I have a Stored Proc which populates a table and then uses BCP to output the table into a flat file and lastly ftp the file out to a remote site.
I'm trying to update the error handling as I first wrote this script on SQL2000 and it has now moved to SQL2008r2. The stored proc looks something like this:
BEGIN TRY BEGIN TRANSACTION <A whole bunch of inserts and updates> COMMIT TRANSACTION END TRY BEGIN CATCH <Error handling> ROLLBACK TRANSACTION END CATCH BEGIN <xp_cmdshell, BCP, FTP stuff> END
What I need to do is jump to the end of the script if an error invokes the CATCH block, so the xp_cmdshell stuff is not exicuted. Can I simply put a GOTO statement to take it to the end in the CATCH block, or do I have to set a variable in the CATCH block then test the variable outside the CATCH block or indeed is there a better way to simply terminate the script following the ROLLBACK?
I have a question. I know its possible to create a dynamic query and do an exec @dynamicquery. Question: Is there a simpler way to update specific columns in a table at run time without doing if else for each column to determine which is null and which has a value because i'm running into a design dilemna on how to go about it.
FYI: All columns in the table design are set to null by default.
is there a way to process a file x records at a time?
We have a table that I need to append to an existing table. The date columns are currently in char but must be converted to datetime for the existing table. The problem is I have bad data. There are 3 million rows where the date field isn't valid for SQL's datetime format. Since this is the data I have, I have to work with it. I would like for SQL to just insert a null if it comes upon a bad date. Currently when it encounters a field that isn't valid, it stops the process with an error.
I have tried to go around it below, but there is still something "hanging" I would like to be able just to insert one million rows at a time and if it errors, then I can look at the next million, find the error, fix it and continue on.
Any suggestions? Or if you have a better idea all together I would love to see it.
SQL Server 7.0, SP2
,CASE when (substring(check_date,1,4) not between '1997' and '2000' or substring(check_date,5,2) not between '01' and '12' or substring(check_date,7,2) not between '01' and '31') THEN null ELSE cast(check_date as datetime) END AS check_date
I am connecting to a new SQL Server 2008 R2 database using SSMS from my ADMIN VM workstation. I bring up a Stored Procedure and make a change.... I execute the Stored Procedure... after it finishes.... I exit out without saving to a file.... I go back in and my change was not held.
I can do the exact same process with an old SQL Server 2005 database. Is there a permission I am missing to set to be able to do this on the 2008 database.
yesterday i was trying to create Stored procedure but it fails i don't know why
CREATE proc GetBooksbyBorrowerID @Borrower_id INT AS BEGIN SELECT A.BORROWER_ID ,a.ISBN, b.book_Title,b.LANGUAGE, CONVERT(VARCHAR,a.borrowed_from_date,103)"Borrowed On(dd/mm/yyyy)" FROM borrower_details a, book_mst b WHERE a.borrower_id=@Borrower_id AND a.ISBN = b.ISBN END GO EXEC SP_Task1 10001
OK, to simplify some annual auditing of DB users (not the SQL logins,) I'm trying to craft a stored procedure that the customer on the server (they're the only customer on this particular server) can run to get a listing of all DB users and what roles they have.
I've got a query that returns this for the currently selected DB, so that part's done.I can use SP_MSFOREACHDB to run it against each DB, with the results going into a temp table to make it easier to copy/paste into an Excel file.What I want to do, and can't seem to see how, is wrap the whole thing in yet another SP of my own, with an EXECUTE AS so that the customer doesn't need sysadmin or any special privileges on the server. When I do this, it runs, but only against master.
Now, from digging it looks like you can't have an "insert #temptable exec sp_whatever" inside another SP. I'd like to avoid dynamic SQL, and while I know there are problems with MSFOREACHDB, it'll work for what we need.how to turn a user created SP, into a system SP so it can be run regardless of the DB you've selected, so at least there's that.
In t-sql 2008 r2 I need execute a stored procedure called StudentData and pass 3 parameter values to the stored procedure. The stored procedure will then return 5 values that are needed for the main sql. My problem is I do not know how to have the t-sql call the stored procedure with the 3 parameter values and pass back the 5 different unique data values that I am looking for.
The basic dataset is the following:
SELECT SchoolNumber, SchoolName, StudentNumber, from [Trans].[dbo].[Student] order by SchoolNumber, SchoolName, StudentNumber
I basically want to pass the 3 parameters of SchoolNumber, SchoolName, and StudentNumber to the stored procedure called StudentData from the data I obtain from the [Trans].[dbo].[Student]. The 3 parameter values will be obtained from the sql listed above.
The columns that I need from the stored procedure called StudentData will return the following data columns that I need for the report: StudnentName, StudentAddress, Studentbirthdate, StudentPhoneNumber, GuardianName.
Thus can you show me how to setup the sql to meet this requirement I have?
I am trying to modify a script that to back databsae in t-sql.I put it in a stored procedure with one paramenter dbname.I would like if a dbname is passed when calling the sproc, it will only backup this database, if no paramenter is entered, the dbname is null, then backup all user databases- no system databases.But I have difficulty to define this two situations in code.
Below is the sproc: CREATE PROCEDURE [dbo].[BackupDB] @dbname varchar(50) AS BEGIN
I'm not new to SQL at all, but I'm completely new to backup/restore TSQL.I have the following script, which backs up 2 databases and restores them under different database names.the script runs fine as-is, but I cannot seem to package it within a create stored procedure statement.if I attempt to wrap create procedure <name> AS begin....end around it, the statement simply executes.
--backup LGTY_QA_01, restore to LGTY_DV_01
BACKUP DATABASE LGTY_QA_01 TO DISK = 'C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLBackupLGTY_QA_01.bak' WITH FORMAT, CHECKSUM GO ALTER DATABASE LGTY_DV_01 SET SINGLE_USER WITH ROLLBACK IMMEDIATE GO
I know ran across this some time ago when researching some other topic, but I would like to add an option to our stored procedures (which have multiple parameters) so a user could do something like:
The result would just list the parameters in the stored procedure and the options available for each parameter/what the parameter was use for.
Here is an example of one stored procedure in use:
CREATE PROCEDURE [dbo].[usp_MyStoredProcedure] ( @BCP INT = 0---- 0 [Default]: No BCP Import, 1: Insert TempBCP Data , @Debug INT = 0---- 0 [Default]: Run process, 1: Create temp tables, run queries , @StartDate smalldatetime = '1/1/1900' ---- Enter StartDate; [Default]: otherwise use Max of Invoice date , @Date INT = 0 ---- 0 [Default]: Use std process (get last Saturday date from @StartDate), 1: Use @StartDate as is ) WITH EXECUTE AS 'ADBAccount' AS BEGIN .... END
Note: @procName,@strAccount,@intHospital,@patType are passed to this procedure from another procedure Note: The @procname procedure also takes the above parameters and @outDupCheck as output parameter
DECLARE @sqlStr NVARCHAR(500) DECLARE @ParmDefinition NVARCHAR(500) DECLARE @parmINAccount VARCHAR(30),@parmINHospId int , @ParmINpatType varchar(1) DECLARE @parmRET1 int SET @parmINAccount = @strAccount SET @parmINHospId = @intHospital SET @ParmINpatType = @patType
I'm working on building a report and asked a developer which table some data comes from in an application. His answer was the name of a 3500 line stored procedure that returns 2 result sets. I could accomplish what I'm trying to do using the second result set, but I'm not sure how to put that into a temporary table so that I could use it.
Here's my plan according to the Kübler-Ross software development lifecycle:
Denial - Ask the developer to make sure this is correct (done) Despair - Look hopelessly for a solution (where I am now) Anger - Chastise developer Bargaining - See if I can get him to at least swap the order that the resultsets are returned Acceptance - Tell the users that this can't be done at present.
We have a stored procedure that makes a decision to pull records from one of two servers.
If one of these servers became unavailable but was no longer queried would the stored procedure still work? Doesn't SQL recompile stored procedures periodically?
Something like this:
If @ServerAIsDown=1 begin select * from ServerB.dbo.MyTable end else begin select * from ServerA.dbo.MyTable end
Firstly may I say that the sproc I am having problems with and the service that calls it is inherited technical debt from an unsupervised contractor. We are not able to go through a rewriting process at the moment so need to live with this if possible.
Background
We have a service written in c# that is processing packages of xml that contain up to 100 elements of goods consignment data. In amongst that element is an identifier for each consignment. This is nvarchar(22) in our table. I have not observed any IDs that are different in length in the XML element.
The service picks up these packages from MSMQ, extracts the data using XPATH and passes the ID into the SPROC in question. This searches for the ID in one of our tables and returns a bool to the service indicating whether it was found or not. If found then we add a new row to another table. If not found then it ignores and continues processing.
Observations
The service seems to be dealing with a top end of around 10 messages a minute... so a max of about 1000 calls to the SPROC per minute. Multi-threading has been used to process these packages but as I am assured, sprocs are threadsafe. It is completing the calls without issue but intermittently it will return FALSE. For these IDs I am observing that they exist on the table mostly (there are the odd exceptions where they are legitimately missing). e.g Yesterday I was watching the logs and on seeing a message saying that an ID had not been found I checked the database and could see that the ID had been entered a day earlier according to an Entered Timestamp.
So the Sproc...
USE [xxxxxxxxxx] GO
SET ANSI_NULLS ON GO
SET QUOTED_IDENTIFIER ON GO
[Code]....
So on occasions (about 0.33% of the time) it is failing to get a bit 1 setting in @bFound after the SELECT TOP(1).
The only suggestions I can make have been...
change @pIdentifier nvarchar(25) to nvarchar(22) Trim any potential blanks from either side of both parts of the identifier comparison Change the SELECT TOP(1) to an EXISTS
The only other thought is the two way parameter direction in the C# for the result OUTPUT. Not sure why he did it that way or what the purpose is.
I have been unable to replicate this using a test app and our test databases. Has observed selects failing to find even though the data is there, like this before?
