Visit: https://zonixsoft.com (our official Website)
A solution to get comma separated list of values of single field of a database table
You can use the following code to get the comma separated list of values of a single field of a table:
DECLARE @commaSeparatedVal AS VARCHAR(4000)
SELECT @commaSeparatedVal = ISNULL(@commaSeparatedVal +‘,’,”) + CONVERT(VARCHAR,[SKU]) FROM PRODUCT
PRINT @commaSeparatedVal
visit: https://zonixsoft.com (our official Website)
DIFFERENCE(string1, string2)
Returns the difference between two strings as a value between 0 and 4. This function uses the SOUNDEX algorithm to compare sounds, rather than characters. If the two strings match phonetically very well, the DIFFERENCE() function will return the value 4. If they don’t resemble each other even remotely, it will return the value 0.
The DIFFERENCE function is commonly used to search with names. Instead of a comparison like:
WHERE Authors.AuthorName = sAuthor
where sAuthor is the name supplied by the user, you can use the following comparison:
WHERE DIFFERENCE(Authors.AuthorName, sAuthor) = 4
The latter comparison will match “Mansfield” with “Mansfeeld”, “Petroutsos” with “Petrutsos”, and so on.
SOUNDEX(string)
Returns the four-character SOUNDEX code of a word. You can use this value to compare the closeness of two words. The SOUNDEX function converts a string to a four-character code: the first character of the code is the first character of the string argument, and the second through fourth characters of the code are numbers that code the consonants in the word (vowels are ignored unless they are the first letter of the string). The strings “Brannon”, “Branon”, and “Bramon” all have a SOUNDEX value of B655, because the sounds of n and m are very close. The word “Bradon”, however, has a SOUNDEX value of B635 (it differs by a consonant in the middle of the word).The word “Branok” has a SOUNDEX value of B652, since it differs in a consonant at the end.
SOUNDEX (Transact-SQL)
Definition:
Returns a four-character (SOUNDEX) code to evaluate the similarity of two strings.
Syntax :
SOUNDEX ( character_expression )
Arguments :
character_expression – Is an alphanumeric expression of character data. character_expression can be a constant, variable, or column.
Return Types :
varchar
Remarks :
SOUNDEX converts an alphanumeric string to a four-character code to find similar-sounding words or names. The first character of the code is the first character of character_expression and the second through fourth characters of the code are numbers. Vowels in character_expression are ignored unless they are the first letter of the string. String functions can be nested.
Examples :
The following example shows the SOUNDEX function and the related DIFFERENCE function. In the first example, the standard SOUNDEX values are returned for all consonants. Returning the SOUNDEX for Smith and Smythe returns the same SOUNDEX result because all vowels, the letter y, doubled letters, and the letter h, are not included.
-> Using SOUNDEX
SELECT SOUNDEX (‘Smith’), SOUNDEX (‘Smythe’);
Here is the result set.
No column name No column name
S530 S530
(1 row(s) affected)
The DIFFERENCE function compares the difference of the SOUNDEX pattern results. The following example shows two strings that differ only in vowels. The difference returned is 4, the lowest possible difference.
-> Using DIFFERENCE
SELECT DIFFERENCE(‘Smithers’, ‘Smythers’);
GO
Here is the result set.
No column name
4
(1 row(s) affected)
In the following example, the strings differ in consonants; therefore, the difference returned is 2, the greater difference.
SELECT DIFFERENCE(‘Anothers’, ‘Brothers’);
GO
Here is the result set.
No column name
2
(1 row(s) affected)
Visit: https://zonixsoft.com (our Official Website)
Definition :
Full text search is the search functionality that enables a fast and easy way of searching text based data in SQL Server.
Why Full Text Search?
Let us assume that we have huge amounts of data in the database and we are having some search functionality involved in User Interface (UI). The UI for the database is a website open to the internet so response time is crucial. Now if the administrator of the website wants to search all the customer names which have a specific pattern, then he or she queries the database system as shown in Listing 1.
Listing 1:
SELECT [NAME] FROM [CUSTOMER] WHERE [NAME] LIKE ‘%PATTERN%’The query in Listing 1 is fine for getting the result. But in real time this may not suffice the actual requirement. Let us see why the query in Listing 1 may not yield us actual results.
1. In case of a large amount of data, the like query works very slowly. It may take minutes to return the result. This is true especially for unstructured text data.
2. If I want to query my data on formatted binary data [format], I cannot use a Like query.
So to summarize, the need to of the hour is for a search functionality that is fast, efficient and can also be run on unstructured text data with the support of search on different data types like formatted binary. Full text search qualifies all these and in fact has many advantages.
Full-Text Indexing Architecture :
You might be a bit surprised to learn that SQL Server doesn’t handle its own full-text indexing tasks. Any version of Windows that SQL Server will run on includes an operating system component named the Microsoft Search Service. This service provides indexing and searching capabilities to a variety of applications, including SQL Server, Exchange, and SharePoint.
SQL Server uses an interface component, the SQL Server Handler, to communicate with the Microsoft Search Service. The Handler extracts data from SQL Server tables that have been enabled for full-text searching and passes it to the search service for indexing. Another component, the full-text OLE DB provider, gets invoked by SQL Server when you actually perform a full-text search. The provider takes the portion of the search that needs to be satisfied by the full-text index and passes it off to the Search Service for evaluation.
You need to be aware of one consequence of this architecture: because the full-text indexes are not in your SQL Server database, they can’t be backed up from within SQL Server. Instead, you need to backup the disk files created by the Search Service. You’ll find these files located under Program FilesMicrosoft SQL ServerMSSQLFTDATA.
Implementing Full Text search :
Let us discuss the steps for implementing full-text search in SQL Server 2005.
-> Adding a full text search catalog to the database – To add a full text search catalog we need to open the database node in Management studio and click on the storage node. We can see the Full text catalog in this node. We have an option for adding the full-text catalog by which we can add a new catalog.
-> Adding the Full text indexing table – We need to specify to the database that we want to implement the full-text search functionality for a table. To do this we can select the respective table and select the right click option of Full-Text Index and Define Full-Text index.
This opens up the Full text indexing wizard.
1. Using the Full text indexing wizard we select a unique index.
2. Once we select a unique index and move to the next step, we select the columns that are eligible for full text queries. This enables the full-text search for the selected columns.
3. We need to select the option of how can we track the changes. If automatically selected then a full population of indexing occurs. To avoid a population at the end of this wizard, select the Do Not Track Changes option, and clear the Start Full Population When Index Is Created checkbox.
4. Once we are done with step 3 we need to map the catalog out of all the catalogs at the database level. Or we could even create a new catalog for the full text search.
5. This is the last step of the wizard. Here we can create a table schedule and catalog schedules and schedule them with time. A table schedule can have a name, time of schedule and occurrences.
Once we create them and click on the finish button the full-text index is created.
Running the Full Text search :
Until now, we have set-up the full text search catalogs and indexes. Let us run the some queries for the full-text search. There are four types of predicates used in SQL Server 2005 for running the Full text search queries. A predicate is an expression that evaluates to TRUE, FALSE or UNKNOWN. A predicate is used in a where condition in T-SQL statement.
1. FREETEXT
2. FREETEXTTABLE
3. CONTAINS
4. CONTAINSTABLE
Let us see each one of them individually with its usage.
FREETEXT :
Freetext is the simplest form of predicates in the full text search. It searches for the words that give the similar meaning and not the exact wording of the word. This kind of predicate is used where we go to query a document as a whole for a word in it. The freetext not only returns the exact wording, but also the nearest meanings attached to it. Listing 1 shows the usage of the free text. To understand listing 1, the user should have a basic understanding of T-SQL queries. Let us also see the usage of Freetext with a help of an example. Listing 2 gives an example for the usage of FREETEXT.
Listing 2 :
FREETEXT ( { column_name | (column_list) | * }
, ‘freetext_string’ [ , LANGUAGE language_term ] )
Listing 3 :
SELECT CellularPhone FROM CART WHERE FREETEXT (CellularPhone, ‘1111’)
Listing 4 :
SELECT CELLULARPHONE FROM CART WHERE CELLULARPHONE LIKE ‘%1111%’
FREETEXTTABLE :
Freetexttable returns a collection of one or more rows as a table. The columns in the rows are selected based on the string given as the predicate. Freetexttable is similar to freetext except the former can return tables and the latter one can return columns. Listing 4 shows the usage of Freetexttable.
Listing 5 :
FREETEXTTABLE (table , { column_name | (column_list) | * }
, ‘freetext_string’
[ ,LANGUAGE language_term ]
[ ,top_n_by_rank ] )
CONTAINS :
As the name suggests, Contains actually works by verifying if a word or phrase is contained in the columns. It can search for a word, prefix of a word, synonym of a word, word formed from another word. We need to specify the language in case the languages of the words are not of the same language. Listing 5 shows the simple usage of contains column.
Listing 6 :
SELECT [Name], [Address]
FROM Customer
WHERE AGE > 30
AND CONTAINS([Name], ‘Uday’);
CONTAINSTABLE :
Contains and Containstable are similar to freetext and freetexttable. The former one returns columns and the latter one returns the table.
Conclusion :
Full-text search allows fast and flexible indexing for keyword-based query of text data stored in a Microsoft SQL Server 2005 database.
visit: https://zonixsoft.com (our official website)
In SQL Server 2008, Microsoft introduces the MERGE functionality through the MERGE command. The MERGE command inserts rows that don’t exist and updates the rows that do exist.
What MERGE Command works as,
IF FOUND
THEN UPDATE
ELSE
INSERT;
Until now, this MERGE functionality could only be made available in SQL Server through stored procedures and cursors, etc.
This article illustrates how to user MERGE command in SQL Server 2008.
Suppose we have 2 tables with same field structure as ID, ProductName and SKU,
MERGE INTO ProductTable1 AS Target
USING AS ProductTable2 AS Source
ON Target.ID = Source.ID
WHEN MATCHED THEN
UPDATE SET Target.ProductName = Source.ProductName,
Target.SKU = Source.SKU
WHEN NOT MATCHED THEN
INSERT (ID,ProductName,SKU) VALUES (Source.ID,Source.ProductName,Source.SKU);
Evaluates a list of conditions and returns one of multiple possible result expressions.
CASE has two formats:
· The simple CASE function compares an expression to a set of simple expressions to determine the result.
· The searched CASE function evaluates a set of Boolean expressions to determine the result.
Examples:
Within a SELECT statement, a simple CASE function allows for only an equality check; no other comparisons are made. The following example uses the CASE function to change the display of product line categories to make them more understandable.
USE AdventureWorks;
GO
SELECT ProductNumber, Category =
CASE ProductLine
WHEN ‘R’ THEN ‘Road’
WHEN ‘M’ THEN ‘Mountain’
WHEN ‘T’ THEN ‘Touring’
WHEN ‘S’ THEN ‘Other sale items’
ELSE ‘Not for sale’
END,
Name
FROM Production.Product
ORDER BY ProductNumber;
GO
Within a SELECT statement, the searched CASE function allows for values to be replaced in the result set based on comparison values. The following example displays the list price as a text comment based on the price range for a product.
USE AdventureWorks;
GO
SELECT ProductNumber, Name, ‘Price Range’ =
CASE
WHEN ListPrice = 0 THEN ‘Mfg item – not for resale’
WHEN ListPrice < 50 THEN ‘Under $50’
WHEN ListPrice >= 50 and ListPrice < 250 THEN ‘Under $250’
WHEN ListPrice >= 250 and ListPrice < 1000 THEN ‘Under $1000’
ELSE ‘Over $1000’
END
FROM Production.Product
ORDER BY ProductNumber ;
GO
Evaluates a list of conditions and returns one of multiple possible result expressions.
CASE has two formats:
· The simple CASE function compares an expression to a set of simple expressions to determine the result.
· The searched CASE function evaluates a set of Boolean expressions to determine the result.
Examples:
Within a SELECT statement, a simple CASE function allows for only an equality check; no other comparisons are made. The following example uses the CASE function to change the display of product line categories to make them more understandable.
USE AdventureWorks;
GO
SELECT ProductNumber, Category =
CASE ProductLine
WHEN ‘R’ THEN ‘Road’
WHEN ‘M’ THEN ‘Mountain’
WHEN ‘T’ THEN ‘Touring’
WHEN ‘S’ THEN ‘Other sale items’
ELSE ‘Not for sale’
END,
Name
FROM Production.Product
ORDER BY ProductNumber;
GO
Within a SELECT statement, the searched CASE function allows for values to be replaced in the result set based on comparison values. The following example displays the list price as a text comment based on the price range for a product.
USE AdventureWorks;
GO
SELECT ProductNumber, Name, ‘Price Range’ =
CASE
WHEN ListPrice = 0 THEN ‘Mfg item – not for resale’
WHEN ListPrice < 50 THEN ‘Under $50’
WHEN ListPrice >= 50 and ListPrice < 250 THEN ‘Under $250’
WHEN ListPrice >= 250 and ListPrice < 1000 THEN ‘Under $1000’
ELSE ‘Over $1000’
END
FROM Production.Product
ORDER BY ProductNumber ;
GO
Visit: https://zonixsoft.com (our official Website)
Web Application Performance (Paging in Sql Server)
As a Web developer, you know by now that using the default paging capabilities of ASP.NET Webcontrols like DataGrid and GridView cause the Performance issue when we have thousands and thousands of records in our database; because with every roundtrip to the data-server, you get ALL the records ALL of the time. This is fine perhaps for very small databases. But in web application, you always have to count on its Performance.
One alternate approach to this scenario is to serve “On Demand” records. That is; for example; retrieving 1-100 records, then 101-200 records result set based on some event rather than retrieving all the records all of the time. I am talking about “Paging in Sql Server”.
My-SQL
The simplest solution is provided by My-SQL using LIMIT keyword. Check out the below Query:
Select * From Product Limits 15,5
It will retrieve results from 16 to 20 records. In above Query, 15 if the offset from where you want the result set records and 5 is the number of records you want to retrieve.
Sql Server 2005
Unfortunately, SQL Server does not have an equivalent to “Limit” keyword. Its nearest is TOP N, which returns the first N rows. Still there are two alternative than we can use for “Paging”.
Using ROW_NUMBER()
Sql 2005 includes the ROW_NUMBER() function, which adds an integer field to each record. In other words, it adds the record’s position within the result set as an additional field so that the first record has a 1, the second a 2, etc.
To ensure the numbering is consistent, however, SQL Server needs to know how to sort the data. Because of this, ROW_NUMBER() must immediately be followed by the OVER() function. OVER() has one required parameter, which is an ORDER BY clause. The basic syntax is:
SELECT ROW_NUMBER() OVER(ORDER BY [Name]) as RowNum,
[Name], [SKU], [ManufacturerPartNumber]
FROM Product
The Product data in result set will be appeared sorted by Name, and it has an extra column indicating each record’s position within the results.
If we want to limit the results displayed to a certain range, we need to nest this SELECT inside another one. To limit our results to records 5 to 10, we can use the following query:
SELECT * FROM
(SELECT ROW_NUMBER() OVER(ORDER BY [Name]) AS RowNum,
[Name], [SKU], [ManufacturerPartNumber] FROM Product) AS Product1
WHERE RowNum >= 5 AND RowNum <= 10
With Nested Queries
There is one another solution that consists only of 1 sql-statement, and so is efficient and quick, even with large databases. The good news is : it will work ! Always ! it’s looking rather a bit complicated; but its not!
Let us see the Example directly to limit our results to records 6 through 10,
SELECT P.[Name], P.[SKU], P.[ManufacturerPartNumber] FROM (
SELECT TOP 5 [ProductID],[Name] FROM (
SELECT TOP 10 [ProductID],[Name]
FROM Product
ORDER BY Product.[Name] ASC) as Product1
ORDER by Product1.[Name] DESC) as Product2
INNER JOIN Product P ON Product2.ProductID = P.ProductID
ORDER by Product2.[Name] ASC
Where 5 is the number of rows you want to retrieve and 10 is the offset.
The innermost Sql Statement will fetch 10 records (1 to 10) Order by Product Name in Ascending Order.
This means that if you have a table with 10,000 records, and you want to have the last 10 records, the innermost SELECT statement will indeed retrieve 10,000 records. This of course has it’s ramifications on performance, but since it’s only the primary key fields (which are indexed) and optionally some sort fields, the impact will be minimal.
The middle Sql Statement will fetch 5 records (10 to 6) Order by Product Name in Descending Order. And Finally the Outer Sql Stateement is simply select statement that will fetch the result in Ascending Order (6 to 10). In Innermost and Midddle Sql Statement only primary key fields or the fields which are indexed are used.
After looking at this in Query Analyzer, it appears that the extra nesting does not add very much to the load. The SQL statement plays with the sorting orders to limit the records, thus resulting in paged recordsets. This means that there should be at least ONE field to be sorted. If there isn’t any, sort on the primary key field(s) !
Visit: https://zonixsoft.com (our official Website)
Web Application Performance (Paging in Sql Server)
As a Web developer, you know by now that using the default paging capabilities of ASP.NET Webcontrols like DataGrid and GridView cause the Performance issue when we have thousands and thousands of records in our database; because with every roundtrip to the data-server, you get ALL the records ALL of the time. This is fine perhaps for very small databases. But in web application, you always have to count on its Performance.
One alternate approach to this scenario is to serve “On Demand” records. That is; for example; retrieving 1-100 records, then 101-200 records result set based on some event rather than retrieving all the records all of the time. I am talking about “Paging in Sql Server”.
My-SQL
The simplest solution is provided by My-SQL using LIMIT keyword. Check out the below Query:
Select * From Product Limits 15,5
It will retrieve results from 16 to 20 records. In above Query, 15 if the offset from where you want the result set records and 5 is the number of records you want to retrieve.
Sql Server 2005
Unfortunately, SQL Server does not have an equivalent to “Limit” keyword. Its nearest is TOP N, which returns the first N rows. Still there are two alternative than we can use for “Paging”.
Using ROW_NUMBER()
Sql 2005 includes the ROW_NUMBER() function, which adds an integer field to each record. In other words, it adds the record’s position within the result set as an additional field so that the first record has a 1, the second a 2, etc.
To ensure the numbering is consistent, however, SQL Server needs to know how to sort the data. Because of this, ROW_NUMBER() must immediately be followed by the OVER() function. OVER() has one required parameter, which is an ORDER BY clause. The basic syntax is:
SELECT ROW_NUMBER() OVER(ORDER BY [Name]) as RowNum,
[Name], [SKU], [ManufacturerPartNumber]
FROM Product
The Product data in result set will be appeared sorted by Name, and it has an extra column indicating each record’s position within the results.
If we want to limit the results displayed to a certain range, we need to nest this SELECT inside another one. To limit our results to records 5 to 10, we can use the following query:
SELECT * FROM
(SELECT ROW_NUMBER() OVER(ORDER BY [Name]) AS RowNum,
[Name], [SKU], [ManufacturerPartNumber] FROM Product) AS Product1
WHERE RowNum >= 5 AND RowNum <= 10
With Nested Queries
There is one another solution that consists only of 1 sql-statement, and so is efficient and quick, even with large databases. The good news is : it will work ! Always ! it’s looking rather a bit complicated; but its not!
Let us see the Example directly to limit our results to records 6 through 10,
SELECT P.[Name], P.[SKU], P.[ManufacturerPartNumber] FROM (
SELECT TOP 5 [ProductID],[Name] FROM (
SELECT TOP 10 [ProductID],[Name]
FROM Product
ORDER BY Product.[Name] ASC) as Product1
ORDER by Product1.[Name] DESC) as Product2
INNER JOIN Product P ON Product2.ProductID = P.ProductID
ORDER by Product2.[Name] ASC
Where 5 is the number of rows you want to retrieve and 10 is the offset.
The innermost Sql Statement will fetch 10 records (1 to 10) Order by Product Name in Ascending Order.
This means that if you have a table with 10,000 records, and you want to have the last 10 records, the innermost SELECT statement will indeed retrieve 10,000 records. This of course has it’s ramifications on performance, but since it’s only the primary key fields (which are indexed) and optionally some sort fields, the impact will be minimal.
The middle Sql Statement will fetch 5 records (10 to 6) Order by Product Name in Descending Order. And Finally the Outer Sql Stateement is simply select statement that will fetch the result in Ascending Order (6 to 10). In Innermost and Midddle Sql Statement only primary key fields or the fields which are indexed are used.
After looking at this in Query Analyzer, it appears that the extra nesting does not add very much to the load. The SQL statement plays with the sorting orders to limit the records, thus resulting in paged recordsets. This means that there should be at least ONE field to be sorted. If there isn’t any, sort on the primary key field(s) !
Visit : https://zonixsoft.com (our official website)
Error Handing in Sql Server 2005.
The release of Sql Server 2005 has provided us somany features over its predecessor. No doubt that more preference is given to the tasks performed by the administrator. But there are some new development features added to make your Sql Code more powerful and error resistance, specially; Stored Procedures.
The Feature – “TRY…CATCH”
The Most impressive functionality improvement added for developers is “Exceptional Handling” technique. There is no beneficial reason if you are not writing your code in “Try.. Catch” block.
• A TRY Block – the TRY block contains the code / script that might cause an exception
• A CATCH Block – if an exception occurs from one of the statements in the TRY block, control is branched to the CATCH block, where the exception can be handled, logged, and so on.
Checking @@ERROR – the “sql 2000” Way of Handling Errors in Stored Procedure
Just have a look at below Store Procedure example.
CREATE PROC usp_AccountTransaction
@AccountNum INT,
@Amount DECIMAL
AS
BEGIN
BEGIN TRANSACTION –beginning a transaction..
UPDATE MyChecking SET Amount = Amount – @Amount
WHERE AccountNum = @AccountNum
IF @@ERROR != 0 –check @@ERROR variable after each DML statements..
BEGIN
ROLLBACK TRANSACTION –RollBack Transaction if Error..
RETURN
END
ELSE
BEGIN
UPDATE MySavings SET Amount = Amount + @Amount
WHERE AccountNum = @AccountNum
IF @@ERROR != 0 –check @@ERROR variable after each DML statements..
BEGIN
ROLLBACK TRANSACTION –RollBack Transaction if Error..
RETURN
END
ELSE
BEGIN
COMMIT TRANSACTION –finally, Commit the transaction if Success..
RETURN
END
END
END
GO
Yes!.. This is what we used to code a Stored Procedure in Sql 2000; Check for @@ERROR after every DML (Data Manipulation) Statements and Commit / RollBack the transaction.
While working with SQL Server 2000, detecting errors could only be handled by checking a global error variable, @@ERROR. Because the @@ERROR variable value is reset after each SQL statement, this leads to rather bloated stored procedures, as the variable must be checked after each statement with code to handle any problems.
The TRY…CATCH block in SQL Server 2005 offers a much more readable syntax and one that developers are more familiar with. And yes, SQL Server 2005 still supports to @@ERROR Approach. In this article we’ll look at the new TRY…CATCH block and examine how it can be used to rollback a transaction in the face of an error. Lets move on to it!
Handling Errors With SQL Server 2005’s TRY…CATCH Blocks
In Fact, there is really nothing new to be describe and discuss on TRY…CATCH Block; as we all know with any programming languages, TRY…CATCH block executes a number of statements in the TRY block. If there are no errors in any of the statements, control proceeds to after the CATCH block. If, however, one of the statements causes an error, control branches immediately to the start of the CATCH block.
Basic Syntax is,
BEGIN TRY
Try Statement 1
Try Statement 2
…
Try Statement M
END TRY
BEGIN CATCH
Catch Statement 1
Catch Statement 2
…
Catch Statement N
END CATCH
The following system functions are available in the CATCH block and can be used to determine additional error information:
Function Description
ERROR_NUMBER() Returns the number of the error.
ERROR_SEVERITY() Returns the severity.
ERROR_STATE() Returns the error state number.
ERROR_PROCEDURE() Returns the name of the stored procedure where the error occurred.
ERROR_LINE() Returns the line number inside the routine that caused the error.
ERROR_MESSAGE() Returns the complete text of the error message.
Take a look at below example,
BEGIN TRY
SELECT GETDATE()
SELECT 1/0–Evergreen divide by zero example!
END TRY
BEGIN CATCH
SELECT ‘There was an error! ‘ + ERROR_MESSAGE()
RETURN
END CATCH;
Using TRY…CATCH to Rollback a Transaction in the Face of an Error
As you saw in earlier example, one of the downsides of the @@ERROR variable approach is that to implement Transaction; we must check this variable after each and every DML SQL statement to determine if an error occurred and, if so, to rollback the transaction. With SQL Server 2005’s TRY…CATCH block, however, these types of scripts are greatly simplified.
Lets Alter the Previous Example!
ALTER PROC usp_AccountTransaction
@AccountNum INT,
@Amount DECIMAL
AS
BEGIN
BEGIN TRY –Start the Try Block..
BEGIN TRANSACTION — Start the transaction..
UPDATE MyChecking SET Amount = Amount – @Amount
WHERE AccountNum = @AccountNum
UPDATE MySavings SET Amount = Amount + @Amount
WHERE AccountNum = @AccountNum
COMMIT TRAN — Transaction Success!
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRAN –RollBack in case of Error
— you can Raise ERROR with RAISEERROR() Statement including the details of the exception
RAISERROR(ERROR_MESSAGE(), ERROR_SEVERITY(), 1)
END CATCH
END
GO
Just look at the simplicity and line of code than previous example!
In the TRY block a transaction is started and the two UPDATE statements are performed. If both UPDATEs succeed, the COMMIT will be reached and the transaction committed. If, however, either one produces an error, control will be execute CATCH block where the transaction will be rolled back.
Also, you can “re-raises” the error (using RAISERROR) so that the error information will be passed up to your .Net application from where you are calling the Stored Procedure, in case if you want to use the error information to process further steps anyhow.
Visit : https://zonixsoft.com (our official wbsite)
Article is about:
The ability to pass “a list of values” from .Net as a parameter to a T-SQL based stored procedure.
Scenarios:
There are lots of scenarios where we need to pass a list of values to save in database. Here’s a couple of obvious ones:
· INSERT a list of values into the database in one “chunky” call (e.g. some IDs from a CheckBoxList)
· SELECT rows where IDs are IN (<list of IDs>)
Some general Approaches:
Taking the INSERT statements as an example, there are various general approaches that we adopt to achieve the desired result:
· Use dynamic / Inline SQL! But ideally say, dynamic / Inline SQL is rarely the ideal solution for obvious reasons.
· Make a stored proc call for each ID to insert. This is the most common approach we see in various projects, mainly because it is the easiest to implement. The drawback of course is if we were to insert 60 values, it would result in 60 “chatty” calls to the database.
· Pass comma separated values via a VARCHAR (or similar) parameter. This works fine but has messy “string splitting” in the stored procedure to extract the IDs and then build the SQL statement in the procedure itself. Prone to SQL injection and not the best performance.
· Pass the list as an XML parameter. This is nicer and is the point of this article.
Coming to the main Point, Using XML:
Using XML for “list passing” has a number of benefits, in particular the ability to pass lists of more “complex types” rather than just single values.
Lets take an example. Suppose we are having 2 CheckedListBox; one is list of Users and another is the list of tasks / roles that can be assigned to Users. We want to store the values in Table which is having Fields UserID and TaskID. The Stored Procedure will accept Parameter with XML Datatype as,
CREATE PROCEDURE [dbo].[usp_InsertUserTask]
@UserTaskXML XML
AS
BEGIN
INSERT INTO UserTasks (UserID,TaskID)
SELECT
UserTaskTab.UserTaskCol.value(‘UserID[1]’,’int’) AS UserID,
UserTaskTab.UserTaskCol.value(‘TaskID[1]’,’int’) AS TaskID
FROM @UserTaskXML.nodes(‘//UserTaskList/UserTaskData’) AS UserTaskTab(UserTaskCol)
END
To call this in Stored Procedure, you would have something like this:
EXEC [dbo].[usp_InsertUserTask]
@UserTaskXML = ‘<UserTaskList>
<UserTaskData>
<UserID>1</UserID>
<TaskID>100</TaskID>
</UserTaskData>
<UserTaskData>
<UserID>2</UserID>
<TaskID>200</TaskID>
</UserTaskData>
</UserTaskList>’
In your application, your C# calling code could be:
SqlConnection sqlCN = new SqlConnection();
sqlCN.ConnectionString = ConfigurationManager.AppSettings[“DBConn”].ToString();
string strQuery = “usp_InsertUserTask”;
SqlParameter[] sqlParams = new SqlParameter[1];
sqlParams[0] = new SqlParameter(“@UserTaskXML”, GetStudyDataXMLString());
SqlHelper.ExecuteNonQuery(sqlCN, CommandType.StoredProcedure, strQuery, sqlParams);
if (sqlCN.State == ConnectionState.Open)
sqlCN.Close();
sqlCN.Dispose();
which calls the method below to translate the UserID and TaskID from CheckBoxLists into an XML String:
private string GetUserTaskListXML()
{
try
{
StringBuilder XMLString = new StringBuilder();
XMLString.AppendFormat(“<UserTaskList>”);
for (int iUserCount = 0; iUserCount < UserCheckBoxList.Items.Count; iUserCount++)
{
if(UserCheckBoxList.Items[iUserCount].Selected)
{
for (int iTaskCount = 0; iTaskCount < TaskCheckBoxList.Items.Count; iTaskCount++)
{
if(TaskCheckBoxList.Items[iTaskCount].Selected)
{
XMLString.AppendFormat(“<UserTaskData>”);
XMLString.AppendFormat(“<UserID>{0}</UserID>”, UserCheckBoxList.Items[iUserCount].value);
XMLString.AppendFormat(“<TaskID>{0}</TaskID>”, UserCheckBoxList.Items[iUserCount].value);
XMLString.AppendFormat(“</UserTaskData>”);
}
}
}
}
XMLString.AppendFormat(“</UserTaskList>”);
}
catch (Exception Ex)
{
throw Ex;
}
return XMLString.ToString();
}
Here, StringBuilder is used for the xml concatenation as in this case I think it fits the bill but purists might prefer an XmlTextWriter approach. In summary, it performs very well and is adaptable for various lists of objects and more complex structures.