Sql Code Masterz

create function dbo.StripHTML( @text varchar(max) )  returns varchar(max) as begin     declare @textXML xml   declare @result varchar(max)  set @textXML = @text;  with doc(contents) as           (                    select chunks.chunk.query('.') from @textXML.nodes('/') as chunks(chunk)          )         select @result = contents.value('.', 'varchar(max)') from doc         return @result end go Now execute - select dbo.StripHTML(' Big Size Buckle Halter Neck Vests Tops ') 

SQL Server buffers the output, so an error message or a result set may not appear directly at the client. In many cases, this is not an issue, but if you are running a long-running procedure, you may want to produce diagnostic messages. To have them displayed immediately in the client, you can use the WITH NOWAIT clause to the RAISERROR statement, as in this example: PRINT 'This message does not display immediately' WAITFOR DELAY '00:00:05' RAISERROR ('But this one does', 0, 1) WITH NOWAIT WAITFOR DELAY '00:00:05' PRINT 'It''s over now' Once there is a message with NOWAIT , all that is ahead of the message in the buffer is also passed to the client. Unfortunately, there is a bug in SQL Server with NOWAIT , which affects you only if you are calling a procedure through RPC (remote procedure call), so that it this case, SQL Server buffers the messages nevertheless. RPC is the normal way to call a procedure from an a

@@trancount is a global variable which reflects the level of nested transactions. Each BEGIN TRANSACTION increases @@trancount by 1, and each COMMIT TRANSACTION decreases @@trancount by 1. Nothing is actually committed until @@trancount reaches 0. ROLLBACK TRANSACTION rolls back everything to the outermost BEGIN TRANSACTION (unless you have used the fairly exotic SAVE TRANSACTION ), and forces @@trancount to 0, regards of the previous value. When you exit a stored procedure, if @@trancount does not have the same value as it had when the procedure commenced execution, SQL Server raises error 266. This error is not raised, though, if the procedure is called from a trigger, directly or indirectly. Neither is it raised if you are running with SET IMPLICIT TRANSACTIONS ON .

