• Use views and stored procedures instead of heavy-duty queries. This can reduce network traffic, because your client will send to server only stored procedure or view name (perhaps with some parameters) instead of large heavy-duty queries text. This can be used to facilitate permission management also, because you can restrict user access to table columns they should not see.


• Use table variables instead of temporary tables.Table variables require less locking and logging resources than temporary tables, so table variables should be used whenever possible. The table variables are available from SQL Server 2000 only.


• Try to use UNION ALL statement instead of UNION, whenever possible. The UNION ALL statement is much faster than UNION, because UNION ALL statement does not look for duplicate rows, and UNION statement does look for duplicate rows, whether or not they exist.


• Try to avoid using the DISTINCT clause, whenever possible. Because using the DISTINCT clause will result in some performance degradation, you should use this clause only when it is necessary.


• Try to avoid using SQL Server cursors, whenever possible. SQL Server cursors can result in some performance degradation in comparison with select statements. Try to use correlated sub-query or derived tables, if you need to perform row-by-row operations.


• If you need to return the total table's row count, you can use alternative way instead of SELECT COUNT(*) statement. Because SELECT COUNT(*) statement make a full table scan to return the total table's row count, it can take very many time for the large table. There is another way to determine the total row count in a table. You can use sysindexes system table, in this case. There is ROWS column in the sysindexes table. This column contains the total row count for each table in your database. So, you can use the following select statement instead of SELECT COUNT(*): SELECT rows FROM sysindexes WHERE id = OBJECT_ID('table_name') AND indid < 2 So, you can improve the speed of such queries in several times


• Include SET NOCOUNT ON statement into your stored procedures to stop the message indicating the number of rows affected by a T-SQL statement. This can reduce network traffic, because your client will not receive the message indicating the number of rows affected by a T-SQL statement.


• Use the select statements with TOP keyword or the SET ROWCOUNT statement, if you need to return only the first n rows. This can improve performance of your queries, because the smaller result set will be returned. This can also reduce the traffic between the server and the clients


• Try to restrict the queries result set by returning only the particular columns from the table, not all table's columns. This can results in good performance benefits, because SQL Server will return to client only particular columns, not all table's columns. This can reduce network traffic and boost the overall performance of the query.

         1. Indexes
         2. avoid more number of triggers on the table
         3. unnecessary complicated joins
         4. correct use of Group by clause with the select list
         5. In worst cases De-normalization

Index Optimization tips

• Every index increases the time in takes to perform INSERTS, UPDATES and DELETES, so the number of indexes should not be very much. Try to use maximum 4-5 indexes on one table, not more. If you have read-only table, then the number of indexes may be increased.


• Keep your indexes as narrow as possible. This reduces the size of the index and reduces the number of reads required to read the index.


• Try to create indexes on columns that have integer values rather than character values.
• If you create a composite (multi-column) index, the order of the columns in the key are very important. Try to order the columns in the key as to enhance selectivity, with the most selective columns to the leftmost of the key.


• If you want to join several tables, try to create surrogate integer keys for this purpose and create indexes on their columns.


• Create surrogate integer primary key (identity for example) if your table will not have many insert operations.


• Clustered indexes are more preferable than nonclustered, if you need to select by a range of values or you need to sort results set with GROUP BY or ORDER BY.


• If your application will be performing the same query over and over on the same table, consider creating a covering index on the table.


• You can use the SQL Server Profiler Create Trace Wizard with "Identify Scans of Large Tables" trace to determine which tables in your database may need indexes. This trace will show which tables are being scanned by queries instead of using an index.


• You can use sp_MSforeachtable undocumented stored procedure to rebuild all indexes in your database. Try to schedule it to execute during CPU idle time and slow production periods. sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?')"

Select the word and hit CTRL+SHIFT+U the SSMS immediately changes the selected word to UPPER CASE. Similar to convert cases to lower case hit CTRL+SHIFT+L.

Displaying Current Date and Time

SELECT CURRENT_TIMESTAMP
GO
SELECT {fn NOW()}
GO
SELECT GETDATE()
GO

CAST and Convert

Syntax for CAST:
CAST ( expression AS data_type [ ( length ) ])

Syntax for CONVERT:
CONVERT ( data_type [ ( length ) ] , expression/ColumnName [ , style ] )

Shrinking Truncate Log File

USE DatabaseName
GO
DBCC SHRINKFILE(, 1)
BACKUP LOG WITH TRUNCATE_ONLY
DBCC SHRINKFILE(, 1)
GO

Restore Database Backup using T-Sql

Step 1: Retrive the Logical file name of the database from backup.

