Let’s say you’re writing T-SQL code and need to make sure that your query returns one and only row. If no records returned – an error message needs to be shown. If more than one record is returned – another error message needs to be show. The code goes something like this:
-- ... query runs here and returns row
IF @@ROWCOUNT = 0 RAISERROR('No records found', 18, 1)
ELSE IF @@ROWCOUNT > 0 RAISERROR('More than one record found', 18, 1)
-- ... continue when exactly one row is returned
Now if your query returns 3 records you’d expect it to trip the second IF statement and raise error with correct message. Unfortunately this doesn’t happen. The reason being – the first IF statement resets @@ROWCOUNT to 0. So, to avoid this we need to preserve the @@ROWCOUNT value in a local variable:
DECLARE @iRowCount int
-- ... query runs here and returns row
SET @iRowCount = @@ROWCOUNT
IF @iRowCount = 0 RAISERROR('No records found', 18, 1)
ELSE IF @iRowCount > 0 RAISERROR('More than one record found', 18, 1)
-- ... continue when exactly one row is returned
This way count of rows returned by the query is saved and is not affected by any following statements.
Let’s say you have a table with some dates and numeric values e.g.:
2011-08-19 16
2011-08-22 45
2011-08-24 62
2011-08-25 88
2011-08-27 17
2011-08-28 35
2011-09-01 10
2011-09-02 79
2011-09-03 70
2011-09-07 83
As you can see not all dates are in the sequential order, 24th comes after 22nd etc. But in many cases you need this data to be sequential, for example if this data feeds a chart you need to fill it with missing dates and 0 for a value. There is an easy way to achieve this. Continue reading 'TSQL: Filling missing date range'»
This was driving me nuts. I have a very basic SQL code similar to
ALTER PROCEDURE Proc1(@val1 NVARCHAR(max))
AS
BEGIN
-- some code
EXEC Proc2 @val2 = @val1
-- some other code
END
I was getting error “String or binary data would be truncated”, but only when 2 conditions were met:
- @val1 is quite large
- Either SQL Server/computer just restarted or stored procedure Proc2 was just updated (ALTER)
Error happened just by the fact of the EXEC Proc2 being there it didn’t even had to do anything, it could RETURN straight away. Both @val2 an @val1 are of a type NVARCHAR(max) so there is no reason for the error. The error happened only once, after that I can pass data of any size – and no error would happen. Like I said – nuts. Continue reading 'Elusive “String or binary data would be truncated” error'»
UNION is a very common operator used in SQL, it allows to combine result of multiple queries into one. Unfortunately if you want to sort this unified resultset sorting is applied to entire result at once. But what if you wan to sort queries-participants individually?
Let’s say you have 2 following queries from Northwind database:
SELECT TOP (5) CompanyName FROM Suppliers ORDER BY CompanyName
Aux joyeux ecclésiastiques
Bigfoot Breweries
Cooperativa de Quesos 'Las Cabras'
Escargots Nouveaux
Exotic Liquids
and
SELECT TOP (5) ProductName FROM Products ORDER BY ProductName
Alice Mutton
Aniseed Syrup
Boston Crab Meat
Camembert Pierrot
Carnarvon Tigers
And now you want to combine them into a single result, adding title lines to separate each result. The direct approach would be:
SELECT 'Suppliers' AS Name
UNION
SELECT TOP (5) CompanyName FROM Suppliers
UNION
SELECT 'Products'
UNION
SELECT TOP (5) ProductName FROM Products
ORDER BY Name
But result is far from what we wanted
Alice Mutton
Aniseed Syrup
Aux joyeux ecclésiastiques
Bigfoot Breweries
Boston Crab Meat
Camembert Pierrot
Carnarvon Tigers
Cooperativa de Quesos 'Las Cabras'
Escargots Nouveaux
Exotic Liquids
Products
Suppliers
Entire resultset is sorted uniformally and products are mixed with suppliers.
Continue reading 'Individual Sorting of SELECT queries in the UNION'»
It’s a common scenario, your table has several records with identical values and you need to leave only one, deleting the rest. Here is a generic TSQL query (SQL Server 2005 and above) that does just that in a few lines:
WITH DUP_TABLE AS
(SELECT ROW_NUMBER()
OVER (PARTITION BY FIELD1, FIELD2 ORDER BY FIELD1, FIELD2) As ROW_NO
FROM ORIGINAL_TABLE)
DELETE FROM DUP_TABLE WHERE ROW_NO > 1
Here ORIGINAL_TABLE is your table with duplicates. FIELD1 and FIELD2 are columns with duplicates value (feel free to add or remove columns to suit your needs). Internal query assigns a row number to each duplicate record and DELETE statements that uses that CTE deletes all the rows except the one with Row Number = 1
Consider following scenario: While developing for SQL Server Reporting Services you created a report in Business Intelligent Studio (or generated RDL file elsewhere and then added it to Studio’s project). Everything works fine, you’re able to preview report. And then you need to change Data Source (one example: you created a shared Data Source and now want your report to use it). After you do that any attempt to run the report results in error: Query execution failed … Expected parameter ….
The reason in this case – when you change the data source thru GUI – it resets query used by the report. It can change query type from StoredProcedure to Text and also remove all default query parameters (hence error above).
There’re 2 possible solutions here: First – instead of using GUI, manually change Data Source in the XML source of the report file (in Solution Explorer right mouse click on the report file and select “View Code”). You will have to change it in several places, so be careful. Second – re-create query with its default parameters after changing Data Source in GUI.
If you’re writing an SQL upgrade script that updates a field in your table from NVARCHAR(n) data type to NVARCHAR(max) and the script is designed to run many times – an easy way to avoid multiple table alteration is to check for max character length for that field, i.e.:
IF (SELECT CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'MY_TABLE' AND COLUMN_NAME = 'MY_COLUMN')<>-1
BEGIN
ALTER TABLE [dbo].[MY_TABLE] ALTER COLUMN [MY_COLUMN] [nvarchar](max) NOT NULL
END
This code checks length of the field which is specific number for standard NVARCHAR and -1 for NVARCHAR(max), and alters column type only if it hasn’t already been altered.
Let’s say you need to add a column to an existing table that already contains some rows. And this new column needs to be prepopulated with a default value. For example integer nullable column NEW_COLUMN is added to table MY_TABLE. Common approach is to check for column existence, and if it doesn’t exist – create and populate it:
if not (exists(SELECT * FROM SYSCOLUMNS WHERE ID = OBJECT_ID('[dbo].[MY_TABLE]') AND Name = 'NEW_COLUMN'))
BEGIN
ALTER TABLE [dbo].[MY_TABLE] ADD [NEW_COLUMN] [int] NULL
UPDATE [dbo].[MY_TABLE] SET [NEW_COLUMN] = 1
END
But this doesn’t work – you get “Invalid column name” error. The trick is – you don’t need a separate UPDATE statement. ALTER TABLE … ADD statement has a DEFAULT parameter. You can modify previous code like this:
if not (exists(SELECT * FROM SYSCOLUMNS WHERE ID = OBJECT_ID('[dbo].[MY_TABLE]') AND Name = 'NEW_COLUMN'))
BEGIN
ALTER TABLE [dbo].[MY_TABLE] ADD [NEW_COLUMN] [int] NULL DEFAULT 1 WITH VALUES
END
This ALTER statement assigns default value of 1 to the new column. WITH VALUES parameter is important, it populates existing rows with the default value (without it it will be populated by NULLs).
Sometimes I bring information to a couple of my other WordPress blogs via RSS feed. It’s a nice feature, allowing you to create several posts at once without manual entry. Unfortunately if RSS feed is broken or improperly formatted it can result in blank posts imported into the blog.
I was looking for a WordPress plugin that would allow me to mass-delete empty posts, but apparently none exist. You can delete posts based on date, tags, category, but not the content. Fortunately if you have access to phpMyAdmin of your MySQL installation – there is a solution. Continue reading 'WordPress MU: Delete Empty Posts'»
Imagine after running a query like this:
SELECT ContactName, Country FROM Customers ORDER BY Country
on Northwind database and getting following result:
ContactName Country
Patricio Simpson Argentina
Yvonne Moncada Argentina
Sergio Gutiérrez Argentina
Georg Pipps Austria
Roland Mendel Austria
Catherine Dewey Belgium
Pascale Cartrain Belgium
Anabela Domingues Brazil
Paula Parente Brazil
Bernardo Batista Brazil
Lúcia Carvalho Brazil
Janete Limeira Brazil
Aria Cruz Brazil
André Fonseca Brazil
Mario Pontes Brazil
Pedro Afonso Brazil
Elizabeth Lincoln Canada
Jean Fresnière Canada
Yoshi Tannamuri Canada
...
You’re asked to retreive only the 2nd and the 5th group of contacts. How do you do it? Continue reading 'Select specific groups using DENSE_RANK'»
Comments (1) 
Themocracy