MSSQL get current datetime

SELECT CURRENT_TIMESTAMP

or

SELECT {fn NOW()}

or

SELECT GETDATE()

SQL 2005: T-SQL Get table name or Stored Procedure name

How to get all the tables' name?

Select * 
From sysobjects 
Where type = 'U'
Order By name

Change the type to 'P' and you will get all the Stored Procedures.
Change the type to 'F' and you will get all the UDF (User-Defined Function).
More details from here.

Another way to get the tables:

Select *
From sys.tables

How to get the content of stored procedures?

Select text From syscomments Where id = 
(Select id From sysobjects Where type='P' and name = 'SprocName')

or

Exec sp_helptext SprocName

I prefer the second one.

SQL 2005 Paging

You can find many ways to implement the paging in the stored procedures. Here is the one that I learned from my colleague.

Create PROCEDURE [dbo].[getMemberInfo] (
@pageNumber int = 1, --which page you want to fetch
@rowsPerPage int = 50 --how many rows per page
)
AS
BEGIN
SET NOCOUNT ON;

Declare @startRowIndex int,
@endRowIndex int

Set @startRowIndex = ((@pageNumber - 1) * @rowsPerPage) + 1
Set @endRowIndex = @startRowIndex + @rowsPerPage - 1

Select * 
From (
Select *,
row_number() Over (Order By accountName) As rownum
From dbo.Member
)
Where rownum Between @startRowIndex And @endRowIndex
End
GO

Remove DBNull

If you are getting data from SQL server, it always has a chance that you will get the Null in your table cell. But the meaning of "Null" in database is different than it in the .Net. So how to remove the Null from the resultset getting from the database?
1.Use IsNull() function in your stored procedures.

Select Name, IsNull(Age, 18) From Members

2.Use the following simple method

public DataTable RemoveDBNull(DataTable dt)
{
  for (int i = 0; i < dt.Rows.Count; i++)
  {
    for (int j = 0; j < dt.Columns.Count; j++)
    {
      if (dt.Rows[i][j] == DBNull.Value)
        dt.Rows[i][j] = 0;
    }
  }
  return dt;
}

A generic method to call Stored Procedures

Scenario:
1.You need to call many Stored Procedures (Sproc), but you don't want to create a method for each of them.
2.You have some Sprocs, and each of them has different numbers of input parameters.

Here is what I did:

private DataSet DBConnectionForGettingData(string sprocName, Dictionary<string, string> paramList)
{
  using (SqlConnection conn = new SqlConnection(str_ConnectionString))
  {
    SqlCommand cmd = new SqlCommand();
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandText = sprocName;
    cmd.Connection = conn;

    //loop through the dictionary
    foreach (string key in paramList.Keys)
    {
      cmd.Parameters.AddWithValue(key, paramList[key]);
    }

    SqlDataAdapter da = new SqlDataAdapter(cmd);
    DataSet ds = new DataSet();
    da.Fill(ds);

    //release the resource
    cmd = null;
    da = null;

    return ds;
  }
}

The paramList is the collection of parameters. All you need to do is create a Dictionary object and add all the parameters to this object. You can also adjust the arguments of this method to make it more flexible.

My easy and simple way to raise an error in T-SQL (4/21/2010 updated)

Sometimes I need to know if a database operation is success or failure in my code (not in stored procedures) and perform different behaviors. My easy and simple way is raising an error in stored procedures and return a specific error message to my application, and I will check if that message is presented in the returning result. The syntax is:

Begin Try
  Update xxxx
  Set yy = zzz
  Where aa = 123
  Select @updateRowcount = @@Rowcount, @ErrorCode = @@Error

  If (@updateRowcount = 0)
    Raiserror('0 row(s) affected', 11, 1)
  If (@ErrorCode != 0)
    Raiserror('Update failed.', 11, 1)

End Try

Begin Catch
  Select Error_number(), Error_line(), Error_message()
  return
End Catch

The string in the Raiserror() function is the "specific message".

Another way to bulk update data in SQL (Updated)

