SQL WHERE statement?
What should my WHERE clause be in a SQL Statement in which I want to return those rows where column A is null or column B is null, but not where both are null? ...
tsql
SELECT INTO a table variable in T-SQL
Got a complex SELECT query, from which I would like to insert all rows into a table variable, but T-SQL doesn't allow it. Along the same lines, you cannot use a table variable with SELECT INTO or INSERT EXEC queries. http://odetocode.com/Articles/365.aspx Short example: declare @userData TABLE( name varchar(30) NOT NULL, oldloca...
Adding one month to date if two values fall withinin same month, and then repeating the validation check (recursion?)
I have some rather dodgy data I'm looking at, and I've been tasked with finding out for how many consecutive months something has been going on for. However I have noitced that in some of my reference tables individual date values are being miscoded, and where there are multiple values in one month, the latest should be added into the ne...
Add a Specific Time to a Date Value
I have a stored procedure that receives a date value like this: @AsOf date In the query I am using this date to filter a datetime field. I need to convert the date passed in to a datetime field with the time portion set to 09:00:00 PM so I can add a filter in the WHERE clause like this: (DateCreated < @TimeAsOfNinePM) I know this i...
Transpose row values to columns ( join table with a bag of key value pairs in T-SQL)
I have two tables, table1 ( Id, date, info1) table2 ( Id, date, nvarchar(50) Key, nvarchar(50) Value) I would like to join these tables and obtain rows where each value in the Key column is a new column, and the values in the value table are the data in the rows. Example: table1 row: 1, 2010-01-01, 234 table2 row: 1, 2010-01-01,...
Set multiple fields in a single case statement
I'm not as familiar with SQL Server 2008 and I haven't seen an answer to this question anywhere else. Does TSQL in SQL Server 2008 allow for multiple fields to be set in a single case statement. For instance: case MyField when 1 then ThisField = 'foo', ThatField = 'bar' when 2 then ThisField = 'Mickey', ThatField = 'Mouse' e...
get date of all saturdays in a given year - sql server
I need to get a list of all Saturday dates in a given year. I've seen an oracle post that goes against a table that had "fiscal calendar table" but I haven't been able to succeed in converting it nor do I have a table that contains a set of dates I want to investigate. SELECT DATE DATES,TO_CHAR(DATE,'DAY') DAYS FROM FISCAL_CALENDAR WHE...
Rules are deprecated, what's instead (TSQL)?
Rules (Transact-SQL)[1] are reusable what permitted to overcome the shortcoming of non-re-usability of check constraints. And now I read [1] that: "This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feat...
nested set and treeview control
Hi All, Given the following database tables and sample data: Locations Id ParentId LeftIndex RightIndex Description -- -------- --------- ---------- ----------- 34 2 85 104 Florida Region 73 34 94 95 Miami Products Id ParentLocationId Code Description -- ---------------- -...
Getting result of dynamic SQL into a variable
Hi, Executing dynamic SQL as follows in Stored Procedure: DECLARE @sqlCommand nvarchar(1000) DECLARE @city varchar(75) SET @city = 'London' SET @sqlCommand = 'SELECT COUNT(*) FROM customers WHERE City = @city' EXECUTE sp_executesql @sqlCommand, N'@city nvarchar(75)', @city = @city How do I use the count(*) column value as return valu...
Do stored procedures lock tables/rows?
Quite a simple question. In SQL 2008 if I have a stored procedure (see below) do I run the risk of a race condition between the first two statements or does the stored procedure put a lock on the things it touches like transactions do? ALTER PROCEDURE [dbo].[usp_SetAssignedTo] -- Add the parameters for the stored procedure here ...
select column name from max query
I have a query that goes something like this : ;WITH t as ( select 1 as RowNumber, 1 as ObjectID, 10 as [Col1], 20 as [Col2], 20 as [Col3], 20 as [Col4] UNION ALL select 2 as RowNumber, 2 as ObjectID, 20 as [Col1], 30 as [Col2], 40 as [Col3], 50 as [Col4] ) SELECT RowNumber, ObjectID, ( SELECT MAX(Amount) ...
CTE query plan for complex query - triple run of the same init query - why?
It's difficult to explain, but I'll try. As you see on query_plan picture attached (It is query plan for "All in one place" query described below), there are 3 almost the same "blocks" - my question is WHY? It seems to me that when I have "all in one" (see below) query the "Init" block (that is rather heavy) is run three times with di...
T-SQL IF condition is not working as expected
I have a stored procedure that will INSERT a record into a table. I have created a unique key constraint on the table to avoid duplicate records. Regardless of the fact that there is a unique key constraint defined, I am using an IF() condition (prior to the INSERT statement) to check for a duplicate record that may already exist. How...
TSQL increment a colum and start over based on another column
I have a table as follows: colA colB(unique) colC --------------------------------- 1 1449 0.50000000 1 1451 1.03400000 1 2404 5.98750000 1 1454 6.00000000 3 1465 1.40000000 3 1467 1.56000000 3 1476 3.00000000 3 1469 ...
how to see/script definitions of system views?
I tried to execute scripts from [1] in model database and user-defined databases but they give definitions/scripts of only user-defined (non-system) views, i.e. those that I anyway can easily get from GUI. How can I see/script the definition/script of a system view in SQL Server 2008 R2? [1] Answers to question "System Views text i...
Get new ID before insert during transaction in .NET
I'm using ADO.NET to try to get the value I'm about to insert before I insert it in SQL Server 2005. SCOPE_IDENTITY() works fine after, but I'm also inside a transaction if that makes a difference. I literally need to select the next ID, and only the ID and have it available to C# before the insert. ...
Help me write a SQL Crosstab query
I have 3 tables that look like this: tblVideo: VideoID | Video Name 1 video 1 2 video 2 3 video 3 4 video 4 tblCategory: CategoryID | CategoryName 1 category1 2 category2 3 ...
select 10 rows with lowest time difference in sql
I'm using sql-server 2005 Hi, i have Users table with userID and registrationDate. I want to select shortest period of time between two registrationDates when first date is x and other row is x+10 rows. I don't mind cursor because i will run this query once in a while. I will explain again, i need shortest period of time between 10 us...
XQuary adding or replacing attribute in single SQL update command
I have a Table with an XML column, I want to update the xml to insert attribute or to change the attribute value if the attribute already exists. Let's say the starting xml is: < d /> Inserting: UPDATE Table set XmlCol.modify('insert attribute att {"1"} into /d[1]') Changing: UPDATE Table set XmlCol.modify('replace value of /d[1]/...