Stored Proc To Get Single Person From A Table Based On Earliest Datetime
Oct 13, 2005
Hi,
I'm having problems with a stored procedure, that i'm hoping someone can help me with.
I have a table with 2 columns - Username (varchar), LastAllocation (datetime)
The Username column will always have values, LastAllocation may have NULL values. Example
Username | LastAllocation
------------------------
Greg | 02 October 2005 15:30
John | 02 October 2005 18:00
Mike | <NULL>
My stored procedure needs to pull back a user name with the following criteria:
If any <NULL> dates send username of first person where date is null, sorted alphabetically, otherwise send username of person with earliest date from LastAllocation
Then update the LastAllocation column with GETDate() for that username.
This SP will be called repeatedly, so all users will eventually have a date, then will be cycled through from earliest date. I wrote an SP to do this, but it seems to be killing my server - the sp works, but I then can't view the values in the table in Enterprise Manager. SP is below - can anyone see what could be causing the problem, or have a better soln?
Thanks
Greg
------------------------------------------------------------------------------
------------------------------------------------------------------------------
CREATE PROCEDURE STP_GetNextSalesPerson AS
DECLARE @NextSalesPerson varchar(100)
BEGIN TRAN
IF (SELECT COUNT(*) FROM REF_SalesTeam WHERE LeadLastAllocated IS NULL) > 0
BEGIN
SELECT TOP 1 @NextSalesPerson = eUserName FROM REF_SalesTeam WHERE LeadLastAllocated IS NULL ORDER BY eUserName ASC
END
ELSE
BEGIN
SELECT TOP 1 @NextSalesPerson = eUserName FROM REF_SalesTeam ORDER BY LeadLastAllocated ASC
END
SELECT @NextSalesPerson
UPDATE REF_SalesTeam SET LeadLastAllocated = GETDATE() WHERE eUserName = @NextSalesPerson
Can someone help me rewrite this query? Basically I need to check if the name stored in "CustomerName" already exist in the table "Renter". If it does not then I ncan insert the new customer name into the data table "Renter". If the value in "CustomerName" already exists in the database, then I need to bypass the "INSERT" and somehow return a value indicating that the insert was not performed. How can I do this. Here is the query I currently have for performing the insert. CREATE PROCEDURE [dbo].[VacancyGet] ( @CustomerName nvarchar(100), @ClientCode nvarchar(10) ) AS INSERT INTO Renter ( CustomerName, ClientCode, ) VALUES ( @CustomerName, @ClientCode, )
Hello all,Quick sql syntax question:I have this table:if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[REQUESTS]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[REQUESTS]GOCREATE TABLE [dbo].[REQUESTS] ([ROW_ID] [uniqueidentifier] NULL ,[REQUEST_DATE] [datetime] NULL ,[STATUS] [tinyint] NULL) ON [PRIMARY]GOwith these values:insert into REQUESTS (REQUEST_DATE, STATUS)values (getdate(), 0)insert into REQUESTS (REQUEST_DATE, STATUS)values (getdate(), 1)insert into REQUESTS (REQUEST_DATE, STATUS)values (getdate(), 0)I need to select the single record with a STATUS = 0 with the earliestREQUEST_DATEI am using this query:SELECT TOP 1 ROW_ID FROM REQUEST_LOG WHERE STATUS = 0 ORDER BYREQUEST_DATEnot sure if this is the way to go...pointer appreciatedthanks
Finding the earliest datetime entry, and then updating the database on these reults.
I need to query a table of data that has multiple datetime entries in it relating to individual customer records. So one customer could have 1 entry, or it could have 10 entries. I need to be able to identify the earliest of these records, and then on this earliest record update a field value. Example:
My Fields in the table are as follows. Log_ID, Cust_Ref_No, ActionDateTime, Action_Code
The Log_ID is the primary key, and I need to update the Action_Code field on only the earliest entry against a customer record i.e.
I have a requirement where i have to create diffferent stored procedures in different databases based on some criteria. The stored procedure creation scriptswill be in a single T SQL variable.Here is something i trying to achieve.( this is just a sample)USE MASTER GO declare @SQLString Nvarchar(1000) declare @STR Nvarchar(1000) declare @DBName nvarchar(100) SET @DBName ='DB1'SET @STR=' create proc sptemp @id int as select * from orders where id=@id go ' set @SQLString='USE ' + @DBName + char(10) +'GO' set @SQLString =@SQLString + @str EXEC (@SQLString) When i try to run this script in master it is giving me the following errors: Msg 170, Level 15, State 1, Line 2 Line 2: Incorrect syntax near 'GO'. Msg 111, Level 15, State 1, Line 3 'CREATE PROCEDURE' must be the first statement in a query batch. Msg 137, Level 15, State 2, Line 6 Can anyone help me out in this?
How do I return a value in a stored procedure? I want to return a value for TheQuarterId below but under all test conditions am only getting back a negative one. Please help! create PROCEDURE [dbo].[GetQuarterIdBasedOnDescription] ( @QuarterString nvarchar(10), @TheQuarterId int output ) AS
BEGIN SELECT @TheQuarterId = QuarterId from Quarter WHERE Description=@QuarterString END
One employee has two pay rates for two different jobs:
Job A: Rate $10.00 Job B: Rate $15.00
I will be updating their record so that they only have one job going forward, Job C. I need Job C to equal their HIGHER of the two existing jobs.
I have a select statement to find what the higher rate is. However, I am not sure how I can apply the rate to be the new job's rate. Here's what I used to find the highest rate for one single person:
SELECT max(rate), employeeID FROM JobsTable inner join IDTable on JobsID2 = IDID2
WHERE JobCode in ('JOBA','JOBB') and EmployeeID = '12345' GROUP BY EmployeeID
(this returns the employee ID from one table, and the highest rate from Jobs A and B from another table)
I can get it to update to add JobC -- how can I get it to assign the result from the above query to be the rate used for Job C?
two variables declared in my proc:@DATE_RANGE_START as datetime,@DATE_RANGE_END as datetime,When I execute my SP it takes 34 seconds.When I change the variables to:@DATE_RANGE_START1 as datetime,@DATE_RANGE_END1 as datetime,and add this to my sp:declare @DATE_RANGE_START datetimedeclare @DATE_RANGE_END datetimeset @DATE_RANGE_START = @DATE_RANGE_START1set @DATE_RANGE_END = @DATE_RANGE_END1the SP runs in 9 seconds (which is expected)Passing in '1/1/01' and '1/1/07' respectivly.Everything else is equal and non-important to this problem.Why does it take 34 seconds when I use the variables from the inputparameters?Interesting isn't it.Jeff
We have a .NET drop down, which gets populated as the user types in letters(last name). If the user types in the single quote we get the error about not escaping the single quote. Question is, which way would it be easier to fix, in the .NET code or in the SQL procedure? I am not to sure if we have full access to the source code since that is a 3rd party control, so if that is not feasible how would I fix that in the stored procedure? This is the current proc that we are using:
Code Snippet select @str = 'SELECT DISTINCT TOP ' + @Top + ' e.DisplayName as DbComboText, e.EmployeeID as DbComboValue FROM DepartmentDirectory.dbo.Employees ee INNER JOIN DataMart.dbo.Employees e ON ee.UIN = e.UIN WHERE e.LastName like ''' + @LastName + ''' AND e.FirstName like ''' + @FirstName + ''' ORDER BY e.DisplayName'
I have used several sql queris to generate a report. This queries pull out data from different tables. But sometimes at the same table too. Basically those are SELECT statements. I have created stored proc for each SELECT statement. now I'm wondering can I include all SELECT statements in one stored proc and run the report. If possible, can anyone show me the format?
I'm trying to concatenate fields in SQL stored proc for use in textfield in asp.net dropdownlist. I'm running into a problem when I tryto use a DateTime field, but can't find the answer (so far) on theInternet. Was hoping someone here would know?My sql stored proc:SELECT AnomalyID, DateEntered + ', ' + Station + ', ' + Problem As'SelectInfo'FROM tblAnomalyWHERE WorkOrder=@varWO AND Signoff=NullORDER BY DateEnteredbut I get the error:The conversion of a char data type to a datetime data type resulted inan out-of-range datetime value.I'm not the brightest bulb on the block, so any clues greatlyappreciated!Thanks, Kathy
I'm attempting to pass a datetime variable to a stored proc (called via sql task). The variables are set in a previous task where they act as OUTPUT paramters from a stored proc. The variables are set correctly after that task executes. The data type for those parameters is set to DBTIMESTAMP.
When I try to exectue a similar task passing those variables as parameters, I get an error:
Error: 0xC002F210 at ax_settle, Execute SQL Task: Executing the query "exec ? = dbo.ax_settle_2 ?, ?,?,3,1" failed with the following error: "Invalid character value for cast specification". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
If I replace the 2nd and 3rd parameters with quoted strings, it is successful: exec ?= dbo.ax_settle ?, '3/29/06', '4/30/06',3,1
I have a patient record and emergency contact information. I need to find duplicate phone numbers in emergency contact table based on relationship type (RelationType0 between emergency contact and patient. For example, if patient was a child and has mother listed twice with same number, I need to filter these records. The case would be true if there was a father listed, in any cases there should be one father or one mother listed for patient regardless. The link between patient and emergency contact is person_gu. If two siblings linked to same person_gu, there should be still one emergency contact listed.
Below is the schema structure:
Person_Info: PersonID, Person Info contains everyone (patient, vistor, Emergecy contact) First and last names Patient_Info: PatientID, table contains patient ID and other information Patient_PersonRelation: Person_ID, patientID, RelationType Address: Contains address of all person and patient (key PersonID) Phone: Contains phone # of everyone (key is personID)
The goal to find matching phone for same person based on relationship type (If siblings, then only list one record for parent because the matching phones are not duplicates).
I need to call the stored procedure below. Basically what I need to know is if the query returns a record? Note I would be happy if I could just return the number Zero if no records are returned. Can someone please help me out here?Here is my query so far in SQL Server. I just don't know how to return a value based upon the result of the records returned from the query. GOCREATE PROCEDURE [dbo].[GetNameStatus]( @CountryId decimal, @NameId decimal, @DescriptionId decimal)AS SELECT Name.Active FROM Name INNER JOIN NameDescription ON Name.NameId = NameDescription.NameId WHERE Name.CountryId=@CountryId AND Name.NameId=@NameId AND NameDescription.DescriptionId=@DescriptionId AND Name.Active='Y'
Can someone give me a clue on this. I'm trying to insert values based off of values in another table.
I'm comparing wether two id's (non keys in the db) are the same in two fields (that is the where statement. Based on that I'm inserting into the Results table in the PledgeLastYr collumn a 'Y' (thats what I want to do -- to indicate that they have pledged over the last year).
Two questions
1. As this is set up right now I'm getting NULL values inserted into the PledgeLastYr collumn. I'm sure this is a stupid syntax problem that i'm overlooking but if someone can give me a hint that would be great.
2. How would I go about writing an If / Else statement in T-SQL so that I can have the Insert statement for both the Yes they have pledged and No they have not pledged all in one stored proc. I'm not to familar with the syntax of writing conditional statements within T-SQL as of yet, and if someone can give me some hints on how to do that it would be greatly appriciated.
Thanks in advance, bellow is the code that I have so far:
RB
Select Results.custID, Results.PledgeLastYr From Results, PledgeInLastYear Where Results.custID = PledgeInLastYear.constIDPledgeInLastYear Insert Into Results(PledgeLastYr) Values ('Y')
I need to set a variable to datetime and time to exact milliseconds in SQL server in stored procedure.
Example: set MyUniqueNumber = 20071101190708733 ie. MyUniqueNumber contains yyyymmddhhminsecms
Please help, i tried the following: 1. SELECT CURRENT_TIMESTAMP; ////// shows up with - & : , I want single string as in above example.2. select cast(datepart(YYYY,getdate()) as varchar(4))+cast(datepart(mm,getdate()) as char(2))+convert(varchar(2),datepart(dd,getdate()),101 )+cast(datepart(hh,getdate()) as char(2))+cast(datepart(mi,getdate()) as char(2))+cast(datepart(ss,getdate()) as char(2))+cast(datepart(ms,getdate()) as char(4))
This one doesnot display day correctly, it should show 01 but shows 1
What I need to do is merge the two tables into a single table based on the matching timestamp:
timestamp, servername1, servername2 as headers then values under "1/1/1-1:10:1" , "10" , "10" - as an example "1/1/1-1:20:1" , "20" , "21" - as an example "1/1/1-1:30:1" , "1" , "5" - as an example
Powershell or MSSQL 2008 queries work.
I have a powershell script atm that can create the table and then throw it back into the database but its very slow because of the amount of records.
The table is for some performance stat collection app.
Table #1 records a timestamp, the servername (which is the same over and over in the table) and the value of the performance object. Table #2 is the same collection but for a different server.
The layout in the database is terrible but this is what I get to work with.
I have table "Clients" who have associated records in table "Mailings" I want to populate a gridview using a single query that grabs all the info I need so that I may utilize the gridview's built in sorting. I'm trying to return records containing the next upcoming mailing for each client.
The closest I can get is below: I'm using GROUP BY because it allows me to return a single record for each client and the MIN part allows me to return the associated record in the mailings table for each client that contains the next upcoming 'send_date'
SELECT MIN(dbo.tbl_clients.client_last_name) AS exp_last_name, MIN(dbo.tbl_mailings.send_date) AS exp_send_date, MIN(dbo.tbl_mailings.user_id) AS exp_user_id, dbo.tbl_clients.client_id, MIN(dbo.tbl_mailings.mailing_id) AS exp_mailing_idFROM dbo.tbl_clients INNER JOIN dbo.tbl_mailings ON dbo.tbl_clients.client_id = dbo.tbl_mailings.client_idWHERE (dbo.tbl_mailings.user_id = 1000)GROUP BY dbo.tbl_clients.client_id The user_id set at 1000 part is what makes it rightly pull in all clients for a particular user. Problem is, by using the GROUP BY statement I'm just getting the lowest 'mailing_id' number and NOT the actual entry associated with mailing item I want to return. Same goes for the last_name field. Perhaps I need to have a subquery within my WHERE clause?Or am I barking up the wrong tree entirely..
It is all contained in one table. I have got the earliest years by using the YEAR() function and grouping by it. The only problem is that I am having a problem joining the table back onto itself with the subselect because of it's an aggregate.
Here is the first join I have....
SELECT DT.ClientID, Year1 FROM ( SELECT ClientID, YEAR(MyDate) as Year1 FROM MyTable ) AS DT GROUP BY ClientID, Year1 ORDER BY ClientID, Year1
I want to use that as my derived table and then join back to it... but it's not working...
We're developing an application request/packaging/rollout worflowapplication for our 50 site, 40,000 user company. There is a requesttable, an engineering table, a distribution table, etc. etc. But, thecompany has a designated "Application Owner" at each site, and eachperson who will use the application must also be listed in the workflowapplication. So, we need a lookup table for the owners and users:CREATE TABLE REQUEST (RQ_ID INTEGER NOT NULL,RQ_BY_ID INTEGER NOT NULL,RQ_FOR_ID INTEGER NOT NULL,ASSIGNED_ENGINEER_ID INTEGER NOT NULL,OTHER INFO...);CREATE TABLE APP_OWNERS (RQ_ID INTEGER NOT NULL,OWNER_ID INTEGER NOT NULL);CREATE TABLE APP_USERS (RQ_ID INTEGER NOT NULL,USER_ID INTEGER NOT NULL);There are many other tables, of course, some with single person IDfields and addititional lookup tables where there are multiple peopleinvolved like testers, package distributors, etc. I began to wonder,why not just a single table to cover ALL the people involved:CREATE TABLE RQ_WORKFLOW_PEOPLE (RQ_ID INTEGER NOT NULL,PERSON_ROLE VARCHAR(20) NOT NULL,PERSON_ID INTEGER NOT NULL);INSERT INTO RQ_WORKFLOW_PEOPLE (rq_id,person_role,person_id) values(123456,'RQ BY',314159),(123456,'RQ FOR',951413),(123456,'APP OWNER',159413),(123456,'APP OWNER',413159),(123456,'USER',594131),(123456,'USER',313459),.....The real question I have is how does one evaluate options like this?The good news, I think, is that where I simply must have crossreference tables because of multiple values (application owners, users,testers, etc.) I've reduced the number of those tables to one byspecifying a single table by role. Is that a good thing or a bad thing?I've also removed similar data from several other tables where only asingle column was needed in those tables, i.e. the requested by andrequested for fields, the assigned engineer, and several others. Thereis one and only one of each for each request but the type of data, thatis an employee ID is exactly the same, so does it make more "sense" tokeep the data with the request table or the engineering table orconsolidate all ID data in an ID table?Any thoughts on this woudl be appreciated.Randy
I am working with a large application and am trying to track down a bug. I believe an error that occurs in the stored procedure isbubbling back up to the application and is causing the application not to run. Don't ask why, but we do not have some of the sourcecode that was used to build the application, so I am not able to trace into the code. So basically I want to examine the stored procedure. If I run the stored procedure through Query Analyzer, I get the following error message: Msg 2758, Level 16, State 1, Procedure GetPortalSettings, Line 74RAISERROR could not locate entry for error 60002 in sysmessages. (1 row(s) affected) (1 row(s) affected) I don't know if the error message is sufficient enough to cause the application from not running? Does anyone know? If the RAISERROR occursmdiway through the stored procedure, does the stored procedure terminate execution? Also, Is there a way to trace into a stored procedure through Query Analyzer? -------------------------------------------As a side note, below is a small portion of my stored proc where the error is being raised: SELECT @PortalPermissionValue = isnull(max(PermissionValue),0)FROM Permission, PermissionType, #GroupsWHERE Permission.ResourceId = @PortalIdAND Permission.PartyId = #Groups.PartyIdAND Permission.PermissionTypeId = PermissionType.PermissionTypeId IF @PortalPermissionValue = 0BEGIN RAISERROR (60002, 16, 1) return -3END
I am trying to grant solely the SELECT privilige to a particular user and to a particular database.I am using Sql server management studio express to do this and SQL Server 2005.
I have created a new login and set the server roles to public, then on the user mapping page, I have checked the box next to the table I wish the user to access and have selected the database membership role - public. Next, On the Table Properties, I have added the user on the Permissions Page and Selected "Select" under the grant column
When I click on effective permissions, the select command does not show and when I try to login with that user, the table does not display.
Need some suggestions on what to check or look for.My stored proc is not finding the table in the sql db. This is the error...Invalid object name 'tblServRec'I use the same function to pass in an arraylist and the sp name for another sp and that one works ok.The sp is the same as another one except for the table name and that one works ok.The sp works fine on my local machine. It finds the table ok. But when I try it on the server, it doesn't work.I have checked permissions, and they are the same for all tables, even the one that the other sp works ok on the server.Here is the sp if that will help.....CREATE PROCEDURE dbo.deleteServRec @fldNum INT ASBEGIN DECLARE @errCode INT DELETE FROM tblServRec WHERE fldNum = @fldNum SET @errCode = 0 RETURN @errCode HANDLE_APPERR: SET @errCode = 1 RETURN @errCodeENDGOThanks all,Zath
I take the information below in query analyzer and everything runs fine-returns 48 rows. I try to run it as a stored proc and I get no records. If I put a simple select statement into the stored proc, it runs, so it's not permissions. Can anyone help me with why this won't execute as a stored procedure? Articles seem to indicate you can do this with temp tables in a stored procedure. Thanks
declare @style as int, @disc as int, @key as varChar(500), @sdate as varChar(15), @edate as varChar(15), @ld as varChar(15)
set @style=0 set @disc=0 set @sdate='3/1/2006' set @ld='2'
insert into #ListAll SELECT top 100 percent wid, parentID, 0 as myFlag FROM myTable WHERE (@style=0 or styleID=@style) and (@edate is null or start_date < @edate) and ((start_date is null) or (datediff(day,start_date,@sdate) <1)) and (@ld='9' or charIndex(convert(varchar(1),datepart(dw,start_dat e)),@ld)>0) and wid in (select wid from myTable2 where (@disc=0 or discID=@disc)) and wid in (select wid from myTable where @key is null or ([title] like '%' + @key + '%' or [keywords] like '%' + @key + '%'))
update #ListAll set myFlag=1 where parentID<>0 insert into #ListAll select w.wid, w.parentID, 0 as myFlag from myTable w right join #ListAll on #ListAll.parentID=w.wid where #ListAll.parentID<>0 delete #ListAll where myFlag=1
SELECT top 100 percent srt, w.WID, w.parentID, w.[title], w.start_date, w.end_date, w.cancelled, w.url, styleID, w.[keywords], w.onlineID, w.httplocation, datepart(dw,w.start_date) as lddate FROM myTable w right join #ListAll on #ListAll.wid=w.wid ORDER BY srt, start_date, [title]
Recently someone told me that I could use a Parameter in a Stored Proc as a text placeholder in the SQL Statement. I needed to update a table by looping thru a set of source tables. I thought NOW IS MY TIME to try using a parameter as a table name. Check the following Stored Proc
CREATE PROCEDURE [dbo].[sp_Update] @DistributorID int, @TableName varchar(50) AS UPDATE C SET C.UnitCost = T.[Price] FROM (tbl_Catalog C INNER JOIN @TableName T ON C.Code = T.Code) GO
NEEDLESS TO SAY this didn't work. In reviewing my references this seems to be a no no.
Is it possible to use a parameter as a table name? OR is there another way to do this?
We have modified a column name for a table. Now we need to find out all the database objects (stored procedures, functions, views) which access this table so that we can modify them. This is a very big database and its not easy to open the code for each object and check if its access that table. To add to the complexity, its not just one table and column but a number of tables have been modified.
I am looking to populate a Schedule table with information from twoother tables. I am able to populate it row by row, but I have createdtables that should provide all necessary information for me to beableto automatically populate a "generic" schedule for a few weeks ormoreat a time.The schedule table contains:(pk) schedule_id, start_datetime, end_datetime, shift_employee,shift_positionA DaysOff table contains:(pk) emp_id, dayoff_1, dayoff_2 <-- the days off are entered in dayofweek (1-7) formA CalendarDays table contains:(pk) date, calendar_dow <-- dow contains the day of week number (asabove) for each day until 2010.My main question is how to put all of this information together andhave SQL populate the rows with data based on days off, for a fewweeks in advance. Anysuggestions?
I am looking to populate a Schedule table with information from twoother tables. I am able to populate it row by row, but I have createdtables that should provide all necessary information for me to beableto automatically populate a "generic" schedule for a few weeks ormoreat a time.The schedule table contains:(pk) schedule_id, start_datetime, end_datetime, shift_employee,shift_positionA DaysOff table contains:(pk) emp_id, dayoff_1, dayoff_2 <-- the days off are entered in dayofweek (1-7) formA CalendarDays table contains:(pk) date, calendar_dow <-- dow contains the day of week number (asabove) for each day until 2010.My main question is how to put all of this information together andhave SQL populate the rows with data based on days off. Anysuggestions?
Hello,Does anyone know of a way to loop thru a SQL table using code in a storedprocedure?I need to go thru each record in a small table and build a string usingvalues from the fields associated with a part number, and I can't find anyway to process each record individually. The string needs to be initializedwith the data associated with the 1st record's part number, and I need tobuild the string until a new part number is incurred. Once a new part numberis found in the table, the string is written to a different table and resetfor this next part number in the table. Need to repeat until all records inthe table have been processed.I use ADO in access 2000 to work thru local recordsets, I just can't findanyway to do this in a stored SQL procedure.Thanks for any suggestions, Eric.
i'm trying to create a stored procedure that takes 2 input parameters (taken from a querystring): a table name, and a number that needs to be checked whether it exists within that table. yes, i have different tables, one for each forum, so they will have the same structures -- i'm figuring this approach will result in faster page-load times if the tables have less in them. if everyone here tells me that having one table for all the forums will work fine, i'll just go that route, but if not, here's my procedure:
Create Procedure VerifySubjectNum (@forum_ID VARCHAR(10), @subject_num INT) As If Exists (SELECT subject_num FROM @forum_ID WHERE subject_num = @subject_num) Return 1 Else Return 0
when i try to create this, i get an error saying @forum_ID must be declared... why doesn't it work within the select for the EXISTS?
Hi folks, I have a procedure that pefroms some action and creates the outputs to a temporary table #mytable. I want to call this procedure and take the results from #mytable within the procedure. Can i. If i call #mytable after executing the procedure; won't work. Means that the table gets dropped and doesn't prolong for the session?
