Replace function in sql server

The Replace function in SQL is used to update the content of a string. The function call is REPLACE() for MySQL, Oracle, and SQL Server. The syntax of the Replace function is:

Replace(str1, str2, str3): In str1, find where str2 occurs, and replace it with str3.

Assume we have the following table:

Table Geography region_name store_name
East Boston
East New York
West Los Angeles
West San Diego


If we apply the following Replace function:

SELECT REPLACE(region_name, 'ast', 'astern')
FROM Geography;

Result: region_name
Eastern
Eastern
West
West

case statement in select query in sql server

A special scalar expression in SQL language is CASE expression. SQL CASE expression is used as a kind of IF-THEN-ELSE statement. It is similar to switch statement in modern programming language such as Java or C#. The syntax of the CASE statement is simple as follows :






SELECT name,salary,  
CASE      
WHEN  salary <= 2000 THEN 'low'
WHEN  salary > 2000 AND salary <= 3000 THEN 'average'
WHEN  salary > 3000 THEN 'high'
END AS salary_level  
FROM employees  
ORDER BY salary ASC

select columns name from table schema in sql server

select COLUMN_NAME from information_schema.columns where table_name='TRGToken'

Shrink database of sql server 2005

DBCC SHRINKFILE(<TransactionLogName>, 1)
BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(<TransactionLogName>, 1)

Shrink Virtual Log files in sql server 2005

USE AdventureWorks
GO
BACKUP LOG AdventureWorks TO DISK='d:\adtlog.bak'
GO
-- Get Logical file name of the log file
sp_helpfile
GO
DBCC SHRINKFILE(AdventureWorks_Log,TRUNCATEONLY)
GO
ALTER DATABASE AdventureWorks
MODIFY FILE
(NAME = AdventureWorks_Log,SIZE = 1GB)
GO
DBCC LOGINFO
GO



Again, here I have assumed that your initial log size is 1 GB, but in reality you should select the number based on your own ideal size of the log file. If your log file grows to 10 GB every day, you may want to put the value as 10 GB.

Shrink Database in sql server

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER proc [dbo].[spShrink]
as
Begin
backup log kamaononstop with truncate_only
dbcc shrinkdatabase (kamaononstop,10,truncateonly)

ALTER DATABASE kamaononstop
MODIFY FILE ( NAME = N'kamaononstop',
MAXSIZE = 1024000KB , FILEGROWTH = 50%)

ALTER DATABASE kamaononstop
MODIFY FILE ( NAME = N'kamaononstop_log',
MAXSIZE = 1024000KB , FILEGROWTH = 50%)

END

sql server join with example

create PROCEDURE dbo.join_test
/*
(
@parameter1 int = 5,
@parameter2 datatype OUTPUT
)
*/
AS
begin


declare  @tbl1   table (id numeric(10),[name] varchar(50))

declare  @tbl2   table (id numeric(10),[name] varchar(50) )

insert into @tbl1 values(1,'a')
insert into @tbl1 values(2,'b')
insert into @tbl1 values(3,'c')
insert into @tbl1 values(4,'d')
insert into @tbl1 values(5,'e')

insert into @tbl2 values(1,'p')
insert into @tbl2 values(2,'q')
insert into @tbl2 values(6,'r')
insert into @tbl2 values(7,'s')
insert into @tbl2 values(8,'t')

-- select a.id , a.name,b.id,b.name  from @tbl1 as a left outer join  @tbl2 as b on a.id=b.id

-- select a.id , a.name,b.id,b.name  from @tbl1 as a right outer join  @tbl2 as b on a.id=b.id

-- select a.id , isnull (a.name,'no name'),b.id,b.name  from @tbl1 as a full outer join  @tbl2 as b on a.id=b.id

-- select a.id , a.name,b.id,b.name  from @tbl1 as a full outer join  @tbl2 as b on a.id=b.id where a.id is null or b.id is null

-- select a.id , a.name,b.id,b.name  from @tbl1 as a cross join  @tbl2 as b where a.id=b.id

end

SP Performance Improvement

SQL SERVER – Stored Procedure Optimization Tips – Best Practices
February 16, 2010 by pinaldave
We will go over how to optimize Stored Procedure with making simple changes in the code. Please note there are many more other tips, which we will cover in future articles.


Include SET NOCOUNT ON statement: With every SELECT and DML statement, the SQL server returns a message that indicates the number of affected rows by that statement. This information is mostly helpful in debugging the code, but it is useless after that. By setting SET NOCOUNT ON, we can disable the feature of returning this extra information. For stored procedures that contain several statements or contain Transact-SQL loops, setting SET NOCOUNT to ON can provide a significant performance boost because network traffic is greatly reduced.
CREATE PROC dbo.ProcName
AS
SET NOCOUNT ON;
--Procedure code here
SELECT column1 FROM dbo.TblTable1
-- Reset SET NOCOUNT to OFF
SET NOCOUNT OFF;
GO

