Sql user defined function to replace HTML text without disturbing HTML elements

You may find many articles on how to strip HTML in SQL. But we rarely find articles on doing replace functionality without disturbing the HTML tags in SQL. This is a very useful requirement where we store content in HTML format in the database.

When I did Google search on this I found some articles on how to strip HTML from SQL and one of those articles inspired me to write a user defined function which does replace within the text and preserves the HTML as it is.

The below is the user defined function written by me as inspired from the article SQL SERVER – 2005 – UDF – User Defined Function to Strip HTML – Parse HTML – No Regular Expression

CREATE FUNCTION [dbo].[udf_ReplaceHTMLContent]
(
@HTMLText VARCHAR(MAX),
@Find VARCHAR(MAX),
@Replace VARCHAR(MAX)
)

RETURNS VARCHAR(MAX)
AS
BEGIN

DECLARE @Start INT
DECLARE @End INT
DECLARE @Length INT
DECLARE @UpdatedHTMLText VARCHAR(MAX)

SET @UpdatedHTMLText = ''
SET @Start = CHARINDEX('<',@HTMLText)
SET @End = CHARINDEX('>',@HTMLText,CHARINDEX('<',@HTMLText))
SET @Length = (@End - @Start) + 1


WHILE @Start > 0
AND @End > 0
AND @Length > 0

BEGIN

SET @UpdatedHTMLText = @UpdatedHTMLText + Replace(SUBSTRING(@HTMLText, 0, @Start), @Find, @Replace)

SET @UpdatedHTMLText = @UpdatedHTMLText + SUBSTRING(@HTMLText, @Start,@Length)

SET @HTMLText = STUFF(@HTMLText,1, @Start + @Length - 1,'')

SET @Start = CHARINDEX('<',@HTMLText)

SET @End = CHARINDEX('>',@HTMLText,CHARINDEX('<',@HTMLText))

SET @Length = (@End - @Start) + 1

END


if @UpdatedHTMLText = ''

SET @UpdatedHTMLText = @UpdatedHTMLText + Replace(@HTMLText, @Find, @Replace)


RETURN LTRIM(RTRIM(@UpdatedHTMLText))

END



table variables in sql server 2005

Table variable is almost similar to the temporary table in sql server 2005. Table variables are replacement to temporary tables. The following syntax shows how to define a table variable.

declare @Customer table
(
CustomerID int identity(1,1),
CustomerName varchar(100),
CompanyName varchar(100),
Address varchar(250)
)

You can notice the syntax to define a table variable is similar to the syntax to normal table definition. declare keyword is used in place of create keyword. And table name is prefixed with '@' as all tsql variables do.

1. Table variables are stored in memory rather than in database.
2. Querying table variables is very fast as there are no disk reads needed.
3. The scope the table variables is same as other tsql variables. i.e, within statements batch or sproc
4. Table variables cannot be dropped as they are automatically disappeared when they reach out scope
5. As explained above all the data in table variables is stored in server's memory. So if there is huge data then it is not recommended to use table variables to avoid memory overhead.
6. If the number of records is more than 100 then it is recommended to use temporary tables. To learn more about temporary tables please readi this blog post http://cherupally.blogspot.com/2009/05/how-to-create-temporary-table-in-sql.html
   

Implementing Custom Paging, Sorting and Dynamic query in sql

This post explains and demonstrates how to implement following things

1. Custom Paging: Returning a set of records for the given page number and page size
   
2. Dynamic query: Querying based on the where string passed thru parameter
   
3. Dynamic sorting: Sorting based on the sort expression passed thru parameter
   
4. SP_ExecuteSql: Executing sql statements included within parameters
   
5. Common Table Expression: Similar to table variable
6. function row_number(): returns the current row number in a result set

The following code is to create the stored procedure GetCustomersByPage. This stored procedure accepts the parameters page size, page number to return the page, where string (It must start with 'and' keyword if a condition is added. Ex: " and CompanyName like '%soft%' " or empty is accepted but applies no filter), sort expression to get the sorted records and total count which is useful when implementing the paging logic in user interface.

The sample sproc is written for NorthWind database. If you have NorthWind database installed then you can run this sproc and see how it works.

create procedure GetCustomersByPage
@PageSize int,
@PageNumber int,
@WhereString varchar(max),
@SortExpression varchar(200),
@TotalCount int output
as
begin

declare @FirstRecord int
declare @LastRecord int


set @FirstRecord = (((@PageNumber - 1) * @PageSize) + 1)
set @LastRecord = (@PageNumber * @PageSize)

declare @sql nvarchar(max)
DECLARE @ParmDefinition NVARCHAR(500)
SET @ParmDefinition=N'@TotalCount int OUTPUT'

set @sql = 'select @TotalCount = count(*) from dbo.Customers where 1=1 '+@WhereString+';
with PagedCustomers as (select row_number() over(order by '+@SortExpression+') as RowNumber, * from dbo.Customers where 1=1 '+@WhereString+')
SELECT * from PagedCustomers where RowNumber between '+cast(@FirstRecord as varchar)+'and '+cast(@LastRecord as varchar)

exec sp_executesql @sql, @ParmDefinition, @TotalCount=@TotalCount output

return @TotalCount
end

Return multiple result sets using IMultipleResults in linq c#

