Fun with CURSOR, print all your SP

Use cursor to print all stored procedure in your database as a text. Use the code below to get all your SP in current database.

USE MyDatabase
GO
DECLARE @proc_Name VARCHAR(100)

DECLARE @mySP_cursor CURSOR FOR
SELECT s.name FROM sysobjects s WHERE type = 'P'

OPEN @mySP_cursor 
FETCH NEXT FROM @mySP_cursor  INTO @proc_Name

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC sp_HelpText @procName
    FETCH NEXT FROM @mySP_cursor  INTO @proc_Name
END
CLOSE @mySP_cursor 
DEALLOCATE @mySP_cursor 
GO 

Happy coding... :)

Post Reference: Vikram Aristocratic Elfin Share 

Surrogate Key, Candidate Key, Primary Key, Alternate Key and Composite Key

A candidate key is one that can identify each row of a table uniquely. Generally a candidate key becomes the primary key of the table. If the table has more than one candidate key, one of them will become the primary key, and the rest are called alternate keys.

A key formed by combining at least two or more columns is called composite key.

Surrogate Key : Artificial key generated internally that has no real meaning outside the Db (e.g. a Unique Identifier or Int with Identity property set etc.). Implemented in SQL Sevrver by Primary Key Constraints on a column(s).

Post Reference: Vikram Aristocratic Elfin Share

Update Employee Table with MERGE

Once very good friend of mine asked me how to update an employee master table having fields
emp_id,
emp_name,
department_id,  (refer to dept_id of Department_Master table) 
department_name

where department_name fields is null and he want to update department_name with the correspondent department_id.

The solution is very simple i told him to do it by cursor or using temporary table and loop through the temp table.

But today when i was toying with MERGE i found one more solution to same question, i have yet not executed it but i m pretty confident it will work and it is the best solution to the above question

MERGE Employee_Master AS ed
USING (SELECT dept_id,dept_name FROM Department_Master) AS dm
ON dm.dept_id = em.dept_id
WHEN MATCHED THEN UPDATE SET em.dept_name = dm.dept_name;

Just try it n see the result.
Happy coding :) 

Post Reference: Vikram Aristocratic Elfin Share

Fetch Excel Records using ADO.Net

This code snap will fetch each record from your excel sheet 1 located at D:\ drive and return records as datatable.

public static  DataTable  GetItemsFromExcel1()
{
    DataTable dt = new DataTable();
    oleDbConnection excelConnection =
                        new  OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;"
                    + @"Data Source=D:\YourFile.xls;"
                    + @"Extended Properties=""Excel
                        8.0;HDR=Yes;IMEX=1;""");

    excelConnection.Open();
    try
    {
       OleDbDataAdapter dbAdapter =
       new OleDbDataAdapter("SELECT * FROM [Sheet1$]", excelConnection);
              
       dbAdapter.Fill(dt);
    }
    finally
    {
       excelConnection.Close();
    }

    return dt;
}


Post Reference: Vikram Aristocratic Elfin Share

Stuff function in SQL SERVER

The STUFF function inserts a set of characters into a given string at a given position

STUFF(character_expression1, start, length, character_expression2)

Character_Expression1 represents the string in which the stuff is to be applied. start indicates the starting position of the character in character_expression1, length is the length of characters which need to be replaced. character_expression2 is the string that will be replaced to the start position

1) SELECT STUFF('Fun', 1, 0, '*')
    the output will be : *Fun.

2) SELECT STUFF('SQL SERVER is USEFUL',5,6,'DATABASE')
    the output will be : SQL DATABASE is USEFUL



Post Reference: Vikram Aristocratic Elfin Share