You can pass a name string into USER_ID() function and get the current database ID for that name. This is very useful when we are accessing the system tables and need to check names against group membership or permission value.
Continue reading “USER_ID() and USER_NAME() in SQL Server”
Month: November 2011
How to Backup and Restore of SQL Server database through VB.NET
This article describes how to back up and restore a SQL Server database using vb.net. The following code example requires a windows form with some combobox and textbox controls (see below picture.) Continue reading “How to Backup and Restore of SQL Server database through VB.NET”
SPACE() and REPLACE() functions in SQL Server
SPACE()
The SPACE() function returns the specified number of the spaces. This function is mainly useful for formatting.
Continue reading “SPACE() and REPLACE() functions in SQL Server”
SUBSTRING() function in SQL Server
The SUBSTRING() function returns part of a character, text, binary, or image expression.
LEN() and REPLICATE() functions in SQL Server
LEN() function is used to return the number of characters of a given character string. It allows us to determine the length of string correctly in characters, and whether or not a unicode string is being used.
REPLICATE() function returns a character string repeated a specified number of times.It can be useful for formatting output from a query. for example you can print ‘-‘ character many times with the help of PRINT statement.
Example:
Suppose we have a table ‘tbl_employee’
Table name: tbl_Employee
Employee Id | EmpName | Salary | Department |
1 | Ankur | 1200 | Sales |
2 | David | 3400 | Sales |
3 | John | 1500 | HR |
4 | James | 1200 | Production |
5 | Mohan | 4500 | Production |
6 | Ram | 2300 | HR |
from the following query we can get length of Employee name:
SELECT LEN(EmpName) AS NameOFLength, EmpName
FROM tbl_employee
WHERE Department= ‘HR’;
Output Will be:
NameOfLength | EmpName |
4 | John |
3 | Ram |
Example of Replicate() function:
The following example replicates a ‘0’ character four times leading with three character of EmpName and trailing with EmployeeId.
SELECT [Empname], LEFT(UPPER([Empname]),3) + ‘-‘ + REPLICATE(‘0’, 4) +
Convert(CHAR(1),[Employee id]) AS ‘EmpCode’
FROM tbl_employee
Output Will be:
EmpName | EmpCode |
Ankur | ANK-00001 |
David | DAV-00002 |
John | JOH-00003 |
James | JAM-00004 |
Mohan | MOH-00005 |
Ram | RAM-00006 |
Difference between Stored Procedure and Function
Here i am presenting an exhaustive list of difference between Stored Procedures and Functions in Database.
1. Functions can be called from procedure where as procedures cannot be called from function.
2. Procedures cannot be utilized in a select statement whereas function can be embedded in a select statement.
3. Function must return one and only one value where as Procedure can return any number of values (0 to n).
4. Function can only have input parameters only where as Procedure can have input as well as output parameters.
5. Function can have only select statement in it where as a Procedure can have select as well as DML statement in it.
6. Procedure can use try-catch block to handle exception whereas try-catch block cannot be used in a function.
7. Function can be used in an assignment operation whereas Procedure cannot be part of assignment operation.
Get selected Font and Font Color through Font Dialog
This code sample shows how to get selected font and color through font dialog object in vb.net :
Continue reading “Get selected Font and Font Color through Font Dialog”
How to create sql connection in VB.NET
In this example we are using SQLClient.SqlConnection class.The following example creates and opens a SqlConnection.
Continue reading “How to create sql connection in VB.NET”
How to find out hex value of a color using vb.net
The following code sample shows how we can get hex value from System.Drawing.Color using vb.net.
Continue reading “How to find out hex value of a color using vb.net”
RTRIM() and LTRIM() functions in SQL Server
RTRIM() function removes any trailing blanks from a string or column. And LTRIM() removes blanks from the beginning of a string instead of end.
RTRIM() and LTRIM() functions can be used with any a constant, variable, or column of either character or binary data. But you need to make sure that
expression whixh you want to use with these functions must be of a data type that is implicitly convertible to varchar. Otherwise, you need to use CAST to explicitly convert expression.
Continue reading “RTRIM() and LTRIM() functions in SQL Server”