Use schema name with object name: The object name is qualified if used with schema name. Schema name should be used with the stored procedure name and with all objects referenced inside the stored procedure. This help in directly finding the complied plan instead of searching the objects in other possible schema before finally deciding to use a cached plan, if available. This process of searching and deciding a schema for an object leads to COMPILE lock on stored procedure and decreases the stored procedure’s performance. Therefore, always refer the objects with qualified name in the stored procedure like
SELECT * FROM dbo.MyTable -- Preferred method
-- Instead of
SELECT * FROM MyTable -- Avoid this method
--And finally call the stored procedure with qualified name like:
EXEC dbo.MyProc -- Preferred method
--Instead of
EXEC MyProc -- Avoid this method

Do not use the prefix “sp_” in the stored procedure name: If a stored procedure name begins with “SP_,” then SQL server first searches in the master database and then in the current session database. Searching in the master database causes extra overhead and even a wrong result if another stored procedure with the same name is found in master database.
Use IF EXISTS (SELECT 1) instead of (SELECT *): To check the existence of a record in another table, we uses the IF EXISTS clause. The IF EXISTS clause returns True if any value is returned from an internal statement, either a single value “1” or all columns of a record or complete recordset. The output of the internal statement is not used. Hence, to minimize the data for processing and network transferring, we should use “1” in the SELECT clause of an internal statement, as shown below:
IF EXISTS (SELECT 1 FROM sysobjects
WHERE name = 'MyTable' AND type = 'U')

Use the sp_executesql stored procedure instead of the EXECUTE statement.
The sp_executesql stored procedure supports parameters. So, using the sp_executesql stored procedure instead of the EXECUTE statement improve the re-usability of your code. The execution plan of a dynamic statement can be reused only if each and every character, including case, space, comments and parameter, is same for two statements. For example, if we execute the below batch:
DECLARE @Query VARCHAR(100)
DECLARE @Age INT
SET @Age = 25
SET @Query = 'SELECT * FROM dbo.tblPerson WHERE Age = ' + CONVERT(VARCHAR(3),@Age)
EXEC (@Query)

If we again execute the above batch using different @Age value, then the execution plan for SELECT statement created for @Age =25 would not be reused. However, if we write the above batch as given below,

DECLARE @Query NVARCHAR(100)
SET @Query = N'SELECT * FROM dbo.tblPerson WHERE Age = @Age'
EXECUTE sp_executesql @Query, N'@Age int', @Age = 25

the compiled plan of this SELECT statement will be reused for different value of @Age parameter. The reuse of the existing complied plan will result in improved performance.

Try to avoid using SQL Server cursors whenever possible: Cursor uses a lot of resources for overhead processing to maintain current record position in a recordset and this decreases the performance. If we need to process records one-by-one in a loop, then we should use the WHILE clause. Wherever possible, we should replace the cursor-based approach with SET-based approach. Because the SQL Server engine is designed and optimized to perform SET-based operation very fast. Again, please note cursor is also a kind of WHILE Loop.
Keep the Transaction as short as possible: The length of transaction affects blocking and deadlocking. Exclusive lock is not released until the end of transaction. In higher isolation level, the shared locks are also aged with transaction. Therefore, lengthy transaction means locks for longer time and locks for longer time turns into blocking. In some cases, blocking also converts into deadlocks. So, for faster execution and less blocking, the transaction should be kept as short as possible.
Use TRY-Catch for error handling: Prior to SQL server 2005 version code for error handling, there was a big portion of actual code because an error check statement was written after every t-sql statement. More code always consumes more resources and time. In SQL Server 2005, a new simple way is introduced for the same purpose. The syntax is as follows:
BEGIN TRY
--Your t-sql code goes here
END TRY
BEGIN CATCH
--Your error handling code goes here
END CATCH

sp_executesql

ALTER procedure [dbo].[EditChallanEntry]
 @ChlLink int,
 @BankCode char(30),
--@DDOCode char(30),
@MajorHead char(30),
@BudgetHead char(30),
--@Transtype int,
--@RemitterName char(30),
@DivisionHead char(30),
@Amount decimal,
@Commission decimal
 as
DECLARE @sql NVARCHAR(3000)
DECLARE @paradef NVARCHAR(3000)
--DECLARE @challanno  int
Begin
set @sql='Update Challan set BankBranchCode= @BankCode,MajorHead=@MajorHead,BudgetHead=@BudgetHead,DivCode=@DivisionHead,TotalAmt= @Amount,Commission=@Commission where convert(varchar(25),SUBSTRING(BudgetHead,10,4)+Convert(varchar(5),chlLink))= @ChlLink'
set @paradef=N'@ChlLink int,@BankCode char(30),@MajorHead char(30),@BudgetHead char(30),@Amount decimal,@Commission decimal,@DivisionHead char(30)'
execute sp_executesql @sql,@paradef,@ChlLink,@BankCode,@MajorHead,@BudgetHead,@Amount,@Commission,@DivisionHead
--select @sql
end