RESTORE FILELISTONLY
FROM DISK = 'D:BackUpYourBaackUpFile.bak'
GO

Step 2: Use the values in the LogicalName Column in following Step.

----Make Database to single user Mode

ALTER DATABASE YourDB
SET SINGLE_USER WITH
ROLLBACK IMMEDIATE

----Restore Database

RESTORE DATABASE YourDB
FROM DISK = 'D:BackUpYourBaackUpFile.bak'
WITH MOVE 'YourMDFLogicalName' TO 'D:DataYourMDFFile.mdf',
MOVE 'YourLDFLogicalName' TO 'D:DataYourLDFFile.ldf'

/*If there is no error in statement before database will be in multiuser mode.

If error occurs please execute following command it will convert database in multi user.*/

ALTER DATABASE YourDB SET MULTI_USER
GO

DECLARE @UserRoleName Table (UserID varchar(50), UserRole varchar(100))
INSERT INTO @UserRoleName
SELECT 'Vasanth','DotNet Developer' UNION ALL
SELECT 'Vasanth','Sql Developer' UNION ALL
SELECT 'Jayaseelan','DotNet Developer' UNION ALL
SELECT 'Jayaseelan','Appl Deployer' UNION ALL
SELECT 'Vinoth','DotNet Developer'

SELECT * FROM @UserRoleName



SELECT UserID, CAST(UserRole AS VARCHAR(100)) AS RoleName FROM @UserRoleName FOR XML PATH('')



SELECT UserID, STUFF((SELECT CAST(t2.UserRole AS VARCHAR(100)) AS RoleName FROM @UserRoleName t2 WHERE t1.Userid = t2.userID
FOR XML PATH('')),1,0,'') AS RoleDescription
FROM @UserRoleName t1 GROUP BY UserID



SELECT UserID, STUFF((SELECT ', ' + CAST(t2.UserRole as varchar(100)) FROM @UserRoleName t2 WHERE t1.Userid = t2.userID
FOR XML PATH('')),1,1,'') Role
FROM @UserRoleName t1 GROUP BY UserID

STUFF - Delete a specified length of characters and insert another set of characters at a specified starting point. eg.

SELECT STUFF('VASANTH', 3, 2, 'XYZ')

3 - Starting Position from where the Replace Starts
1 - upto Which position it needs to be replaced from '3'
'XYZ' - Replacing characters in that position

Result is : VAXYZNTH

REPLACE - Replace all occurrences of the second given string expression in the first string expression with a third expression. eg.

SELECT REPLACE('VASANTH', 'A', 'KZT')

Result is : VKZTSKZTNTH

Declare @Sql nvarchar(max)
Declare @cnt varchar(10)
Declare @TableName as varchar(50)
SET @TableName = 'temp'

SET @Sql = 'Select @Cnt = count(*) From ' + @TableName
--PRINT @Sql
EXEC sp_executeSql @Sql, N'@Cnt varchar(10) output', @Cnt output
Select @Cnt

Temp Tables vs. Table Variables

