How to query a stored procedure in SQL

If you have ever needed to query an existing stored procedure in an ad-hoc SQL query then probably the most common method is to create a temp table (or table variable), execute the sp, and insert the result set into the temp table (see stack overflow article here). But this involves manually specifying the columns, and if your result set has more than a few columns it can be tedious and off-putting.

Fortunately there is another solution that allows a direct query of a stored procedure's result set without having to build temp tables (thanks to Erland Sommarskog for the info).

OPENQUERY allows you to query a stored procedure in the same way you query a base table, similar to querying a query in MS Access, and even allows joins between different sps.

For example, if I have two stored procs on my local server, PaymentUpsert and MilestoneUpsert, I can run the following select statement using OPENQUERY:

SELECT p.Status__c
,m.Status__c
FROM OPENQUERY(LT010, 'EXEC StagingArea.dbo.PaymentUpsert') AS p
JOIN OPENQUERY(LT010, 'EXEC StagingArea.dbo.MilestoneUpsert') AS m
ON p.[Milestone__r:External_Id__c] = m.External_Id__c
WHERE p.Status__c = 'Completed';


If you get an error telling you that your server instance is not configured for data access then you can run the following query to allow access:

EXEC sp_serveroption 'LocalServerName', 'DATA ACCESS', TRUE;

Be aware that setting your local server data access to true may have security implications for you and your network. See data acess article on technet and also article on openquery.

Convert dates to Salesforce format in T-SQL

When preparing csv files for import through the data loader, it is best practice to format the datetime values in the full yyyy-mm-ddThh.mm.ss.sssZ format required by Salesforce.com. This prevents any complications with Data Loader or other migration tools which may have different settings depending on the environment or installed software (for example, dates can be scrambled if your data loader has 'Use European Date Format' checked, but your client's data loader does not.

CONVERT float to nvarchar

Attempting to compare company numbers (e.g. ABN) stored as datatype float to text strings is complicated by the fact that the default display for float numbers over 6 digits is scientific notation. To work around this you need firstly to convert the float to a bigint, then convert to nvarchar.

Differences between views and temp tables

In a relational database like SQL Server, views provide a way of giving users a way to work with specific portions of a larger schema. Temporary tables are another means of providing end users with a subset or collation of data from base tables, and at first glance it seems that these are similar methods of achieving the same end. So it is important to understand what the difference is between a view and a temp table.

T-SQL not equal to NULL

This is a well known problem addressed elsewhere, but I want to log it here anyway. When joining two tables and checking for records where a value in one does not equal a value in the other, T-SQL fails to find records where the reference table has recorded a NULL value. Presumably this is because a NULL value is unknown, so there is no way of knowing whether the two values are equal or not.

CONVERT CAST dates in T-SQL

Sometimes you need to compare dates as text rather than in date format, and this can be especially useful when comparing dates from different systems using different data types. But T-SQL makes it easy to format different dates using the CONVERT function.

SQL Server intellisense disabled after windows update

Last week my corporate network administrator pushed out a couple of windows updates for SQL Server 2008 R2 and Visual Studio 2010. After installing them I discovered that my intellisense in SQL Server management studio had disappeared. No matter what I tried, I couldn't re-enable it.

The intellitrace in VS2010 SP1 causes an issue with the intellisense in SSMS 2008 R2 and Microsoft are aware of the problem and have issued a hotfix. Scott Guthrie blogged about the issue but doesn't mention the fix, and Microsoft don't make it easy to get your hands on it either.

http://support.microsoft.com/kb/2531482

Add a unique constraint to a nullable column

Enforcing uniqueness is a simple matter of adding a UNIQUE constraint with the following code:

ALTER TABLE [dbo].[TableName]
ADD CONSTRAINT UNQ__TableName__ColumnName UNIQUE ([ColumnName]);

But if your column includes null values, then multiple nulls are considered non-unique and they are not allowed.

Add a LastModified column to SQL Server table

I had a job recently where I needed to find the delta from a number of Microsoft Access database tables. The db designer had made a lot of interesting architectural decisions, but the biggest problem I had was finding which records had been touched in the past few months. It is easy enough to detect the changed tables, but at a field level I had to run a diff of the old and new copies and extract the changes using DiffMerge.

UPDATE table1 from table 2

I'm using a SQL Server staging area for my current ETL work and I find it easiest to update cleaned IDs using temporary tables that can be created and deleted as necessary. The syntax is simple and I use it in stored procs for persistent matched data and ad hoc queries for manual matching.

NOT IN sub-select query with NULL values

I was wondering why my NOT IN query was failing to return the expected amount of records, so I googled and found the 'SQL with Manoj' post at wordpress. If there are NULL values returned by your sub-select query then you need to handle the NULL values explicitly.