Sparce column in sqlserver 2008

SQL SERVER – 2008 – Introduction to SPARSE Columns
July 10, 2008 by pinaldave

I have been writing recently about how SQL Server 2008 is better in terms of Data Stage and Backup Management. I have received very good replies from many users and have requested to write more about it. Today we will look into another interesting concept of SPARSE column. The reason I like this feature because it is way better in terms of how columns are managed in SQL Server.

SPARSE column are better at managing NULL and ZERO values in SQL Server. It does not take any space in database at all. If column is created with SPARSE clause with it and it contains ZERO or NULL it will be take lesser space then regular column (without SPARSE clause).

In SQL Server 2008 maximum column allowed per table is 1024. All the SPARSE columns does not count to this limit of 1024. The maximum limit of SPARSE column is 100,000. In summary any table can have maximum of 100,000 SPARSE and 1024 regular columns.

Let us see following example of how SPARSE column saves space in database table.
CREATE TABLE UnSparsed(ID INT IDENTITY(1,1),
FirstCol INT,
SecondCol VARCHAR(100),
ThirdCol SmallDateTime)
GO
CREATE TABLE Sparsed(ID INT IDENTITY(1,1),
FirstCol INT SPARSE,
SecondCol VARCHAR(100) SPARSE,
ThirdCol SmallDateTime SPARSE)
GO
DECLARE @idx INT = 0
WHILE @idx < 50000
BEGIN
INSERT INTO UnSparsed VALUES (NULL,NULL, NULL)
INSERT INTO Sparsed VALUES (NULL, NULL, NULL)
SET @idx+=1
END
GO
sp_spaceused 'UnSparsed'
GO
sp_spaceused 'Sparsed'
GO
DROP TABLE UnSparsed
GO
DROP TABLE Sparsed
GO

Sql server Split String

string term =txt_term.Text;
        try
        {
            string[] a = term.ToString().Split('.');
            if (Convert.ToInt32(a[1]) < 11)
            {
                if (Convert.ToInt32(a[1]) < 10)
                {
                    txt_term.Text = a[0].ToString() + "." + "0" + a[1].ToString();
                }

            }
            else
            {
                txt_term.Text = a[0].ToString() + "." + "11";
            }
        }
        catch
        {
        }S

SQL SERVER – Find Current Location of Data and Log File of All the Database

SELECT name, physical_name AS current_file_location
FROM sys.master_files

SQL SERVER – Get Query Plan

SQL SERVER – Get Query Plan Along with Query Text and Execution Count
------------------------------------------------------------------------------



 SELECT cp.objtype AS ObjectType,
OBJECT_NAME(st.objectid,st.dbid) AS ObjectName,
cp.usecounts AS ExecutionCount,
st.TEXT AS QueryText,
qp.query_plan AS QueryPlan
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st

SQL SERVER – List Schema Name and Table Name for Database

SELECT '['+SCHEMA_NAME(schema_id)+'].['+name+']'
AS SchemaTable
FROM sys.tables

SQL SERVER – Time Delay While Running T-SQL Query

Option 1 : Waitfor Delay in executing T-SQL
T-SQL runs after particular delay is completed.
--------------------------------------------------------------

SELECT GETDATE() CurrentTime
WAITFOR DELAY '00:00:05' ---- 5 Second Delay
SELECT GETDATE() CurrentTime


----------------------------------


Option 2 : Waitfor Time in executing T-SQL
T-SQL runs after particular time has arrived.
--------------------------------------------------------------


DECLARE @MyDateTime DATETIME
/* Add 5 seconds to current time so
system waits for 5 seconds*/
SET @MyDateTime = DATEADD(s,5,GETDATE())
SELECT GETDATE() CurrentTime
WAITFOR TIME @MyDateTime
SELECT GETDATE() CurrentTime

SQL SERVER JOINs

Introduction

In this article, we’ll see the basic concepts of SQL JOINs. In the later part of the article, we’ll focus on the advanced subject of Self-JOIN and some interesting observations on how inner JOIN can be simulated using left JOIN. The author has tried his best to amalgamate various topics in a single concept.

The JOIN keyword is used in a SQL statement to query data from two or more tables based on a relationship between certain columns in these tables.

Inner JOIN

A JOIN that displays only rows that have a match in both the JOINed tables is known as inner JOIN.  This is the default type of JOIN in the Query and View Designer.

Outer JOIN

A JOIN that includes rows even if they do not have related rows in the joined table is an Outer JOIN.  You can create three different outer JOINs to specify the unmatched rows to be included:

Left Outer JOIN: In Left Outer JOIN, all rows in the first-named table, i.e. “left” table, which appears leftmost in the JOIN clause, are included. Unmatched rows in the right table do not appear.