In a small application, sometimes we need to update some among of data in database frequently. I ever mentioned how to convert xml into a table in SQL, so that we can convert those data into a XML file and send it to SQL server.
What if those data that need to be updated contain many duplicate columns? We can send those duplicate columns just once, and concatenate those non-duplicate columns into a string and send it to SQL as a parameter. It works like this:
1.create a method to fetch data from List and merge them into a string by using a delimiter to seperate them.
For example:

//This is a new feature of C# 3.0. Take a look of this:
//"http://blogs.msdn.com/abhinaba/archive/2005/09/17/470358.aspx".
List<int> CustomerIdList = new List<int>{1001,1002,1003,1004};
string CustomerIds = MergeData(CustomerIdList);

Create your own MergeData() method to merge the input List<int> and output as a single string like this: "1001,1002,1003,1004".
2.Send this string as a parameter to the SQL server.
3.At SQL server, we need to create a Table-Valued function to parse this string into a table.

CREATE FUNCTION [dbo].[fxnParseCommaDelmitedList]
(
@CommaDelimitedList varchar(8000)
)
RETURNS @TableVar TABLE (ItemID int NOT NULL )
AS
BEGIN
  DECLARE @IDListPosition int
  DECLARE @IDList varchar(4000)
  DECLARE @ArrValue varchar(4000)
  SET @IDList = COALESCE(@CommaDelimitedList, '')
  IF @IDList <> ''
  BEGIN
  -- Add comma to end of list so user doesn''t have to
  SET @IDList = @IDList + ','
  -- Loop through the comma demlimted string list
  WHILE PATINDEX('%,%' , @IDList ) <> 0
    BEGIN
      SELECT @IDListPosition = PATINDEX('%,%' , @IDList)
      SELECT @ArrValue = LEFT(@IDList, @IDListPosition - 1)
      -- Insert parsed ID into TableVar for "where in select"
      INSERT INTO @TableVar (ItemID) VALUES (CONVERT(int, @ArrValue))
      -- Remove processed string
      SELECT @IDList = STUFF(@IDList, 1, @IDListPosition, '')
    END
  END
  RETURN
END

This sproc will return a table like this:

ItemID (column name)
1001
1002
1003
1004

A lighter way than using XML file.

Convert xml to table in SQL 2005

This is how I convert XML string into a Table in SQL 2005:

DECLARE @tempTable TABLE (
userId INT,
userName NVARCHAR(50),
password NVARCHAR(50)
)
DECLARE @xml XML
SET @xml='
<row userId="67" userName="Kenny1" password="1234" />
<row userId="80" userName="Kenny2" password="5678" />'

INSERT INTO @tempTable
SELECT Tbl.Col.value('@userId', 'INT'),
       Tbl.Col.value('@userName', 'NVARCHAR(50)'),
       Tbl.Col.value('@password', 'NVARCHAR(50)')
FROM   @xml.nodes('//row') Tbl(Col)

--See the table
SELECT * FROM @tempTable 

Web page search and SQL

Here is a way to search many columns in a table:

CREATE PROCEDURE [dbo].[SearchExample] (
@userId UNIQUEIDENTIFIER = NULL,   --set all parameters' default value to null
@firstName NVARCHAR(50) = NULL,
@lastName NVARCHAR(50) = NULL,
@address NVARCHAR(100) = NULL,
@zipcode INT = NULL
)

SELECT ...  --omit
FROM   dbo.UserInfo u
WHERE  u.userId = COALESCE(@userId, u.userId)
AND    u.firstName LIKE ('%'+ COALESCE(@firstName, u.firstName)+'%')
AND    ...  --omit
...    --omit

Set the default value to null for all the input parameters is because if we don't have any filter to search (the value is null), then COALESCE() will use the second parameter as the output, otherwise it will output the first parameter.
For example, if @userId = 1001, the

WHERE  u.userId = COALESCE(@userId, u.userId)

will become like

WHERE  u.userId = 1001

Otherwise, it will become like

WHERE  u.userId = u.userId