As we know that LINQ introduced a new feature which lets us to get multiple result sets from the stored procedure. But there is one important which all should know about IMultipleResults. The order of the GetResult's should be same as the order of the select statements in the sproc. This must be followed in order to avoid unexpected errors. I spent many hours on this to get rid of this problem.

For better understanding please go through the following

The following tsql code shows how the spoc which returns multiple result sets look like

CREATE PROCEDURE GetMultipleResults
@SomeID int
AS
BEGIN
SELECT * FROM SomeTable1 where SomeColumn1=@SomeID
SELECT * FROM SomeTable2 where SomeColumn2=@SomeID
SELECT * FROM SomeTable3 where SomeColumn3=@SomeID
SELECT * FROM SomeTable4 where SomeColumn4=@SomeID
END

The below code shows how to get the IMultipleResult object by calling the above sproc using LINQ

[Function(Name = "dbo.SPROCName")]
[ResultType(typeof(ResultSet1))]
[ResultType(typeof(ResultSet2))]
[ResultType(typeof(ResultSet3))]
[ResultType(typeof(ResultSet4))]
public IMultipleResults SomeMethod([Parameter(DbType = "INT")] int? SomeID
{
IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), SomeID);
return ((IMultipleResults)(result.ReturnValue));
}


The below code shows how to read the result set from the object of IMultipleResult type

public void SomeOtherMethod(int SomeID)
{
DataContext1 context = new DataContext1 (dbConnString);
IMultipleResults results = context.SomeMethod(SomeID);

ResultSet1 resultSet1= results.GetResult().FirstOrDefault();
IEnumerable resultSet2 = results.GetResult();
IEnumerable resultSet3 = results.GetResult();
ResultSet4 resultSet4= results.GetResult().FirstOrDefault();
}

If you observe the above code you can notice that the method is reading individual result sets from the IMultipleResult object in the same sequence as the sproc returns the results. This is very important that the order of the IMultipleResults.GetResult() statements for the result sets should be same as the order of the select statements in sproc. If the order is different then you will get runtime errors or invalid objects and many more issues.

Exception handling in Sql server 2000

When I was working on a project, I had written a sproc which runs in sql server 2005. I had used Try Catch blocks to handle the exceptions and roll back the transaction for any exception. My script was working fine with sql-2005. But when I had to deploy on production, I noticed that we had sql server 2000 on which our legacy database was sitting. When I ran my script it was failing to create the sproc due to try catch blocks. After doing a while research on it I found that sql server 2000 deals with exceptions in different way.

When any exception is raised then @@ERROR will be set to a non zero value. If you want to check whether exception has araised you should check

@@ERROR <>0 -> Exception occurred

@@ERROR =0 -> No Exception

To handle exceptions in sql server 2000 we need to check the condition @@ERROR <>0 for every insert/update/delete statement and based on that we need to roll back the transaction if used.

Example:

BEGIN TRAN

DELETE EMP WHERE EmployeeType = ‘ProjectManager’

IF (@@ERROR <>0)

GOTO HandleError;

UPDATE EMP SET Salary = Salary * 1.30, EmployeeType = ‘ProjectManager’ WHERE EmployeeType = ‘TeamLead’

IF (@@ERROR <>0)

GOTO HandleError;

COMMIT TRANS

RETURN;

HandleError:

ROLLBACK TRAN

RETURN;

Setting Execution Order of Triggers in Sql Server

I had multiple triggers defined for update on a table. I got a requirement to force the execution for the triggers to be done in a defined order. I had searched some websites and found that it is possible using the built in sproc sp_settriggerorder

sp_settriggerorder [@triggername = ] ‘triggername‘
, [@order = ] ‘value‘
, [@stmttype = ] ‘statement_type‘

Argument

[@triggername = ] ‘triggername‘

Is the name of the trigger whose order will be set or changed. triggername is sysname. If the name does not correspond to a trigger or if the name corresponds to an INSTEAD OF trigger, the procedure will return an error.

[@order = ] ‘value‘

Is the setting for the new trigger order. value is varchar(10) and it can be any of the following values.

Important The First and Last triggers must be two different triggers.

Value	Description
First	Trigger will be fired first.
Last	Trigger will be fired last.
None	Trigger will be fired in undefined order.

[@stmttype = ] ‘statement_type‘

Specifies which SQL statement fires the trigger. statement_type is varchar(10) and can be INSERT, UPDATE, or DELETE. A trigger can be designated as the First or Last trigger for a statement type only after that trigger has been defined as a trigger for that statement type. For example, trigger TR1 can be designated First for INSERT on table T1 if TR1 is defined as an INSERT trigger. SQL Server will return an error if TR1, which has been defined only as an INSERT trigger, is set as a First (or Last) trigger for an UPDATE statement. For more information, see the Remarks section.

Search for keywords in stored procedures

When I was working on a project I got a requirement to know how certain columns/tables are populated, which sprocs are populating them. Then I googled for a while about it and got the link about information_schema.routines. information_schema.routines is a system view which returns all the sprocs defined in the database.

You can have a look at the below example to know the usage of it. The below query shows the list of sprocs whose definition contains the word "customers". In general terms I am using this query to know what all sprocs are operating on the table "customers"

use northwind
go
select routine_name,routine_definition from information_schema.routines where routine_definition like '%customers%'

This is very useful when there is a huge database with many number of tables and many sprocs.

It was useful to me as our project was very complex and huge.