Right Outer JOIN: In Right Outer JOIN, all rows in the second-named table, i.e. “right” table, which appears rightmost in the JOIN clause, are included. Unmatched rows in the left table are not included.

Full Outer JOIN: In Full Outer JOIN, all rows in all the joined tables are included, whether they are matched or not.

Additional Notes related to JOIN

The following are three classic examples to demonstrate the cases where Outer JOIN is useful. You must have noticed several instances where developers write query as given below.

The query demonstrated above can be easily replaced by Outer JOIN. Indeed, replacing it by Outer JOIN is the best practice. The query that generates the same result as above is shown here using Outer JOIN and WHERE clause in JOIN.

The above example can also be created using Right Outer JOIN.

NOT Inner JOIN

Remember, the term Not Inner JOIN does not exist in database terminology. However, when full Outer JOIN is used along with WHERE condition, as explained in the above two examples, it will give you exclusive result to Inner JOIN. This JOIN will show all the results that were absent in Inner JOIN.

Cross JOIN

A cross JOIN devoid of a WHERE clause produces the Cartesian product of the tables involved in the JOIN. The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table. One common example is when a company lists all its products in a pricing table to compare each product with others prices.

Self-JOIN

In this particular case, one table JOINs to itself with one or two aliases to stave off confusion. A self-JOIN can be of any type, as long as the joined tables are the same. A self-JOIN is unique in the sense that it involves a relationship with only one table. A common example is when a company has a hierarchal reporting structure whereby a member of staff reports to another member. Self-JOIN can either be an Outer JOIN or an Inner JOIN.

Self-JOIN is accomplished by using table name aliases to give each instance of the table a separate name. Joining a table to itself can be useful when you want to compare values in a column to other values of the same column. Self-JOIN is a JOIN in which records from a table are combined with other records from the same table when there are matching values in the joined fields. A self-JOIN can either be an inner JOIN or an outer JOIN. A table is joined to itself based upon a field or combination of fields that have duplicate data in different records. The data type of the inter-related columns must be of the same type or cast to the same type.

Now, think of a situation where all the data you require is contained within a single table, but data needed to extract is related to each other in the table itself. Examples of this type of data relate to employee information, where the table may have both an employee’s ID number for each record and also a field that displays the ID number of an employee’s supervisor or manager. To retrieve the data, it is mandatory for the tables to relate/JOIN to itself.

Another example that can be tried on SQL SERVER 2005 sample database AdventureWorks is to find products that are supplied by more than one vendor. Please refer to the sample database for table structure.

Note:Before we continue further let me make it very clear that INNER JOIN should be used where it cannot be used and simulating INNER JOIN using any other JOINs will degrade the performance. If there are scopes to convert any OUTER JOIN to INNER JOIN, it should be done with priority.

Run the following two scripts and observe the result-set. It will be identical.

After gazing at the identical result the first question that cropped up in my mind was - what is behind the scene plan? Looking at the actual execution plan of the query it is quite evident that even if LEFT JOIN is used in SQL Server Query Optimizer, it converts to INNER JOIN since results are the same and performance is better.

Looking at the above scenario it makes me ponder how smart Query Optimizer Engine is and how it might be saving innumerable performance-related issues for sub-optimal queries.

Now let us try to grasp the cause of LEFT JOIN acting as INNER JOIN. When 1= 1 is used in ON clause it is always true and converts LEFT JOIN to CROSS JOIN. However, when WHERE condition’s effect is applied to the above CROSS JOIN it produces a result similar to INNER JOIN in our case. SQL Server Query Optimizer interprets this in advance and uses INNER JOIN right away.

sql server Retrieve List of Primary Keys and Foreign Keys of Database

There are two different methods of retrieving the list of Primary Keys and Foreign Keys from database.

Method 1: INFORMATION_SCHEMA

SELECT
DISTINCT
Constraint_Name AS [Constraint],
Table_Schema AS [Schema],
Table_Name AS [TableName]
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
GO

Method 2: sys.objects

SELECT OBJECT_NAME(OBJECT_ID) AS NameofConstraint,
SCHEMA_NAME(schema_id) AS SchemaName,
OBJECT_NAME(parent_object_id) AS TableName,
type_desc AS ConstraintType
FROM sys.objects
WHERE type_desc IN ('FOREIGN_KEY_CONSTRAINT','PRIMARY_KEY_CONSTRAINT')

Using the SqlDataAdapter's Update Method to submit changes to the DataBase

An interesting feature of ADO.NET is using the SqlDataAdapter's Update Method. This method will iterate through all the DataRows of your DataSet and, depending on the values in the RowState, assign that row to the appropriate Stored Procedure for processing. Additionally it will automatically map the DataSet's Columns to the corresponding Stored Procedure's parameters. Notice in the Code below that the only parameter is the modified DataSet. This is the only place in all of the code where it will be directly referenced, in the Update method. Of the five methods below, SubmitChangesToPerformance(), SelectPerformanceCommand(), DeletePerformanceCommand(), InsertPerformanceCommand() and UpdatePerformanceCommand() only SubmitChangesToPerformance()is public and it is the only one to take an argument. What this means is that if this codes is in a DataAccess Layer, then only the SubmitChangesToPerformance() is exposed.

