So, I have written before about how to put your database into version control using database projects in Visual Studio. Even while having the scripts in the solution, there can be times when you can’t exactly remember what you changed and needs to go out with your project deployment to the production database. Deploying files is easy because of tools like WinMerge, however deploying things to a database can get quite complicated. You could script both schemas and use WinMerge to see the differences between the development database and the production database. But even doing that, you will still have to write a custom script to get the production database schema up to date.

Enter xSql

xSQLSoftware This is where a tool such as xSQL Object can be extremely helpful. All that need be done is set up the connections, run the comparison, and then you can visually see what has changed between your development and live databases! No need to remember what you changed or any of that, just run the comparison and execute the change script. It will even allow you to save database snapshots before running your change scripts. You can see an excellent walkthrough here. xSQL Object also comes in a bundle with another one of their products, xSQL Data or by it’s self. xSQL Data allows you to compare data differences between two databases.

The Best Part

All the goodness of the xSQL Bundle (xSQL Object and xSQL Data) Lite Edition comes at a very affordable price FREE! If you only use SQL express edition then you can get the full bundle lite edition and it will work without any restrictions at all! However, if you need to use it against other versions of SQL Server it does have the following limitations:

• up to 25 tables
• up to 40 views
• up to 40 stored procedures
• up to 40 functions

If you are using it against a small database then you shouldn’t have any problems. Now if you have databases larger than this and are using SQL Server editions other than express, the product costs $399.00 for a single user license. BUT, after downloading it I was sent an email offering 30% off if I purchased the product within 7 days of the download. That brings the cost down to $280! Not too bad when you compare it with the prices of other comparable tools. So I ask you to go to the website and check it out if you haven’t already! http://www.xsqlsoftware.com

Update

After contacting the company about licensing, I was shown another one of their great tools. A little while back I wrote a post titled Finding Text in SQL Server Stored Procedures. They have a tool called xSQL Object Search that allows you to search for all object types, through the names and definitions, for strings. It will also do a search using regular expressions! Pretty powerful stuff for a **FREE **tool! Check it out here: xSQL Object Search

Happy Deployments!

So, I’m sure you have been met with a similar scenario during development. You know the one that you have to rename a column or even drop a column in the database. This can be quite annoying if you are doing stored procedure based data access for your application. Once you change the column on the table, you have to figure out which stored procedures reference the column. They aren’t always tough to find most of the time, but sometimes you are dealing with a column that may be referenced in many stored procedures. Well thanks to my boss Cliff’s research and knowledge sharing, your search can be as easy as ours!

The query:

1
2
3
4
5
6
7
8
9
10
11

USE Northwind
GO 
DECLARE @SearchText AS VARCHAR(50)
SET @SearchText = 'CustomerID'

SELECT
ROUTINE_NAME,
ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME LIKE '%' + @SearchText + '%'
OR ROUTINE_DEFINITION LIKE '%' + @SearchText + '%'

This query will return all the names and routine definitions of stored procedures that contain certain text. It is not really bound by column names but I needed a true development scenario. Run the query with what you are looking for and presto! All the stored procedures you will need to modify.

Hope this helps!

So this is what it has come to anymore. Everyone is all about server side paging via SQL Server. As well they should be! It is so much faster and more efficient than having ADO or ADO.NET bring back a ton of records and then chop it to page it. However, there has always been some problems when trying to accomplish this task, especially using a SQL database that is pre 2005.

This task is easier to accomplish in SQL 2005 and 2008 using the ROW_NUMBER() function. The part that gets flaky is having a dynamic order by clause in your SQL statement. Unfortunately, the only way to accomplish this is to write some dynamic SQL. In doing so, It can be hard to tell if the order by parameter received by the stored procedure is a valid one for the table you are selecting from.

Solution

Enter the "IsValidOrderBy" user-defined function. This is a little function that will tell you if the column and order in the dynamic order by parameter is a valid one for the select statement you are running.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

CREATE FUNCTION [dbo].[udf_OrderByExists] 
(
    @TableName NVARCHAR(50),
    @OrderBy NVARCHAR(50)
)
RETURNS BIT
AS
BEGIN
 
    DECLARE @Result BIT
    SET @Result = 0
 
    DECLARE @TableColumns TABLE
    (
        [ColumnNameAndSort] NVARCHAR(100) NOT NULL
    )
 
    INSERT INTO @TableColumns
    SELECT [Name] 
    FROM syscolumns 
    WHERE ID = OBJECT_ID(@TableName)
 
    INSERT INTO @TableColumns
    SELECT [Name] + ' ASC' 
    FROM syscolumns 
    WHERE ID = OBJECT_ID(@TableName)
 
    INSERT INTO @TableColumns
    SELECT [Name] + ' DESC' 
    FROM syscolumns 
    WHERE ID = OBJECT_ID(@TableName)
 
    IF EXISTS(SELECT [ColumnNameAndSort] FROM 
        @TableColumns WHERE [ColumnNameAndSort] = @OrderBy)
        SET @Result = 1
 
    RETURN @Result    
 
END

Here you can see that we are taking 2 inputs. The first one being the table name you are selecting from, and the second being the order by clause received by the stored procedure. The function will then return a bit telling you if the column and order was found for the table you are selecting from.

Example

A simple example of using this user defined function would be selecting from a table of products. In that case, your stored procedure could look like so

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53

CREATE PROCEDURE [dbo].[usp_GetProductsPaged]
    @SortExpression NVARCHAR(50),
    @PageIndex INT,
    @PageSize INT
AS
 
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
 
IF ((SELECT [dbo].[udf_OrderByExists]('dbo.Products', @SortExpression)) = 0)
    SET @SortExpression = 'Name'
 
DECLARE @sql AS NVARCHAR(MAX),
    @ParamDefinition AS NVARCHAR(MAX),
    @StartRowIndex INT,
    @RecordCount INT
 
SELECT @RecordCount = COUNT([ProductID]) FROM [Products]
 
IF @PageIndex = 0
    SET @PageIndex = 1
IF @PageSize = 0
    SET @PageSize = @RecordCount
SET @StartRowIndex = ((@PageIndex * @PageSize) - @PageSize) + 1
SET @ParamDefinition = N'@paramStartRowIndex INT, 
    @paramPageSize INT'
 
SET @sql = N'SELECT
    [ProductID],
    [Name],
    [Description],
    [Price]
FROM (SELECT
    [ProductID],
    [Name],
    [Description],
    [Price],
    ROW_NUMBER() OVER(ORDER BY ' + @SortExpression + ') AS [RowNumber]
FROM [Products]) AS [Prods]
WHERE [RowNumber] BETWEEN @paramStartRowIndex
        AND (@paramStartRowIndex + @paramPageSize) - 1'
 
-- For testing
--PRINT @sql
--PRINT @StartRowIndex
 
EXEC sp_executesql @sql, 
@ParamDefinition, 
@paramStartRowIndex = @StartRowIndex, 
@paramPageSize = @PageSize
 
SELECT @RecordCount AS [RecordCount]

As you can see, by calling **udf_OrderByExists **and passing in the parameters, if the order by does not fit the table, we then change it to be something known and valid.

Conclusion

With a simple and portable user defined function, we can ensure that the order by clauses going into our paging stored procedures are validated thus keeping integrity. It isn’t fun having to write and maintain dynamic SQL in stored procedures, but it can be done and also made a little bit safer. One last tip: Always use the sp_executesql, as this will tell the SQL server that the execution plan should be cached for re-use.

Hope this helps!