@@rowcount is a global variable reports the number of affected rows in the most recently executed statement. Just like @@error you need to save it in a local variable if you want to use the value later, since @@rowcount is set after each statement. Since with SET you can only assign variable at a time, you must use SELECT if you need to save both @@error and @@rowcount into local variables: SELECT @err = @@error, @rowc = @@rowcount (For this reason, I prefer to always use SELECT for variable assignment, despite Microsoft's recommendations to use SET.) In T-SQL it is not an error if, for instance, an UPDATE statement did not affect any rows. But it can of course indicate an error in your application, as it could be an error if a SELECT returns more that one row. For these situations, you can check @@rowcount and raise an error and set a return value, if @@rowcount is not the expected value.

Here is a typical error message you can get from SQL Server when working from Query Analyzer. Server: Msg 547, Level 16, State 1, Procedure error_demo_sp, Line 2 UPDATE statement conflicted with COLUMN FOREIGN KEY constraint 'fk7_acc_cur'. The conflict occurred in database 'bos_sommar', table 'currencies', column 'curcode'. The statement has been terminated. Note: Under Tools -> Options -> Connections, I have checked Parse ODBC Message Prefixes . The error information that SQL Server passes to the client consists of several components, and the client is responsible for the final interpretation of the message. These are the components that SQL Server passes to the client. Message number – each error message has a number. You can find most of the message numbers in the table sysmessages in the master database. (There some special numbers like 0 and 50000 that do not appear there.) In this example, the message number i

Many programming languages have a fairly consistent behaviour when there is a run-time error. Common is that the execution simply terminates in case of an error, unless you have set up an exception handler that takes care the error. In other languages, some error variable is set and you have to check this variable. T-SQL is confusing, because depending on what error that occurs and in which context it occurs, SQL Server can take no less than four different actions. I first give an overview of these alternatives, followed by a more detailed discussion of which errors that cause which actions. I then discuss two special cases: trigger context and user-defined functions. The Possible Actions These are the four main possible actions SQL Server can take: Statement-termination. The current statement is aborted and rolled back. Execution continues on the next statement. Any open transaction is not rolled back. @@error is set to the number of the error. Since the statement is rolled back

All stored procedures have a return value, determined by the RETURN statement. The RETURN statement takes one optional argument, which should be a numeric value. If you say RETURN without providing a value, the return value is 0 if there is no error during execution. If an error occurs during execution of the procedure, the return value may be 0, or it may be a negative number. The same is true if there is no RETURN statement at all in the procedure: the return value may be a negative number or it may be 0. Whether these negative numbers have any meaning, is a bit difficult to tell. It used to be the case, that the return values -1 to -99 were reserved for system-generated return values, and Books Online for earlier versions of SQL Server specified meanings for values -1 to -14. However, Books Online for SQL  2000 is silent on any such reservations, and does not explain what -1 to -14 would mean. With some occasional exception, the system stored

OPERATOR EXAMPLE DESCRIPTION   nut Searches for inflectional forms of the word nut crank arm crank AND arm Searches for documents containing inflectional forms of the words crank AND arm crank and ann. The keyword AND is optional. tire OR air Searches for documents containing inflectional forms of the words tire or air, “reflector bracket” Performs a phrase search for the phrase "reflector bracket". hardware -bracket Searches for documents containing inflectional forms of the word hardware but not the word bracket. +clamp Searches for the word darn') without generating inflectional forms. ~seat Searches for thesaurus forms of the word seat Assemb* Searches for words that begin with the prefix assemb . <washer nut> Searches for documents that contain the words washer in close proximity to the word nut

Names that sound alike do not always have the same soundex code. For example, Lee (L000) and Leigh (L200) are pronounced identically, but have different soundex codes because the silent g in Leigh is given a code. Names that sound alike but start with a different first letter will always have a different soundex code. Thus, names such as Carr (C600) and Karr (K600) have different soundex codes even though they sound alike. Name that sound alike but have different first letters should have each name calculated and searched for separately. Since the soundex system is based on English pronunciationn, some European names may not soundex correctly. For example, some French surnames with silent last letters will not code according to pronunciation. An example is the French name such as Beaux - where the x is silent. While Beau (B000) is pronounced identically to Beaux (B200), they will have different soundex codes. This could be true of any surname that does no

Indexes help us retrieve data from tables quicker. Let's use an example to illustrate this point: Say we are interested in reading about how to grow peppers in a gardening book. Instead of reading the book from the beginning until we find a section on peppers, it is much quicker for us to go to the index section at the end of the book, locate which pages contain information on peppers, and then go to these pages directly. Going to the index first saves us time and is by far a more efficient method for locating the information we need. The same principle applies for retrieving data from a database table. Without an index, the database system reads through the entire table (this process is called a 'table scan') to locate the desired information. With the proper index in place, the database system can then first go through the index to find out where to retrieve the data, and then go to these locations directly to get the needed data. This is much faster.

The SOUNDEX code is a substitution code using the following rules: The first letter of the surname is always retained. The rest of the surname is compressed to a three digit code using the following coding scheme: A E I O U Y H W not coded B F P V coded as 1 C G J K Q S X Z coded as 2 D T coded as 3 L coded as 4 M N coded as 5 R coded as 6 Consonants after the initial letter are coded in the order they occur: HOLMES = H-452 ADOMOMI = A-355 The code always uses initial letter plus three digits. Further consonants in long names are ignored: VONDERLEHR = V-536 Zeros are used to pad out shorter names: BALL = B-400 SHAW = S-000 Double consonants are treated as one letter: BALL = B-400 As are adjacent consonants from the same code group: JACKSON = J-250 A consonant following an initial letter from the same code group is ignored: SCANLON = S-545 Abbreviated prefixes should be spelt out in full: ST JOHN = SAINTJOHN = S-532 Apostrophes and hyphens are

Some times  we need that we want to transfer the data from a XML file to the sql server.I know that there are several ways to do that.But this methods is quite simple and sort and obviously very fast.   Only we need to read xml file into a dataset then simply write this data to SQL server by SQLBulkCopy Command.And you are done.             DataSet ds = new DataSet();         ds.ReadXml(Server.MapPath("HotelList.xml"));                DataTable dt = ds.Tables[0];        // dt.Columns.Remove("HotelID");        // dt.Columns.Remove("HotelFileName");        // dt.Columns.Remove("HotelName");        // dt.Columns.Remove("CityID");        // dt.Columns.Remove("CityFileName");        // dt.Columns.Remove("CityName");        // dt.Columns.Remove("StateID");        // dt.Columns.Remove("StateFileName");        // dt.Columns.Remove("StateName");        //dt.

Convert the columns into a comma separated string in T-Sql create  FUNCTION toCSV (@id int) RETURNS varchar(100) AS BEGIN DECLARE @List varchar(100) SELECT  @List = COALESCE(@List + ', ', '') +    CAST(name AS varchar(10)) FROM location WHERE ID in (163,164,165,166,167,168,169,170) select @list END; go

Cursors in SQL procedures In SQL procedures, a cursor make it possible to define a result set (a set of data rows) and perform complex logic on a row by row basis. By using the same mechanics, an SQL procedure can also define a result set and return it directly to the caller of the SQL procedure or to a client application. A cursor can be viewed as a pointer to one row in a set of rows. The cursor can only reference one row at a time, but can move to other rows of the result set as needed. To use cursors in SQL procedures, you need to do the following: Declare a cursor that defines a result set. Open the cursor to establish the result set. Fetch the data into local variables as needed from the cursor, one row at a time. Close the cursor when done To work with cursors you must use the following SQL statements: DECLARE CURSOR OPEN FETCH CLOSE The following example demonstrates the basic use of a read-only cursor within an SQL procedure: CREATE PROCEDURE sum_salaries(OUT su

SQL Views A VIEW is a virtual table, through which a selective portion of the data from one or more tables can be seen. Views do not contain data of their own. They are used to restrict access to the database or to hide data complexity. A view is stored as a SELECT statement in the database. DML operations on a view like INSERT, UPDATE, DELETE affects the data in the original table upon which the view is based. The Syntax to create a sql view is CREATE VIEW view_name AS SELECT column_list FROM table_name [WHERE condition]; view_name is the name of the VIEW. The SELECT statement is used to define the columns and rows that you want to display in the view. For Example: to create a view on the product table the sql query would be like CREATE VIEW view_product AS SELECT product_id, product_name FROM product;  Source : -http://beginner-sql-tutorial.com/sql-views.htm

SQL Index Index in sql is created on existing tables to retrieve the rows quickly. When there are thousands of records in a table, retrieving information will take a long time. Therefore indexes are created on columns which are accessed frequently, so that the information can be retrieved quickly. Indexes can be created on a single column or a group of columns. When a index is created, it first sorts the data and then it assigns a ROWID for each row. Syntax to create Index: CREATE INDEX index_name ON table_name (column_name1,column_name2...); Syntax to create SQL unique Index: CREATE UNIQUE INDEX index_name ON table_name (column_name1,column_name2...); index_name is the name of the INDEX. table_name is the name of the table to which the indexed column belongs. column_name1, column_name2.. is the list of columns which make up the INDEX. In Oracle there are two types of SQL index namely, implicit and explicit. Implicit Indexes: They are created when a column i

SQL Alias SQL Aliases are defined for columns and tables. Basically aliases is created to make the column selected more readable. For Example: To select the first name of all the students, the query would be like: Aliases for columns: SELECT first_name AS Name FROM student_details; or SELECT first_name Name FROM student_details; In the above query, the column first_name is given a alias as 'name'. So when the result is displayed the column name appears as 'Name' instead of 'first_name'. Output: Name ------------- Rahul Sharma Anjali Bhagwat Stephen Fleming Shekar Gowda Priya Chandra Aliases for tables: SELECT s.first_name FROM student_details s; In the above query, alias 's' is defined for the table student_details and the column first_name is selected from the table. Aliases is more useful when There are more than one tables involved in a query, Functions are used in the query, The column names are big or not readabl

DCL commands are used to enforce database security in a multiple user database environment. Two types of DCL commands are GRANT and REVOTE. Only Database Administrator's or owner's of the database object can provide/remove privileges on a databse object. SQL GRANT Command SQL GRANT is a command used to provide access or privileges on the database objects to the users. The Syntax for the GRANT command is: GRANT privilege_name ON object_name TO {user_name |PUBLIC |role_name} [WITH GRANT OPTION]; privilege_name is the access right or privilege granted to the user. Some of the access rights are ALL, EXECUTE, and SELECT. object_name is the name of an database object like TABLE, VIEW, STORED PROC and SEQUENCE. user_name is the name of the user to whom an access right is being granted. user_name is the name of the user to whom an access right is being granted. PUBLIC is used to grant access rights to all users. ROLES are a set of privileges grouped together.

SQL Integrity Constraints Integrity Constraints are used to apply business rules for the database tables. The constraints available in SQL are Foreign Key, Not Null, Unique, Check. Constraints can be defined in two ways 1) The constraints can be specified immediately after the column definition. This is called column-level definition. 2) The constraints can be specified after all the columns are defined. This is called table-level definition. 1) SQL Primary key: This constraint defines a column or combination of columns which uniquely identifies each row in the table. Syntax to define a Primary key at column level: column name datatype [CONSTRAINT constraint_name] PRIMARY KEY Syntax to define a Primary key at table level: [CONSTRAINT constraint_name] PRIMARY KEY (column_name1,column_name2,..) column_name1, column_name2 are the names of the columns which define the primary Key. The syntax within the bracket i.e. [CONSTRAINT constraint_name] is optional. For Example:

SQL Tuning/SQL Optimization Techniques: 1) The sql query becomes faster if you use the actual columns names in SELECT statement instead of than '*'. For Example: Write the query as SELECT id, first_name, last_name, age, subject FROM student_details; Instead of: SELECT * FROM student_details; 2) HAVING clause is used to filter the rows after all the rows are selected. It is just like a filter. Do not use HAVING clause for any other purposes. For Example: Write the query as SELECT subject, count(subject) FROM student_details WHERE subject != 'Science' AND subject != 'Maths' GROUP BY subject; Instead of: SELECT subject, count(subject) FROM student_details GROUP BY subject HAVING subject!= 'Vancouver' AND subject!= 'Toronto'; 3) Sometimes you may have more than one subqueries in your main query. Try to minimize the number of subquery block in your query. For Example: Write the query as SELECT name FROM employee

Convert the columns into a comma separated string in T-Sql Input :- test1 test2 test3 test4 Output:-             test1,test2,test3,test4 Query:- SELECT STUFF((SELECT ', ' +convert(nvarchar, name) FROM (SELECT name FROM Locations_Alternative where LID=234) AS T FOR XML PATH('')),1,1,'') AS [Name]

Get all nested subcategories or data from a table in sql server Consider the situation- ID      Name                 PID 1       a                         0         2       -a.a                     1 3       --a.a.a                2 4       --a.a.b               2 5       ---a.a.b.a           4 6       ---a.a.b.b           4 7       ----a.a.b.b.a       6 8       -a.b                   1 9       -a.c                   1 10      --a.c.a              9 11      --a.c.b              9 12      b                      0 13      -b.a                 12 14      -b.b                 12 15      --b.b.a             14 and you want to select all the subcategories to n level under any category i.e. if you want that all subcategories under "b" will be selected - Result- 12      b                       0 13      -b.a                  12 14      -b.b                  12 15      --b.b.a              14 or All subcategories under category "a" - result- 1