What this means is that after you have done all your modification to your DataSet, inserts, updates and deletes, when submitting the DataSet to your DataAccess Layer, all the rows in the Sql Datbase Table will be modified by executing a single line of code..

MyDataAccess.SubmitChangesToPerformance(MyDataSet);

Before moving on, the SqlDataAdapter's Update Method also works with DataTables. If your DataSet is made up of several related DataTables you can pass each DataTable object to their appropriate Update method.

MyDataAccess.SubmitChangesToPerformance(MyDataSet.Tables["Performance"]);
MyDataAccess.SubmitChangesToPerformers(MyDataSet.Tables["Performers"]);
MyDataAccess.SubmitChangesToConcerts(MyDataSet.Tables["Concerts"]);

To accomplish this we'll use an example of an update procedure, (scroll down)...

public void SubmitChangesToPerformance(DataSet ds) << The DataSet with all its modified
                                                   rows is passed in as the only argument
{
    SqlDataAdapter da = new SqlDataAdapter();   //Create an SqlDataAdapter
 SqlCon.Open();     //Open an SqlConnection so we can...
 SqlTransaction tx = SqlCon.BeginTransaction(); //...attach a transaction to it.

 //Assign the SqlCommand Objects to their 
 //appropriate SqlDataAdapter's counter parts...
 da.SelectCommand = SelectPerformanceCommand();
 da.DeleteCommand = DeletePerformanceCommand();
 da.InsertCommand = InsertPerformanceCommand();
 da.UpdateCommand = UpdatePerformanceCommand();

 //Now assign the Transaction object to the SqlDataAdapter >
 da.DeleteCommand.Transaction = tx;
 da.InsertCommand.Transaction = tx;
 da.UpdateCommand.Transaction = tx;

 try
 {
  da.Update(ds); << This is where all the action take place,           in this one method.
  tx.Commit();
 }
 catch(SqlException SqlEx) //On Sql Exception, roll back and throw an exception
 {
  tx.Rollback();
  throw SqlEx;
 }
 catch(Exception Ex) //On Any other Exception, roll back and throw an exception
 {
  tx.Rollback();
  throw Ex;
 }
 finally
 {
  SqlCon.Close();
 }
}
 

This first step is to set up the Stored Procedures. Nothing unusual here, you simply want an Insert, Update and Delete Stored procedure to correspond with matching Insert, Update and Delete SqlCommand objects you will write. This has the added advantage a insuring that the DataSet's DataTable Column names match the Sql Table's Column Names. ( You can name your columns anything you want, but if you do you will be stuck using the DataTableMapping object to get this system to work. That will involve a lot of ugly code.) So a simple Stored Procedure would be...

CREATE PROCEDURE dbo.sp_PerformUpdate
(
 @PerformID int,
 @CompID int,
 @ConcertHallID int,
 @PerfDate varchar(50),
 @Version varchar(50),
 @Recording varchar(50),
 @Length varchar(50),
 @HandHNotes text,
 @Original_CompID int, << Note the @Original_... Parameters and how they are used.
 @Original_ConcertHallID int,
 @Original_PerfDate varchar(50),
 @Original_Version varchar(50)
)
AS
 SET NOCOUNT OFF;
UPDATE tblPerformance SET CompID = @CompID, ConcertHallID = @ConcertHallID ,
 PerfDate = @PerfDate, Version = @Version, Recording = @Recording, Length = @Length ,
 HandHNotes = @HandHNotes
WHERE PerformID = @PerformID AND CompID = @Original_CompID AND ConcertHallID = @Original_ConcertHallID
 AND PerfDate = @Original_PerfDate AND Version = @Original_Version
GO

Note the four parameters that start with @Original-. This is a optional concurrancy check and takes advantage of the DataSet's DataRow RowState feature. When you update or delete a DataRow in the DataSet the old values are saved with in a DataRow State of 'Original'. You can use that value to perform simple concurrency checks like here, but it is not needed by the Update method.

Now that the Stored Procedure is done, and we have a public SubmitChangesToPerformance() method that evokes the SqlDataAdapter's Update method, there is only one more piece of code to write, a method that returns the SqlCommand object. Think of this method as the 'glue' between the SubmitChangesToPerformance method and the Stored Procedure.

