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.
View 7 Replies
ADVERTISEMENT
Apr 17, 2014
I have a few questions about the following t-sql 2008 r2 sql code listed below that is calling a stored procedure:
DECLARE@return_value int,
@endYear SMALLINT = 2014,
@CustomerID INT = '9999',
@Schedules CHAR(1) = N'C'
EXEC [dbo].[sproom] @endYear
,@CustomerID
,@Schedules
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.".
when I change the sql above to:
DECLARE @return_value int,
@endYear SMALLINT = 2014,
@CustomerID INT = '9999',
@Schedules CHAR(1) = N'C',
@CustName varchar(50)
EXEC [dbo].[sproom] @endYear
,@CustomerID
,@Schedules
,@CustName
That is when I get the error message.
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.
View 3 Replies
View Related
Dec 3, 2014
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`?
View 1 Replies
View Related
Feb 26, 2013
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
Code:
Schedule for room called GR4
Course StartDate EndDate BeginTime EndTime HoursPerClass
MATH0001 06-FEB-13 19-FEB-13 0810 1700 9
MATH0002 20-FEB-13 04-Mar-13 0810 1700 9
MATH0003 10-JAN-13 05-Feb-13 0810 1700 9
MATH0004 22-APR-13 17-May-13 0810 1700 9
MATH0005 08-MAR-13 21-APR-13 0810 1700 9
MATH0006 07-JAN-13 09-JAN-13 0910 1300 4
MATH0007 20-JAN-13 17-MAY-13 1710 2000 3
MATH0008 08-JAN-13 18-JAN-13 1710 2000 3
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,
[code]....
View 2 Replies
View Related
Apr 15, 2008
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.
View 6 Replies
View Related
Jul 14, 2006
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
View 1 Replies
View Related
Dec 26, 2007
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
like this until the last day of the month
TNX
View 2 Replies
View Related
Mar 3, 2008
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
declare @ErrorMessage nvarchar(4000);
declare @ErrorSeverity int;
select
@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!
Thanks,
Flea
View 5 Replies
View Related
Nov 14, 2014
I am new to work on Sql server,
I have One Stored procedure Sp_Process1, it's returns no of columns dynamically.
Now the Question is i wanted to get the "Sp_Process1" procedure return data into Temporary table in another procedure or some thing.
View 1 Replies
View Related
Jul 20, 2007
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?
View 4 Replies
View Related
Feb 25, 2015
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
View 6 Replies
View Related
Jun 11, 2015
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.
View 5 Replies
View Related
Mar 6, 2014
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?
[URL]
View 7 Replies
View Related
Feb 25, 2008
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.
Thanks in advance,
Dimeji
View 4 Replies
View Related
Jul 3, 2000
Hi all -
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
Thanks,
Michelle
View 1 Replies
View Related
Apr 8, 2014
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.
View 9 Replies
View Related
Oct 16, 2014
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
View 9 Replies
View Related
Apr 9, 2014
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.
View 7 Replies
View Related
Apr 16, 2014
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?
View 2 Replies
View Related
May 16, 2014
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
[code]...
View 1 Replies
View Related
Jun 20, 2014
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
[code]...
View 1 Replies
View Related
Aug 5, 2014
I have a rather strange requirement where I need to convert my stored procedure into a normal SQL query for generating a SSRS report.
The business is very specific not to use SP or temp table in the query for the report generation.
I have a stored proc where I have used temp table and I have inserted values into that table.
what all these things I need to take when I convert it to as a SQL query for report generation?
View 9 Replies
View Related
Aug 29, 2014
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:
EXEC dbo.usp_MyStoredProcedure - EXEC dbo.usp_MyStoredProcedure -?
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
View 2 Replies
View Related
Oct 15, 2014
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
SET @sqlStr = N'Exec ' + @procName + ' @strAccount,@intHospital,@patType, @outDupCheck OUTPUT'
SET @ParmDefinition=N'@strAccount varchar(50),@intHospital int,@patType varchar(1), @outDupCheck int OUTPUT';
EXECUTE sp_executesql @sqlStr, @ParmDefinition, @strAccount = @parmINAccount, @intHospital=@parmINHospId,@patType=@ParmINpatType,@outDupCheck = @parmRET1
SELECT @parmRET1
--The parameter @parmRET1 returns NULL instead of 1 .
The @procName returns value 1 correctly if I run it separately ( outside of the dynamic sql)
Not sure what is wrong here...
View 2 Replies
View Related
Jan 8, 2005
Hi! all
How do I get student_ID's that attended prep between 3:00am and 4:00 am on daily basis from table below. With reference to Log_Time
CREATE TABLE [dbo].[PrepTime] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[PrepNo] [int] NOT NULL ,
[Log_Time] datetime NOT NULL DEFAULT, CURRENT_TIMESTAMP,
[coming] [int] NULL ,
[going] [int] NULL ,
[Student_ID] [bigint] NULL ,
[Study_Type] [int] NULL ,
[State] [smallint] NULL
) ON [PRIMARY]
GO
Thank you in Advance
View 3 Replies
View Related
Jan 8, 2005
Hi! all
How do I get student_ID's that attended prep between 3:00am and 4:00 am on daily basis from table below
CREATE TABLE [dbo].[PrepTime] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[PrepNo] [int] NOT NULL ,
[Log_Time] datetime NOT NULL DEFAULT, CURRENT_TIMESTAMP,
[coming] [int] NULL ,
[going] [int] NULL ,
[Student_ID] [bigint] NULL ,
[Study_Type] [int] NULL ,
[State] [smallint] NULL
) ON [PRIMARY]
GO
Thank you in Advance
View 14 Replies
View Related
Mar 14, 2014
Is it possible to write a stored procedure that will be used registered server?
For example I have registered server named 'Test Servers' and I want to use it in stored procedure
View 3 Replies
View Related
May 13, 2014
Say I have to server, A and B.
Now I want to write a stored procedure in server A, to retrieve data from server B.
How could I manage to do that?
View 4 Replies
View Related
Jun 2, 2014
I have various ways of getting the parameters of a stored procedure:
I have a procedure that has all defaults 4 are null and 2 are 0.
The following shows most of what I need but no defaults
SELECT PARAMETER_NAME ,
ORDINAL_POSITION ,
DATA_TYPE ,
CHARACTER_MAXIMUM_LENGTH ,
CHARACTER_OCTET_LENGTH ,
NUMERIC_PRECISION ,
[Code] ...
This one has two values:
PARAMETER_HASDEFAULT (always 0) and PARAMETER_DEFAULT (always 0)
sp_procedure_params_rowset proc procedure
Is there something else that would tell me if there is a default on a parameter and what the default is if there is one.
View 2 Replies
View Related
Jun 6, 2014
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.
View 3 Replies
View Related
Sep 11, 2014
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
View 2 Replies
View Related
Mar 11, 2015
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?
View 6 Replies
View Related
Mar 25, 2015
My stored procedure returns one row. I need to insert the result to a table. Can I right something like that:
=============
Insert into table1
exec myProc '1/1/15', 3/1/15'
=====
Or may be I can use Table-valued function insted of myProc?
View 4 Replies
View Related