1. SQL Server does not place locks on table variables when the table variables are used.
2. Temp tables allow for multiple indexes to be created
3. Table variables allow a single index the Primary Key to be created when the table variable is declared only.
4. Temp tables can be created locally (#TableName) or globally (##TableName)
5. Table variables are destroyed as the batch is completed.
6. Temp tables can be used throughout multiple batches.
7. Temp tables can be used to hold the output of a stored procedure (temp tables will get this functionality in SQL Server 2008).

Table variables and Temp Tables vs. CTEs

1. CTEs are used after the command which creates them.
2. CTEs can be recursive within a single command (be careful because they can cause an infinite loop).
3. Table variables and Temp Tables can be used throughout the batch.
4. The command before the CTE must end with a semi-colon (;).
5. As Temp tables and table variables are tables you can insert, update and delete the data within the table.
6. CTEs can not have any indexes created on them, source tables much have indexes created on them.

DECLARE @people TABLE
(
id INT,
name VARCHAR(32)
)

A table variable is created in memory, and so performs slightly better than #temp tables (also because there is even less locking and logging in a table variable). A table variable might still perform I/O to tempdb (which is where the performance issues of #temp tables make themselves apparent), though the documentation is not very explicit about this.

Table variables are automatically cleared when the procedure or function goes out of scope, so you don't have to remember to drop or clear the data (which can be a good thing or a bad thing; remember "release early"?). The tempdb transaction log is less impacted than with #temp tables; table variable log activity is truncated immediately, while #temp table log activity persists until the log hits a checkpoint, is manually truncated, or when the server restarts.

Table variables are the only way you can use DML statements (INSERT, UPDATE, DELETE) on temporary data within a user-defined function. You can create a table variable within a UDF, and modify the data using one of the above statements.

Table variables can lead to fewer stored procedure recompilations than temporary tables and — since they cannot be rolled back — do not bother with the transaction log.

Limitation of Table Variable :

1. Table variables are only allowed in SQL Server 2000+, with compatibility level set to 80 or higher.

2. You cannot use a table variable in either of the following situations:

INSERT @table EXEC sp_someProcedure

SELECT * INTO @table FROM someTable

3. You cannot truncate a table variable.

4. Table variables cannot be altered after they have been declared.

5. You cannot explicitly add an index to a table variable, however you can create a system index through a PRIMARY KEY CONSTRAINT, and you can add as many indexes via UNIQUE CONSTRAINTs.

6. Table variables are not visible to the calling procedure in the case of nested procs.

7. Table variables do not maintain statistics like temporary tables.

Do I have to use table variables instead of temporary tables?

The answer depends on these three factors:

1. The number of rows that are inserted to the table.
2. The number of recompilations the query is saved from.
3. The type of queries and their dependency on indexes and statistics for performance.

In some situations, breaking a stored procedure with temporary tables into smaller stored procedures so that recompilation takes place on smaller units is helpful.

CREATE TABLE ##people
(
id INT,
name VARCHAR(32)
)

Global temporary tables operate much like local temporary tables; they are created in tempdb and cause less locking and logging than permanent tables. However, they are visible to all sessions, until the creating session goes out of scope (and the global ##temp table is no longer being referenced by other sessions). If two different sessions try the above code, if the first is still active, the second will receive the following:

Server: Msg 2714, Level 16, State 6, Line 1
There is already an object named '##people' in the database.

I have yet to see a valid justification for the use of a global ##temp table. If the data needs to persist to multiple users, then it makes much more sense, at least to me, to use a permanent table. You can make a global ##temp table slightly more permanent by creating it in an autostart procedure, but I still fail to see how this is advantageous over a permanent table. With a permanent table, you can deny permissions; you cannot deny users from a global ##temp table.

CREATE TABLE #people
(
id INT,
name VARCHAR(32)
)

A temporary table is created and populated on disk, in the system database tempdb — with a session-specific identifier packed onto the name, to differentiate between similarly-named #temp tables created from other sessions. The data in this #temp table (in fact, the table itself) is visible only to the current scope (usually a stored procedure, or a set of nested stored procedures). The table gets cleared up automatically when the current procedure goes out of scope, but you should manually clean up the data when you're done with it:

DROP TABLE #people

This will be better on resources ("release early") than if you let the system clean up *after* the current session finishes the rest of its work and goes out of scope.

A common use of #temp tables is to summarize/compact/reorganize data coming from another stored procedure. So, take this example, which pares down the results of the system procedure sp_who2 into only the SPID, Status, and HostName of *active* processes that are *not* part of the regular operation of the system:

CREATE TABLE #sp_who3
(
SPID INT,
Status VARCHAR(32) NULL,
Login SYSNAME NULL,
HostName SYSNAME NULL,
BlkBy SYSNAME NULL,
DBName SYSNAME NULL,
Command VARCHAR(32) NULL,
CPUTime INT NULL,
DiskIO INT NULL,
LastBatch VARCHAR(14) NULL,
ProgramName VARCHAR(32) NULL,
SPID2 INT
)

INSERT #sp_who3 EXEC sp_who2 'active'

SELECT SPID, Status, HostName FROM #sp_who3
WHERE spid > 15

DROP TABLE #sp_who3

One of the main benefits of using a #temp table, as opposed to a permanent table, is the reduction in the amount of locking required (since the current user is the only user accessing the table), and also there is much less logging involved. (You could also increase this performance by placing tempdb on a separate drive.)

One minor problem with #temp tables is that, because of the session-specific identifier that is tacked onto the name, the name you give it is limited to 116 characters, including the # sign (while other table types are limited to 128). If you try, you will see this:

Server: Msg 193, Level 15, State 1, Line 1
The object or column name starting with '#' is too long. The maximum length is 116 characters.

Hopefully this won't be a limitation in your environment, because I can't imagine a table name that long being useful or manageable.

Another potential problem with #temp tables is that, if you enter a transaction and use a #temp table, and then cancel without ever issuing a ROLLBACK or COMMIT, you could be causing unnecessary locks in tempdb.

Checking for Duplicates :

On any version of SQL Server, you can identify duplicates using a simple query, with GROUP BY and HAVING, as follows:

DECLARE @table TABLE (data VARCHAR(20))
INSERT INTO @table VALUES ('not duplicate row')
INSERT INTO @table VALUES ('duplicate row')
INSERT INTO @table VALUES ('duplicate row')

SELECT data, COUNT(data) nr
FROM @table
GROUP BY data
HAVING COUNT(data) > 1

The result indicates that there are two occurrences of the row containing the “duplicate row” text:

data nr
-------------------- ----
duplicate row 2

Removing Duplicate Rows in SQL Server :

The following sections present a variety of techniques for removing duplicates from SQL Server database tables, depending on the nature of the table design.

Tables with no primary key :

When you have duplicates in a table that has no primary key defined, and you are using an older version of SQL Server, such as SQL Server 2000, you do not have an easy way to identify a single row. Therefore, you cannot simply delete this row by specifying a WHERE clause in a DELETE statement.

You can, however, use the SET ROWCOUNT 1 command, which will restrict the subsequent DELETE statement to removing only one row. For example:

DECLARE @table TABLE (data VARCHAR(20))
INSERT INTO @table VALUES ('not duplicate row')
INSERT INTO @table VALUES ('duplicate row')
INSERT INTO @table VALUES ('duplicate row')

SET ROWCOUNT 1

DELETE FROM @table WHERE data = 'duplicate row'

SET ROWCOUNT 0

In the above example, only one row is deleted. Consequently, there will be one remaining row with the content “duplicate row”. If you have more than one duplicate of a particular row, you would simply adjust the ROWCOUNT accordingly. Note that after the delete, you should reset the ROWCOUNT to 0 so that subsequent queries are not affected.

To remove all duplicates in a single pass, the following code will work, but is likely to be horrendously slow if there are a large number of duplicates and table rows:

DECLARE @table TABLE (data VARCHAR(20))

INSERT INTO @table VALUES ('not duplicate row')
INSERT INTO @table VALUES ('duplicate row')
INSERT INTO @table VALUES ('duplicate row')

SET NOCOUNT ON

SET ROWCOUNT 1

WHILE 1 = 1
BEGIN
DELETE FROM @table
WHERE data IN (SELECT data
FROM @table
GROUP BY data
HAVING COUNT(*) > 1)

IF @@Rowcount = 0
BREAK ;
END

SET ROWCOUNT 0

When cleaning up a table that has a large number of duplicate rows, a better approach is to select just a distinct list of the duplicates, delete all occurrences of those duplicate entries from the original and then insert the list into the original table.

DECLARE @table TABLE(data VARCHAR(20))

INSERT INTO @table VALUES ('not duplicate row')
INSERT INTO @table VALUES ('duplicate row')
INSERT INTO @table VALUES ('duplicate row')
INSERT INTO @table VALUES ('second duplicate row')
INSERT INTO @table VALUES ('second duplicate row')

SELECT data
INTO #duplicates
FROM @table
GROUP BY data
HAVING COUNT(*) > 1

-- delete all rows that are duplicated

DELETE FROM @table
FROM @table o INNER JOIN #duplicates d
ON d.data = o.data

-- insert one row for every duplicate set

INSERT INTO @table(data)
SELECT data
FROM #duplicates

As a variation of this technique, you could select all the data, without duplicates, into a new table, delete the old table, and then rename the new table to match the name of the original table:

CREATE TABLE duplicateTable3(data VARCHAR(20))

INSERT INTO duplicateTable3 VALUES ('not duplicate row')
INSERT INTO duplicateTable3 VALUES ('duplicate row')
INSERT INTO duplicateTable3 VALUES ('duplicate row')
INSERT INTO duplicateTable3 VALUES ('second duplicate row')
INSERT INTO duplicateTable3 VALUES ('second duplicate row')

SELECT DISTINCT data
INTO tempTable
FROM duplicateTable3
GO

TRUNCATE TABLE duplicateTable3

DROP TABLE duplicateTable3

exec sp_rename 'tempTable', 'duplicateTable3'

In this solution, the SELECT DISTINCT will select all the rows from our table except for the duplicates. These rows are immediately inserted into a table named tempTable. This is a temporary table in the sense that we will use it to temporarily store the unique rows. However, it is not a true temporary table (i.e. one that lives in the temporary database), because we need the table to exist in the current database, so that it can later be renamed, using sp_Rename.

The sp_Rename command is an absolutely horrible way of renaming textual objects, such as stored procedures, because it does not update all the system tables consistently. However, it works well for non-textual schema objects, such as tables.

New Techniques for Removing Duplicate Rows in SQL Server 2005

SQL Server 2005 has introduced the row_number() function, which provides an alternative means of identifying duplicates. Rewriting the first example, for tables with no primary key, we can now assign a row number to each row in a duplicate group, with a command such as:

DECLARE @duplicateTable4 TABLE (data VARCHAR(20))

INSERT INTO @duplicateTable4 VALUES ('not duplicate row')
INSERT INTO @duplicateTable4 VALUES ('duplicate row')
INSERT INTO @duplicateTable4 VALUES ('duplicate row')
INSERT INTO @duplicateTable4 VALUES ('second duplicate row')
INSERT INTO @duplicateTable4 VALUES ('second duplicate row')

SELECT data, row_number() OVER ( PARTITION BY data ORDER BY data ) AS nr
FROM @duplicateTable4

The result will show:

data nr
-------------------- --------------------
duplicate row 1
duplicate row 2
not duplicate row 1
second duplicate row 1
second duplicate row 2

In the above example, we specify an ordering and partitioning for the row_number() function. Note that the row_number() is a ranking window function, therefore the ORDER BY and the PARTITION BY in the OVER clause are used only to determine the value for the nr column, and they do not affect the row order of the query. Also, while the above is similar to our previous GROUP BY clause, there is a big difference concerning the returned rows. With GROUP BY you must use an aggregate on the columns that are not listed after the GROUP BY. With the OVER clause there is no such restriction, and you can get access to the individual rows in the groups specified by the PARTITION BY clause. This gives us access to the individual duplicate rows, so we can get not only the number of occurrences, but also a sequence number for the individual duplicates. To filter out the duplicate rows only, we could just put the above query into a CTE or a subquery. The CTE approach is as follows:

DECLARE @duplicateTable4 TABLE (data VARCHAR(20))

INSERT INTO @duplicateTable4 VALUES ('not duplicate row')
INSERT INTO @duplicateTable4 VALUES ('duplicate row')
INSERT INTO @duplicateTable4 VALUES ('duplicate row')
INSERT INTO @duplicateTable4 VALUES ('second duplicate row')
INSERT INTO @duplicateTable4 VALUES ('second duplicate row')

WITH numbered
AS ( SELECT data, row_number() OVER ( PARTITION BY data ORDER BY data ) AS nr
FROM @duplicateTable4
)

DELETE FROM numbered
WHERE nr > 1

This solution will even work with large objects, if you stick to the new large object types introduced in SQL Server 2005: i.e. use VARCHAR(MAX) instead of TEXT, NVARCHAR(MAX) instead of NTEXT, and VARBINARY(MAX) instead of IMAGE. These new types are comparable to the deprecated TEXT, NTEXT and IMAGE, and they have the advantage that you will be able to use them with both DISTINCT and row_number().

Create procedure usp_proc3

as

Begin

Create table ##Temp (ID int,[Name] char(30) )

Insert into ##Temp values (1,'Venkat')

End

Creating another SP Which calls the above created Temporary table(#GlobalTempTable)

Create procedure usp_proc4

as

Begin

Select * from ##Temp

End

Now, i am trying to executing the first Stored Procedure

exec usp_proc3

Global Temporary table is created and now i am trying to execute the Second SP,

exec usp_proc4

It will display the result as "1" "Venkat"

Limitations and Alternative for Temporary Table

Temporary tables created in TempDB will create additional overheads because of huge resource utilization. The best alternative for temporary table is creating table variables, which reside only in memory instead of creating in TempDB and it will use less resources when compare to Temporary tables.

Tables which can be declared and functions similar to a varaible are referred as Table Variables.

Table variables were created in memory inturn enable faster database access. Table variables are considered as good alternative for Temporary tables. Since, temporary tables will create tables in TempDB, there is additional resource utilization in accessing TempDB and Creating tables in TempDB.

Terms and Conditions in using variables:

Unlike temporary tables, table variables adhere to its scope. Temporary tables can be created first and we can execute insert statement or dml operations seperately inside the scope. For example, #a is executed first and afterwards we can execute dml operations subsequently since its stored in database we can use it. Where as, table varaibles resides in memory and doesn't have physical structure, the declaration statement and dml statements should be executed simultaneously else if we try to execute declare statement first(executed) and afterward if we try to access that table an error will be thrown indicating table is not available.

Declaring a table variable:

DECLARE tablevariable-name TABLE ( COLUMN1 TYPE, COLUMN2 TYPE, COLUMN3 TYPE, .....)

Example : declare @TableVariable table(id int,name varchar(20))

All the DML Operations are similar to the Table.

insert into @TableVariable values(1,'Microsoft')

select * from @TableVariable

select * from Table inner join @TableVariable on Table.id= @TableVariable.id

Created a Table Called Temp :

CREATE TABLE TEMP (ID Int, Name Varchar(50))

Insert some values into the Table Temp :

INSERT INTO TEMP (ID, Name)
SELECT 1, 'VASANTH' UNION ALL
SELECT 2, 'RAJA' UNION ALL
SELECT 3, 'KARTIK'

To View the Inserted Records in the Table Temp :

SELECT * FROM TEMP

Stored procedure to Insert the Record in the Table Temp with XML Value as Input Parameter

CREATE PROCEDURE usp_InsertTemp
@XMLValue VARCHAR(MAX)
AS
BEGIN

DECLARE @XmlVar INT

EXEC sp_xml_preparedocument @XmlVar OUTPUT, @XMLValue

SELECT ID, Name INTO #TEMP FROM OPENXML(@Xmlvar, '/InsertTemp/Val', 2)
WITH(ID int, Name varchar(50))

EXEC sp_xml_removedocument @XmlVar

INSERT INTO temp (ID, Name) SELECT ID, Name From #TEMP

END

To execute the Stored Procedure :

EXEC usp_InsertTemp
'<InsertTemp>

<Val><ID>4</ID><Name>Singh</Name></Val>

<Val><ID>5</ID><Name>Vinoth</Name></Val>

<Val><ID>6</ID><Name>prakash</Name></Val>

<Val><ID>7</ID><Name>Karu</Name></Val>

</InsertTemp>'

The XML is framed like

In the Open XML Function the RootNode and ParentNode has to be defined clearly.

when an error occurs the processing in the TRY block stops and processing is then picked up in the CATCH block.

* Each TRY block is associated with only one CATCH block and must be followed immediately by an associated CATCH block.

* The CATCH block is executed only when any error occurs. If no errors, then the next statement after the END CATCH is executed.

* TRY-CATCH constructs can be nested.

* On ERROR, the control passes to the first statement in the associated CATCH block.

Here it is done with an example :

Step 1 : Created a stored procedure for a non-existent table - Temp

CREATE PROCEDURE usp_TestingException
as
BEGIN
SELECT * FROM Temp
END

Step 2 : Run the stored procedure using the below code, the error is sent to the CATCH block and an error message is returned. At this point processing can continue without a problem.

Example 1:

BEGIN TRY
EXECUTE usp_TestingException
END TRY

BEGIN CATCH
SELECT
Error_Number() as ErrorNumber,
Error_Severity() as ErrorSeverity,
Error_State() as ErrorState,
Error_procedure() as ErrorProcedure,
Error_Line() as ErrorLine,
Error_Message() as ErrorMessage
END CATCH

Example 2 :

BEGIN TRY
SELECT GETDATE()
SELECT 1/0
END
BEGIN CATCH
SELECT
Error_Number() as ErrorNumber,
Error_Severity() as ErrorSeverity,
Error_State() as ErrorState,
Error_procedure() as ErrorProcedure,
Error_Line() as ErrorLine,
Error_Message() as ErrorMessage
END CATCH

CREATE PROCEDURE usp_CheckAmstrongNumber
DECLARE @InputNumber INT
AS
BEGIN

SET NOCOUNT ON;

DECLARE @IncNo INT
DECLARE @TotalCnt INT
DECLARE @AmstrongNo INT

SET @AmstrongNo = @InputNumber
SET @IncNo = 0
SET @TotalCnt = 0

WHILE (LEN(@AmstrongNo) >= @IncNo)
BEGIN

SET @TotalCnt = @TotalCnt + POWER(@InputNumber % 10, LEN(@AmstrongNo))
SET @InputNumber = @InputNumber / 10
SET @IncNo = @IncNo + 1

END

IF(@AmstrongNo = @TotalCnt)
SELECT 'Amstrong Number'
ELSE
SELECT 'Not a Amstrong Number'

END
GO

CREATE PROCEDURE [dbo].[usp_UpdateTableRecords]
-- Add the parameters for the stored procedure here
@TableName Varchar(100) = 'tbl_Name', -- Name of the Table
@Location Varchar(MAX) = 'C:\DBUpdate\Table\tbl_Name.csv' -- Excel File Location
AS
BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

--Variable Declaration
DECLARE @ColName VARCHAR(MAX)
DECLARE @Condition as VARCHAR(MAX)
DECLARE @Var as VARCHAR(100)
DECLARE @Qry as VARCHAR(MAX)
DECLARE @ColumnName VARCHAR(MAX)
DECLARE @Condition1 as VARCHAR(MAX)
DECLARE @ColumnName1 VARCHAR(MAX)
DECLARE @FieldName VARCHAR(MAX)
SET @Condition = ''
SET @Condition1 = ''
SET @ColumnName1 = ''
-- Insert statements for procedure here

IF EXISTS(SELECT * FROM Sysobjects WHERE type = 'u' and name = 'tbl_TEMP1234')
DROP TABLE tbl_TEMP1234

SET @Qry = 'Select * into tbl_TEMP1234 from ' + @TableName + ' where 1 = 2'
EXEC (@Qry)

SET @Qry = 'Bulk Insert tbl_TEMP1234 From ''' + @Location + ''' WITH (FIELDTERMINATOR = '','',ROWTERMINATOR = ''\n'')'
EXEC (@Qry)

SELECT @ColName = Coalesce(@ColName,'') + KU.Column_name + ',' FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KU ON TC.CONSTRAINT_TYPE = 'PRIMARY KEY' AND
TC.CONSTRAINT_NAME = KU.CONSTRAINT_NAME AND KU.Table_name = @TableName

IF(@colName <> '')
BEGIN
WHILE (@ColName != '')
BEGIN
SET @Var = (SELECT LTRIM(SUBSTRING(@Colname, 1,charindex(',', @Colname)-1)))
SET @Condition = @Condition + (@Var + ' Not in (SELECT ' + @Var + ' From ' + @TableName + ') and ')
SET @Condition1 = @Condition1 + (@TableName + '.' + @Var + ' = tbl_TEMP1234.'+ @Var + ' and ')
SET @ColName = (CASE WHEN (LEFT(@ColName, 1) != ',') THEN SUBSTRING(@ColName, charindex(',', @ColName) + 1, LEN(@ColName)) ELSE '' END)
END

SET @Condition = (SELECT Substring(@Condition, 1, Len(@Condition) - 4))
SET @Condition1 = (SELECT Substring(@Condition1, 1, Len(@Condition1) - 4))

SELECT @ColumnName = Coalesce(@ColumnName,'') + b.NAME + ',' FROM syscolumns b
INNER JOIN sysobjects a ON a.id = b.id AND a.TYPE = 'u' AND a.NAME = @TableName

SET @FieldName = @ColumnName
WHILE (@FieldName != '')
BEGIN
SET @Var = (SELECT LTRIM(SUBSTRING(@FieldName, 1,charindex(',', @FieldName)-1)))
SET @ColumnName1 = (@ColumnName1 + @TableName + '.' + @Var + ' = tbl_TEMP1234.'+ @Var + ',')
SET @FieldName = (CASE WHEN (LEFT(@FieldName, 1) != ',') THEN SUBSTRING(@FieldName, charindex(',', @FieldName) + 1, LEN(@FieldName)) ELSE '' END)
END

SET @ColumnName = (SELECT Substring(@ColumnName, 1, Len(@ColumnName) - 1))
SET @ColumnName1 = (SELECT Substring(@ColumnName1, 1, Len(@ColumnName1) - 1))

SET @Qry = ('UPDATE ' + @TableName + ' SET ' + @columnName1 + ' FROM tbl_TEMP1234 WHERE ' + @Condition1)
EXEC (@Qry)

SET @Qry = ('INSERT INTO ' + @TableName + '(' + @ColumnName + ')
SELECT * FROM tbl_TEMP1234 WHERE ' + @Condition)
EXEC (@Qry)
END

DROP TABLE tbl_TEMP1234

END

Similarly we can upload the file with any field and row Terminator

ALTER FUNCTION [dbo].[udsf_WorkingHrsinMin]
(
-- Add the parameters for the function here
@StartDate Datetime, @EndDate Datetime, @Format varchar(10)
)
RETURNS VARCHAR(30)
AS
BEGIN

-- Declare the return variable here
DECLARE @Age VARCHAR(30)
DECLARE @FromDate as Datetime
DECLARE @TodayEndTime as Datetime
DECLARE @WorkingMin as INT
SET @FromDate = @StartDate

IF(Convert(Varchar(10),@StartDate,101) = Convert(Varchar(10),@EndDate,101))
BEGIN

IF(DateName(dw, @StartDate) = 'Saturday' OR DateName(dw, @StartDate) = 'Sunday')
SET @WorkingMin = 0
ELSE
SET @WorkingMin = Datediff("mi",@StartDate, @EndDate)
END

ELSE
BEGIN

WHILE(@StartDate < @EndDate)
BEGIN

IF (Convert(varchar(10),@StartDate,101) = Convert(Varchar(10),@FromDate,101))
BEGIN

IF (DateName(dw, @StartDate) = 'Saturday' OR DateName(dw, @StartDate) = 'Sunday')
SET @WorkingMin = 0
ELSE
SET @WorkingMin = Datediff("mi", @StartDate, Convert(Datetime, Convert(varchar(10),@FromDate,101) + ' 11:59 PM')) + 1
END

ELSE IF (Convert(varchar(10),@StartDate,101) = Convert(Varchar(10),@EndDate,101))
BEGIN
IF (DateName(dw, @StartDate) = 'Saturday' OR DateName(dw, @StartDate) = 'Sunday')
SET @WorkingMin = @WorkingMin + 0
ELSE
SET @WorkingMin = @WorkingMin + Datediff("mi", @StartDate, @EndDate)
END

ELSE
BEGIN
SET @TodayEndTime = Convert(Datetime, Convert(varchar(10),@StartDate,101) + ' 11:59 PM')
IF (DateName(dw, @StartDate) = 'Saturday' OR DateName(dw, @StartDate) = 'Sunday')
SET @WorkingMin = @WorkingMin + 0
ELSE
SET @WorkingMin = @WorkingMin + Datediff("mi", @StartDate, @TodayEndTime) + 1
END
SET @StartDate = Dateadd("d",1, convert(varchar(10),@StartDate,101))

END
END

IF ((@WorkingMin/1440) > 0)
BEGIN
IF (@Format = 'DDHHMM')
SELECT @Age = Convert(Varchar,(@WorkingMin/1440)) + ' D : ' + CONVERT(Varchar, ((@WorkingMin%1440)/60)) + ' H : ' + CONVERT(Varchar, ((@WorkingMin%1440)%60)) + ' M'
ELSE IF (@Format = 'DDHH')
SELECT @Age = Convert(Varchar,(@WorkingMin/1440)) + ' D : ' + CONVERT(Varchar, ((@WorkingMin%1440)/60)) + ' H'
ELSE IF (@Format = 'DD')
SELECT @Age = Convert(Varchar,(@WorkingMin/1440)) + ' D'
ELSE
SELECT @Age = @WorkingMin
END

ELSE
BEGIN
IF (@Format = 'DDHHMM')
SELECT @Age = '0 D : ' + CONVERT(Varchar, ((@WorkingMin%1440)/60)) + ' H : ' + CONVERT(Varchar, ((@WorkingMin%1440)%60)) + ' M'
ELSE IF (@Format = 'DDHH')
SELECT @Age = '0 D : ' + CONVERT(Varchar, ((@WorkingMin%1440)/60)) + ' H'
ELSE IF (@Format = 'DD')
SELECT @Age = '0 D'
ELSE
SELECT @Age = @WorkingMin
END

-- Return the result of the function
RETURN @Age

END
GO

CREATE FUNCTION [dbo].[udsf_GetWeekEndCount]
(
-- Add the parameters for the function here
@StartDate Datetime, @EndDate Datetime
)
RETURNS INT
AS
BEGIN

DECLARE @HolidayCnt as Int
SET @HolidayCnt = 0

WHILE(@StartDate <= @EndDate)
BEGIN

IF(DateName(dw, @StartDate) = 'Saturday' OR DateName(dw, @StartDate) = 'Sunday')
SET @HolidayCnt = @HolidayCnt + 1
SET @StartDate = Dateadd("d", 1, @StartDate)

END

RETURN @HolidayCnt END
GO

It is called like "Select dbo.udsf_GetWeekEndCount('7/1/2009', '7/11/2009')
The result is 3

Declare @Value as Varchar(max)
SELECT @Value = COALESCE(@Value + ',', '') + Convert(varchar,ID) FROM Temp1 ORDER BY ID
Select @Value

DECLARE @val AS VARCHAR(MAX)
DECLARE @Delimiter AS VARCHAR(5)

SET @Val = '1,3,4,5,6,134,234,234,213,34'
--SET @Val = 'a, b, c, d, e'
SET @Delimiter = ','
SET @Val = @Val + @Delimiter

CREATE TABLE #Temp (val VARCHAR(50))

WHILE (@Val != '')
BEGIN

INSERT INTO #Temp
SELECT LTRIM(SUBSTRING(@val, 1,charindex(@Delimiter, @val)-1))
SET @Val = (CASE WHEN (LEFT(@val, 1) != @Delimiter) THEN SUBSTRING(@val, charindex(@Delimiter, @val) + 1, LEN(@Val)) ELSE '' END)

END

SELECT * FROM #temp
DROP TABLE #Temp