private SqlCommand UpdatePerformanceCommand()
{
 SqlCommand cmd = new SqlCommand("sp_PerformUpdate",SqlCon); //Create an SqlCommand that calls the sproc
 cmd.CommandType = CommandType.StoredProcedure;

 SqlParameterCollection pc = cmd.Parameters;  //Create an SqlParameter Collection
 SqlParameter param;

 //Reference each sproc param with correct Data Type and Row Version
 param = pc.Add("@PerformID",SqlDbType.Int,4,"PerformID"); << The DataDow's Column is also named 'PerformID'.
 param.SourceVersion = DataRowVersion.Current;          << We're asking for the DataDow's 'Current' version here.
 param = pc.Add("@CompID",SqlDbType.Int,4,"CompID");
 param.SourceVersion = DataRowVersion.Current;
 param = pc.Add("@ConcertHallID",SqlDbType.Int,4,"ConcertHallID");
 param.SourceVersion = DataRowVersion.Current;
 param = pc.Add("@PerfDate",SqlDbType.VarChar,50,"PerfDate");
 param.SourceVersion = DataRowVersion.Current;
 param = pc.Add("@Version",SqlDbType.VarChar,50,"Version");
 param.SourceVersion = DataRowVersion.Current;
 param = pc.Add("@Recording",SqlDbType.VarChar,50,"Recording");
 param.SourceVersion = DataRowVersion.Current;
 param = pc.Add("@Length",SqlDbType.VarChar,50,"Length");
 param.SourceVersion = DataRowVersion.Current;
 param = pc.Add("@HandHNotes",SqlDbType.Text,0,"HandHNotes");
 param.SourceVersion = DataRowVersion.Current;

 //Get Data Row's Original Version for the Concurrance Check params
 param = pc.Add("@Original_CompID",SqlDbType.Int,4,"CompID");
 param.SourceVersion = DataRowVersion.Original;       << We're asking for the DataDow's 'Original' version here.
 param = pc.Add("@Original_ConcertHallID",SqlDbType.Int,4,"ConcertHallID");
 param.SourceVersion = DataRowVersion.Original;
 param = pc.Add("@Original_PerfDate",SqlDbType.VarChar,50,"PerfDate");
 param.SourceVersion = DataRowVersion.Original;
 param = pc.Add("@Original_Version",SqlDbType.VarChar,50,"Version");
 param.SourceVersion = DataRowVersion.Original;

 return cmd;  //Return the SqlCommand Object
}

The first part of the method creates an SqlCommand that references the above Stored Procedure, "sp_PerformUpdate". It next creates an SqlParameter Collection where there will be a one to one matching between the Stored Procedure parameters and the DataSet's DatRow columns. Make sure the data types match between the DataSet Columns, Database Columns and the SqlParameter. The first 8 assignemts uses the Data Row's current data. This is the data that is going to update the Table's Columns. The second 4cassignments uses the DataRow's original version for concurrency checking. Implemented in the Stored Procedure's WHERE clause.

To make life easier on yourself, make the DataSet's DataColumn names and the Sql Table Column names and the Stored Procedure Parameter names all the same. This method works best if there is a one-to-one-to-one relationship between the Sql Table Columns, the DataSet's Data Columns and the Stored Procedure's parameters in both name and data types.

That's it! It really works. As the SqlDataAdapter's Update method iterates through the rows of the DataSet, when one has a DataRowState of "Modified" it will evoke UpdatePerformanceCommand(). That method will assign the DataSet's Columns to the Stored Procedure's parameter collection and then evoke the Procedure. That Sql Table's row will be updated with the values from the DataSet. Likewise when the Update method encounters DataRowStates of "Deleted" or "Added" it will evoke DeletePerformanceCommand() or InsertPerformanceCommand() respectively.

For the sake of completeness here is the code for Inserting and Deleting along with a little snipit about SqlDataAdapter's Fill method.

CREATE PROCEDURE dbo.sp_PerformInsert
(
 @CompID int,
 @ConcertHallID int,
 @PerfDate varchar(50),
 @Version varchar(50),
 @Recording varchar(50),
 @Length varchar(50),
 @HandHNotes text
)
AS
 SET NOCOUNT OFF;
INSERT INTO tblPerformance (CompID, ConcertHallID, PerfDate, Version, Recording, Length, HandHNotes)
VALUES (@CompID, @ConcertHallID, @PerfDate, @Version, @Recording, @Length, @HandHNotes)

GO

CREATE PROCEDURE dbo.sp_PerformDelete
(
 @PerformID int,
 @Original_CompID int,
 @Original_ConcertHallID int,
 @Original_PerfDate varchar(50),
 @Original_Version varchar(50)
)
AS
 SET NOCOUNT OFF;
DELETE FROM tblPerformance
WHERE @PerformID = @PerformID AND CompID = @Original_CompID AND ConcertHallID = @Original_ConcertHallID
     AND PerfDate = @Original_PerfDate AND Version = @Original_Version

GO

--------------------------------------------------------------

