I have a table for a menu system with the following structure, and some data. ID, Text, ParentID, DestinationID 1, Applications, (null), (null) 2, Games, (null), (null) 3, Office, 1, (null) 4, Text Editing, 1, (null) 5, Media, (null), (null) 6, Word, 3, 1 7, Excel, 3, 2 8, Crysis, 2, 3 What I need is a query to which I can pass the m...
This question is very similar to SQL Server 2005: T-SQL to temporarily disable a trigger However I do not want to disable all triggers and not even for a batch of commands, but just for one single INSERT. I have to deal with a shop system where the original author put some application logic into a trigger (bad idea!). That application ...
Hi, I want to check if SQL Server(2000/05/08) has the ability to write a nested stored procedure, what I meant is - WRITING a Sub Function/procedure inside another stored procedure. NOT calling another SP. Why I was thinking about it is- One of my SP is having a repeated lines of code and that is specific to only this SP.So if we have...
Hi, I've got a query that returns the cost of wages for a given member of staff SELECT totalhours * staffbaserate AS TotalCost FROM newrotaRaw WHERE staffref = @staffref However I need to do an additional bit of maths if the returned value is > 105. The bit of maths I need to do is that if the value is < 105 the value...
I have an interesting query to do and am trying to find the best way to do it. Basically I have an absence table in our personnel database this records the staff id and then a start date and end date for the absence. End date being null if not yet entered (not returned). I cannot change the design. They would like a report by month on n...
Example Query: select * from A join B on A.ID = B.SOMEVALUE where A.VALUE="something" and B.ID = (select ID from B where SOMEVALUE = A.ID and THISDATE = (select max(SOMEDATE) from B where ...)) so, if you can read SQL you should see that I am doing a couple correlated subqueries to narrow down the results of th...
I'm using Linq-to-SQL with a SQL Server backend (of course) as an ORM for a project. I need to get the result set from a stored procedure that returns from a dynamically-created table. Here's what the proc looks like: CREATE procedure [RetailAdmin].[TitleSearch] ( @isbn varchar(50), @author varchar(50), @title varchar(50)) as declare ...
I'm supporting an existing application written by another developer and I have a question as to whether the choices the data type the developer chose to store dates is affecting the performance of certain queries. Relevant information: The application makes heavy use of a "Business Date" field in one of our tables. The data type for t...
I need to write a select query that joins the tables based on a condition (in this case, based on a value in one of the columns). I would like to do something like this: SELECT * FROM TableA INNER JOIN TableB ON (TableA.Column1 = TableB.Column1 OR TableA.Column1 = 0) -- Does not work! ...
If I have a Tables: Projects(projectID, CreatedByID) Employees(empID,depID) Departments(depID,OfficeID) Offices(officeID) ("CreatedByID" is a foreign key for employees) And I have a query that I need to run for almost every request of a web application that grabs all projects in an office. Is it bad practice to just add a redundant "...
We use Guids as primary keys for entities in the database. Traditionally, we've followed a pattern of letting the database set the ID for an entity during the INSERT, I think mostly because this is typically how you'd handle things using an auto-increment field or whatever. I'm finding more and more that it's a lot handier to do key as...
Hello all, I'm lousy at SQL queries so this may be a silly question. However, here is roughly what i'd like to do: table corpuses //stuff i'd like to search thru title body ... table searches //list of search terms to be applied to corpuses term ... The query i'd like to write is more or less as foll...
I am trying to set every row's CheckColor to Blue in the table tblCheckbook (why the hell do people add tbl to the start of every table, I think I know it's a table). I'm using this query UPDATE tblCheckbook SET CheckColor = Blue However, Microsoft SQL Server Management Studio Express complains Invalid column name 'Blue'. T...
I want to implement an atomic transaction like the following: BEGIN TRAN A SELECT id FROM Inventory WITH (???) WHERE material_id = 25 AND quantity > 10 /* Process some things using the inventory record and eventually write some updates that are dependent on the fact that that specific inventory record had sufficient quantity (greater ...
I'm creating an AIR application which connects to a SQLite database. The database balks at this insert statement, and I simply cannot figure out why. There is no error, it simply does not move on. INSERT INTO Attendee (AttendeeId,ShowCode,LastName,FirstName,Company,Address,Address2,City,State,ZipCode,Country,Phone,Fax,Email,BuyNum,Prim...
I'm trying to perform a group by action on an aliased column (example below) but can't determine the proper syntax. SELECT LastName + ', ' + FirstName AS 'FullName' FROM customers GROUP BY 'FullName' What is the correct syntax? EDIT Extending the question further (I had not expected the answers I had received) wou...
I am using SQL Server 2005, I want to find out what all the grants are on a specific database for all tables. It would also help to find out all tables where the delete grant has been given for a specific user. Note: this may be similar to this question, but I could not get the selected answer's solution working (if someone could provid...
Hello, I was working for a telecom company some years ago and I had to generate a formula which calculates duration of a call according to the following algorithm: t1 is the first period t2 is the recurring period RCT is the actual call time (in seconds) CD is the effective call duration (for billing purposes) if RCT is less than ...
I'd like to update a set of rows based on a simple criteria and get the list of PKs that were changed. I thought I could just do something like this but am worried about possible concurrency problems: SELECT Id FROM Table1 WHERE AlertDate IS NULL; UPDATE Table1 SET AlertDate = getutcdate() WHERE AlertDate IS NULL; If that is wrapped i...
I want to create a SQL Select to do a unit test in MS SQL Server 2005. The basic idea is this: select 'Test Name', foo = 'Result' from bar where baz = (some criteria) The idea being that, if the value of the "foo" column is "Result", then I'd get a value of true/1; if it isn't, I'd get false/0. Unfortunately, T-SQL doesn't like the ex...