private SqlCommand InsertPerformanceCommand()
{
 SqlCommand cmd = new SqlCommand("sp_PerformInsert",SqlCon); //Create an SqlCommand that calls the sproc
 cmd.CommandType = CommandType.StoredProcedure;

 SqlParameterCollection pc = cmd.Parameters;  //Create an SqlParameter Collection
 SqlParameter param;

 //Reference each sproc param with correct Data Type and Row Version
 param = pc.Add("@CompID",SqlDbType.Int,4,"CompID");
 param.SourceVersion = DataRowVersion.Current;
 param = pc.Add("@ConcertHallID",SqlDbType.Int,4,"ConcertHallID");
 param.SourceVersion = DataRowVersion.Current;
 param = pc.Add("@PerfDate",SqlDbType.VarChar,50,"PerfDate");
 param.SourceVersion = DataRowVersion.Current;
 param = pc.Add("@Version",SqlDbType.VarChar,50,"Version");
 param.SourceVersion = DataRowVersion.Current;
 param = pc.Add("@Recording",SqlDbType.VarChar,50,"Recording");
 param.SourceVersion = DataRowVersion.Current;
 param = pc.Add("@Length",SqlDbType.VarChar,50,"Length");
 param.SourceVersion = DataRowVersion.Current;
 param = pc.Add("@HandHNotes",SqlDbType.Text,0,"HandHNotes");
 param.SourceVersion = DataRowVersion.Current;

 return cmd;  //Return the SqlCommand Object
}


private SqlCommand DeletePerformanceCommand()
{
 SqlCommand cmd = new SqlCommand("sp_PerformDelete",SqlCon); //Create an SqlCommand that calls the sproc
 cmd.CommandType = CommandType.StoredProcedure;

 SqlParameterCollection pc = cmd.Parameters;  //Create an SqlParameter Collection
 SqlParameter param;

 //Reference each sproc param with correct Data Type and Row Version
 param = pc.Add("@PerformID",SqlDbType.Int,4,"PerformID");
 param.SourceVersion = DataRowVersion.Original;
 param = pc.Add("@Original_CompID",SqlDbType.Int,4,"CompID");
 param.SourceVersion = DataRowVersion.Original;
 param = pc.Add("@Original_ConcertHallID",SqlDbType.Int,4,"ConcertHallID");
 param.SourceVersion = DataRowVersion.Original;
 param = pc.Add("@Original_PerfDate",SqlDbType.VarChar,50,"PerfDate");
 param.SourceVersion = DataRowVersion.Original;
 param = pc.Add("@Original_Version",SqlDbType.VarChar,50,"Version");
 param.SourceVersion = DataRowVersion.Original;

 return cmd;  //Return the SqlCommand Object
}

If you have not used the SqlDataAdapter much it's actually quite simple to use and it packs in a lot of functionality. A very usefull method, besides the Update Method above, is the Fill Method. It will populate a DataSet's DataTable and do a few other usefull things. Here's a quick example...

DataSet MyDataSet = new DataSet();
..........
string strSqlCon = "data source=.........";
string strQuery = "SELECT * FROM tblXyz";
SqlDataAdapter MyAdapter = new SqlDataAdapter(strSqlCon,strQuery);
MyAdapter.Fill(MyDataSet);
.........
.........

Notice there is no SqlConnection or SqlCommand object. Since there is no SqlConnection object there is likewise no Open() or Close() methods. The SqlDataAdapter takes care of all of that. It creates (internally) the Command object, opens a connection to the DataBase, executes the query, populates the DataSet's DataTable then closes the connection. The SqlDataAdapter is heavily overloaded so I recomend you read David Sceppa's book, ADO.NET Core Reference

Much of this information I obtained from David Sceppa's excelent book, ADO.NET Core Reference and my personal experience using this method building the Handel and Haydn On-Line Database.

stored procedure in sql server

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[inseruser]
(
@username varchar(50),
@password varchar(50),
@firstname varchar(50),
@lastname varchar(50),
@city varchar(50)
)
AS
Begin
declare @usercount int
declare @ReturnValue int

select @usercount=count(*) from users where username=@username

if @usercount=0
begin
insert into users (username,password,firstname,lastname,city)
values(@username,@password,@firstname,@lastname,@city)
set @ReturnValue=1
end
else
begin
set @ReturnValue=2
end
return @ReturnValue
End

substring in sql server

The Substring function in SQL is used to grab a portion of the stored data. This function is called differently for the different databases:

MySQL: SUBSTR(), SUBSTRING()
Oracle: SUBSTR()
SQL Server: SUBSTRING()
The most frequent uses are as follows (we will use SUBSTR() here):
SUBSTR(str,pos): Select all characters from <str> starting with position <pos>. Note that this syntax is not supported in SQL Server.

SUBSTR(str,pos,len): Starting with the <pos>th character in string <str> and select the next <len> characters.

Assume we have the following table:

Table Geography region_name store_name
East Boston
East New York
West Los Angeles
West San Diego


Example 1:

SELECT SUBSTR(store_name, 3)
FROM Geography
WHERE store_name = 'Los Angeles';

Result:

's Angeles'

Example 2:

SELECT SUBSTR(store_name,2,4)
FROM Geography
WHERE store_name = 'San Diego';

Result:

'an D'

try catch in stored procedure in sql server

Begin try

---------------


-----
-----
-----
-----

---------------

end try

Begin catch
Select
ERROR_MESSAGE(),
ERROR_NUMBER() ,
ERROR_SEVERITY() ,
ERROR_STATE() ,
ERROR_PROCEDURE() ,
ERROR_LINE()

end Catch

Commit & Rollback in sql server

-------------------------------------------------------------
***********************************************************
-------------------------------------------------------------

A c# using statement wraps up the connection, because SqlConnection implements IDisposable.  The using statement makes sure that Dispose() gets called on the connection object so it can free up any unmanaged resources.
Before you can begin a transaction, you must first open the connection.  You begin your transaction and then assign any newly created command objects to that transaction and perform queries as necessary.
Commit the transaction.If an error occurs, Rollback the transaction in a catch statement to void out any changes and then rethrow the error so that the application can deal with it accordingly.
The connection is properly closed in the finally statement, which gets called no matter what, and any unmanaged resources are disposed when the using statement calls Dispose() on the connection.
Pretty simple solution to a fairly advanced topic.The above template could actually implement a second c# using statement around command, because SqlCommand also implements IDisposable.
I don't know that it is really necessary, however.  More theoretical than probably anything.
I just like to see using statements around anything that implements IDisposable:

-------------------------------------------------------------
***********************************************************
-------------------------------------------------------------

---------------

using (SqlConnection connection =
            new SqlConnection(connectionString))
{
    using (SqlCommand command =
            connection.CreateCommand())
    {
        SqlTransaction transaction = null;
       
        try
        {
            // BeginTransaction() Requires Open Connection
            connection.Open();
           
            transaction = connection.BeginTransaction();
           
            // Assign Transaction to Command
            command.Transaction = transaction;
           
            // Execute 1st Command
            command.CommandText = "Insert ...";
            command.ExecuteNonQuery();
           
            // Execute 2nd Command
            command.CommandText = "Update...";
            command.ExecuteNonQuery();
           
            transaction.Commit();
        }
        catch
        {
            transaction.Rollback();
            throw;
        }
        finally
        {
            connection.Close();
        }
    }
}
--------------------------------------------------------------------

view in sql server

A view is a virtual table. A view consists of rows and columns just like a table. The difference between a view and a table is that views are definitions built on top of other tables (or views), and do not hold data themselves. If data is changing in the underlying table, the same change is reflected in the view. A view can be built on top of a single table or multiple tables. It can also be built on top of another view. In the SQL Create View page, we will see how a view can be built.

ASp.Net Menu with css

<%@ Page Language="C#" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
    <style type="text/css">
        .staticMenuItem
        {
            color:black;
            border:solid 1px black;
            padding:2px 4px;
        }
        .menuHover
        {
            color:white;
            background-color:blue;
        }
        .dynamicMenuItem
        {
            color:black;
            padding:2px 4px;       
        }
        .dynamicMenu
        {
            border:Solid 1px black;
            filter:progid:DXImageTransform.Microsoft.dropshadow(OffX=5, OffY=5, Color='gray', Positive='true')" 
        }
    </style>
    <title>Menu Desktop</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    
    <asp:Menu
        id="Menu1"
        Orientation="Horizontal"
        StaticMenuItemStyle-CssClass="staticMenuItem"
        StaticHoverStyle-CssClass="menuHover"
        DynamicHoverStyle-CssClass="menuHover"
        DynamicMenuItemStyle-CssClass="dynamicMenuItem"
        DynamicMenuStyle-CssClass="dynamicMenu"
        Runat="server">
        <Items>
        <asp:MenuItem 
            Text="File"
            Selectable="false">
            <asp:MenuItem 
                Text="Save" />
            <asp:MenuItem 
                Text="Open" />
        </asp:MenuItem>    
        <asp:MenuItem 
            Text="Format"
            Selectable="false">
            <asp:MenuItem 
                Text="Bold" 
                ImageUrl="MenuImages/Bold.gif" />
            <asp:MenuItem 
                Text="Italic" 
                ImageUrl="MenuImages/Italic.gif" />
            <asp:MenuItem 
                Text="Underline"
                ImageUrl="MenuImages/Underline.gif"
                SeparatorImageUrl="Images/Divider.gif" />
            <asp:MenuItem
                Text="Left Align" 
                ImageUrl="MenuImages/JustifyLeft.gif" />
            <asp:MenuItem
                Text="Center Align" 
                ImageUrl="MenuImages/JustifyCenter.gif" />            
            <asp:MenuItem
                Text="Right Align"
                ImageUrl="MenuImages/JustifyRight.gif" />                
        </asp:MenuItem>    
        </Items>
    </asp:Menu>    
    
    </div>
    </form>
</body>
</html>