Update with Inner Join SQL Server

Problem

 

I have two tables (table1 and table2). I want to set a flag in table1 for
each common row with table2. I

 

 

Solution:

 

Update

iwf_requestdetails

set

LastUpdatedStatus=f.Status_ID

from

iwf_requestdetails i innerjoin [FinalWorkflowDetails1] f

on

i.[FormNo] = f.[FORM_NO]

Sqlserver : Looping through temporary table

CREATE

TABLE #ActiveCustomer

(

 

RowID

intIDENTITY(1, 1),

formno

varchar(50),

adddel

varchar(50),

optionid

varchar(50)

)

DECLARE

@NumberRecords int, @RowCount

int

DECLARE

 

 

@form_no varchar(50),@previousform_no varchar(50),@add_del varchar(50), @option_id varchar(50

)

 

 

 

INSERT

INTO #ActiveCustomer(formno,adddel,optionid

)

 

select form_no, add_del, option_id from dbo.iws_kapiti_optionsorderby form_no

SET

@NumberRecords =

@@ROWCOUNT

 

SET

@RowCount =1

 

 

WHILE

@RowCount <= @NumberRecords

BEGIN

SELECT

@form_no = formno,@add_del = adddel,@option_id = optionid

FROM

#ActiveCustomer

WHERE

RowID = @RowCount

if

@add_del =

‘A’

 

begin

end

if

@add_del =

‘D’

 

begin

end

 

SET @RowCount = @RowCount + 1

END

 

DROP

TABLE#ActiveCustomer

 

 

sqlserver cursor

ALTER

procedure [bouser].[InsertTransfer_delete]

as

declare @form_no asvarchar(50)declare @from_br asvarchar(50)declare @to_br asvarchar(1000)declare @reccount asinteger

 

set @reccount = 0declare iws_transfr_detail_cursor cursorforselect form_no, from_br, to_br from dbo.iws_transfr_detail

 

open iws_transfr_detail_cursorfetchnextfrom iws_transfr_detail_cursor into @form_no, @from_br, @to_brwhile@@FETCH_STATUS= 0Begin

 

UPDATE IWF_RequestDetails SET NewBranchCode = @from_br , OldBranchCode = @to_brwhere FormNo = @form_noset @reccount = @reccount + 1fetchnextfrom iws_transfr_detail_cursor into @form_no, @from_br, @to_br

 

print @reccountEndclose iws_transfr_detail_cursordeallocate iws_transfr_detail_cursor

SqlServer Lessons

INSERT INTO VALUES

The syntax for inserting data into a table one row at a time is as follows:

INSERT INTO “table_name” (“column1″, “column2″, …)
VALUES (“value1″, “value2″, …)

Assuming that we have a table that has the following structure,

Table Store_Information
Column Name Data Type
store_name char(50)
Sales float
Date datetime
and now we wish to insert one additional row into the table representing the sales data for Los Angeles on January 10, 1999. On that day, this store had $900 in sales. We will hence use the following SQL script:

INSERT INTO Store_Information (store_name, Sales, Date)
VALUES (‘Los Angeles’, 900, ‘Jan-10-1999′)
INSERT INTO SELECT

The second type of INSERT INTO allows us to insert multiple rows into a table. Unlike the previous example, where we insert a single row by specifying its values for all columns, we now use a SELECT statement to specify the data that we want to insert into the table. If you are thinking whether this means that you are using information from another table, you are correct. The syntax is as follows:

INSERT INTO “table1″ (“column1″, “column2″, …)
SELECT “column3″, “column4″, …
FROM “table2″

Note that this is the simplest form. The entire statement can easily contain WHERE, GROUP BY, and HAVING clauses, as well as table joins and aliases.

So for example, if we wish to have a table, Store_Information, that collects the sales information for year 1998, and you already know that the source data resides in the Sales_Information table, we’ll type in:

INSERT INTO Store_Information (store_name, Sales, Date)
SELECT store_name, Sales, Date
FROM Sales_Information
WHERE Year(Date) = 1998

Here I have used the SQL Server syntax to extract the year information out of a date. Other relational databases will have different syntax. For example, in Oracle, you will use to_char(date,’yyyy’)=1998.

 ——————

SQL Server: INSERT INTO with SubQuery
October 11, 2008 by decoding

Introduction

This is a short tutorial on how to insert data with a subquery on SQL Server databases.
The reason I am writing about it is that the required syntax is not that obvious!

Sample tables and data

Let’s create two simple tables for this tutorial with the names ‘Customers’ and ‘Orders’.
view sourceprint?
1 CREATE TABLE [dbo].[Customers]( 

2     [id] [int] IDENTITY(1,1) NOT NULL, 

3     [name] [varchar](100) NOT NULL, 

4     [username] [varchar](20) NOT NULL

5 )
view sourceprint?
1 CREATE TABLE [dbo].[Orders]( 

2     [order_id] [int] IDENTITY(1,1) NOT NULL, 

3     [customer_id] [int] NOT NULL, 

4     [order_date] [datetime] NOT NULL

5 )

We are also going to insert a new row on table ‘Customers’:
view sourceprint?
1 INSERT INTO Customers 

2 VALUES (‘John Smith’,'john.smith’)

Inserting data with a subquery

The obvious syntax to insert data into the orders table with a subquery would be:
view sourceprint?
1 INSERT INTO Orders 

2 VALUES ((SELECT id FROM Customers WHERE username = ‘john.smith’), GETDATE())

However if you try the above query, you would end up with the following message:

Subqueries are not allowed in this context. Only scalar expressions are allowed.

The correct syntax to accomplish our task is:
view sourceprint?
1 INSERT INTO Orders 

2 SELECT id, GETDATE() FROM Customers 

3 WHERE username = ‘john.smith’

or if you would like to specify the columns:
view sourceprint?1 INSERT INTO Orders (customer_id, order_date) 

2 SELECT id, GETDATE() FROM Customers 

3 WHERE username = ‘john.smith’

————————————

Following three questions are many time asked on this blog.

How to insert data from one table to another table efficiently?
How to insert data from one table using where condition to anther table?
How can I stop using cursor to move data from one table to another table?

There are two different ways to implement inserting data from one table to another table. I strongly suggest to use either of the method over cursor. Performance of following two methods is far superior over cursor. I prefer to use Method 1 always as I works in all the case.

Method 1 : INSERT INTO SELECT
This method is used when table is already created in the database earlier and data is to be inserted into this table from another table. If columns listed in insert clause and select clause are same, they are are not required to list them. I always list them for readability and scalability purpose.
USE AdventureWorks
GO
—-Create TestTable
CREATE TABLE TestTable (FirstName VARCHAR(100), LastName VARCHAR(100))
—-INSERT INTO TestTable using SELECT
INSERT INTO TestTable (FirstName, LastName)
SELECT FirstName, LastName
FROM Person.Contact
WHERE EmailPromotion = 2
—-Verify that Data in TestTable
SELECT FirstName, LastName
FROM TestTable
—-Clean Up Database
DROP TABLE TestTable
GO

Method 2 : SELECT INTO
This method is used when table is not created earlier and needs to be created when data from one table is to be inserted into newly created table from another table. New table is created with same data types as selected columns.
USE AdventureWorks
GO
—-Create new table and insert into table using SELECT INSERT
SELECT FirstName, LastName
INTO TestTable
FROM Person.Contact
WHERE EmailPromotion = 2
—-Verify that Data in TestTable
SELECT FirstName, LastName
FROM TestTable
—-Clean Up Database
DROP TABLE TestTable
GO

Both of the above method works with database temporary tables (global, local). If you want to insert multiple rows using only one insert statement refer article SQL SERVER – Insert Multiple Records Using One Insert Statement – Use of UNION ALL.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

———————————

sqlserver replace character in position :::::

Using REPLACE in an UPDATE statement

By Bill Graziano on 31 March 2010 | 12 Comments | Tags: UPDATE, Functions

---

This article covers using the REPLACE function to selectively replace text inside a string in SQL Server. The REPLACE function is easy to use and very handy with an UPDATE statment.

Replace searches for certain characters in a string and replaces them with other characters. So this statement:

 

SELECT Replace('SQLTeam.com Rocks!', 'Rocks', 'Rolls')

will return

SQLTeam.com Rolls!

REPLACE searches the the first string for any occurance of the the second string and replaces it with the third string. You can also do replacements of different sizes. For example,

 

SELECT Replace('SQLTeam.com Rocks!', 'Rocks', 'is cool')

gives us

SQLTeam.com is cool!

I replaced a five character string with a seven character string with no problem. If the string isn’t found, no changes will be made.

 

SELECT Replace('SQLTeam.com Rocks!', 'Yak', 'Tibetan bison')

returns exactly what we started with which is

SQLTeam.com Rocks!

If it doesn’t find anything to change it just returns the string unchanged. You can use REPLACE in an UPDATE statement. Using the pubs database we could write:

 

Update dbo.authors
Set    city = replace(city, 'Salt', 'Olympic');

There were two authors that had “Salt Lake City” in the CITY field. Now that field holds “Olympic Lake City” for those two authors. The CITY field is unchanged for all the other authors.

A more common approach is to use this in conjuntion with a WHERE clause like this:

 

UPDATE dbo.authors
SET    city = replace(city, 'Salt', 'Olympic')
WHERE  city LIKE 'Salt%';

This only affects the rows that start with ‘Salt’

—————————

1. CREATE FUNCTION fnReplaceCharAtPos(@Str varchar(8000),@Pos int, @Chr char(1))
2. RETURNS varchar(8000) AS  
3. BEGIN 
4. declare @Res varchar(8000)
5. set @Res=left(@Str,@Pos-1) + @Chr  + right(@Str,len(@Str)-@Pos)
6. return @Res
7. END
8.  

and call it in a query with

Expand|Select|Wrap|Line Numbers

1. select dbo.fnReplaceCharAtPos(str,8,’Z')
2. from YourTable

—————————-

SQL Functions – STUFF() and REPLACE()

by Vishal

STUFF() can be used to stuff a string into another string. It inserts the string at a given position, and deletes the number of characters specified from the original string.

– © 2011 – Vishal (http://SqlAndMe.com)

DECLARE @string1 VARCHAR(20) = ‘Microsoft Server’

DECLARE @string2 VARCHAR(20) = ‘SQL Server 2005′

SELECT      @string1 + ‘ -> ‘ + STUFF(@string1, 11, 0, ‘SQL ‘)

            AS ‘String 1′,

            @string2 + ‘ -> ‘ + STUFF(@string2, 15, 1, ’8 R2′)

            AS ‘String 2′

Result Set:

String 1                                 String 2

—————————————- ————————————-

Microsoft Server -> Microsoft SQL Server SQL Server 2005 -> SQL Server 2008 R2

(1 row(s) affected)

In the first string it inserts ‘SQL ‘ at specified position – 11, the third argument 0 indicated the number of characters to be deleted before inserting the new string.

For second string, we have deleted one (1) character starting from position 15, which deletes ’5′, and then it inserts the new string at position 15 – ’8 R2′.

REPLACE():

REPLACE() replaces all the specified characters with new characters.

DECLARE @string3 VARCHAR(35) = ‘sql 2005, sql 2008, sql 2008 r2′

SELECT @string3, REPLACE(@string3,’sql’,'SQL’)

Result Set:

———————————–      ———————————–

sql 2005, sql 2008, sql 2008 r2   SQL 2005, SQL 2008, SQL 2008 r2

(1 row(s) affected)

However, it is not limited to same number of characters:

DECLARE @string3 VARCHAR(35) = ’2008 R2′

SELECT @string3, REPLACE(@string3,’20′,’SQL Server 2′)

Result Set:

————–       ————————

2008 R2              SQL Server 208 R2

(1 row(s) affected)

Hope This Helps! Cheers!

Reference : Vishal (http://SqlAndMe.com)

———————-

 

Create User defined function and how to call UDF
CREATE FUNCTION fx_SumTwoValues
( @Val1 int, @Val2 int )
RETURNS int
AS
BEGIN
  RETURN (@Val1+@Val2)
ENDThe structure of the CREATE FUNCTION statement is fairly straightforward. You provide an object name (fx_SumTwoValues), input parameters (@Val1 and @Val2), the type of data the function will return () and the statement(s) the function executes are located between the BEGIN…END block. The following SELECT statement calls the function. Note that the two-part name (owner.object_name) is required when calling this function.
SELECT dbo.fx_SumTwoValues(1,2) AS SumOfTwoValues

SumOfTwoValues
————–
3

 

SQL SERVER – Retrieve Last Inserted Identity of Record

http://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record/

SQL SERVER – @@IDENTITY vs SCOPE_IDENTITY() vs IDENT_CURRENT – Retrieve Last Inserted Identity of Record

SELECT @@IDENTITY
It returns the last IDENTITY value produced on a connection, regardless of the table that produced the value, and regardless of the scope of the statement that produced the value.
@@IDENTITY will return the last identity value entered into a table in your current session. While @@IDENTITY is limited to the current session, it is not limited to the current scope. If you have a trigger on a table that causes an identity to be created in another table, you will get the identity that was created last, even if it was the trigger that created it.

SELECT SCOPE_IDENTITY()
It returns the last IDENTITY value produced on a connection and by a statement in the same scope, regardless of the table that produced the value.
SCOPE_IDENTITY(), like @@IDENTITY, will return the last identity value created in the current session, but it will also limit it to your current scope as well. In other words, it will return the last identity value that you explicitly created, rather than any identity that was created by a trigger or a user defined function.

SELECT IDENT_CURRENT(‘tablename’)
It returns the last IDENTITY value produced in a table, regardless of the connection that created the value, and regardless of the scope of the statement that produced the value.
IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the identity value generated for a specific table in any session and any scope.

To avoid the potential problems associated with adding a trigger later on, always use SCOPE_IDENTITY() to return the identity of the recently added row in your T SQL Statement or Stored Procedure.

SQL SERVER – Retrieve Last Inserted Identity of Record

Source :

http://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record/

SELECT @@IDENTITY
It returns the last IDENTITY value produced on a connection, regardless of the table that produced the value, and regardless of the scope of the statement that produced the value.
@@IDENTITY will return the last identity value entered into a table in your current session. While @@IDENTITY is limited to the current session, it is not limited to the current scope. If you have a trigger on a table that causes an identity to be created in another table, you will get the identity that was created last, even if it was the trigger that created it.

SELECT SCOPE_IDENTITY()
It returns the last IDENTITY value produced on a connection and by a statement in the same scope, regardless of the table that produced the value.
SCOPE_IDENTITY(), like @@IDENTITY, will return the last identity value created in the current session, but it will also limit it to your current scope as well. In other words, it will return the last identity value that you explicitly created, rather than any identity that was created by a trigger or a user defined function.

SELECT IDENT_CURRENT(‘tablename’)
It returns the last IDENTITY value produced in a table, regardless of the connection that created the value, and regardless of the scope of the statement that produced the value.
IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the identity value generated for a specific table in any session and any scope.

To avoid the potential problems associated with adding a trigger later on, always use SCOPE_IDENTITY() to return the identity of the recently added row in your T SQL Statement or Stored Procedure.

Sqlserver Self Join

How To Use Self Join In Sql Server 2000 2005

Self Join in SQL Server 2000/2005 helps in retrieving the records having some relation or similarity with other records in the same database table. A common example of employees table can do more clearly about the self join in sql. Self join in sql means joining the single table to itself. It creates the partial view of the single table and retrieves the related records. You can use aliases for the same table to set a self join between the single table and retrieve the records satisfying the condition in where clause.

For self join in sql you can try the following example:

Create table employees:

emp_id	emp_name	emp_manager_id
1	John	Null
2	Tom	1
3	Smith	1
4	Albert	2
5	David	2
6	Murphy	5
7	Petra	5

 

Now to get the names of managers from the above single table you can use sub queries or simply the self join.

Self Join SQL Query to get the names of manager and employees:

select e1.emp_name ‘manager’,e2.emp_name ‘employee’
from employees e1 join employees e2
on e1.emp_id=e2.emp_manager_id

Result:

manager	employee
John	Tom
John	Smith
Tom	Albert
Tom	David
David	Murphy
David	Petra

 

Understanding the Self Join Example

In the above self join query, employees table is joined with itself using table aliases e1 and e2. This creates the two views of a single table.

from employees e1 join employees e2
on e1.emp_id=e2.emp_manager_id

Here e.emp_manager_id passes the manager id from the 2nd view to the first aliased e1 table to get the names of managers.

Sql Help

ctrl  + shift + q -> query editor
acitivity monitor

execution plan:
—-
compute scalar
physical operation
logical operation
estimated i/o cost
estimated cpu cost
numbers of executions
operator cost
subtree cost
number of rows
rows size
ordered
Nodeid

activity monitor:
1. overview (graphical ui0
2. processes :
3. Resource waits
4. Data file i/o
5. Recent expensive queries

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Table : a

id      Project mon      sun     tues   wed     thurs   fri     sat
———————————————————————-
1 Proj1 1 NULL NULL NULL NULL NULL NULL
2 Proj1 NULL 2 NULL NULL NULL NULL NULL
3 Proj1 NULL NULL 3 NULL NULL NULL NULL
4 Proj1 NULL NULL NULL 4 NULL NULL NULL
5 Proj1 NULL NULL NULL NULL 5 NULL NULL
6 Proj1 NULL NULL NULL NULL NULL 6 NULL
7 Proj1 NULL NULL NULL NULL NULL NULL 7
8 Proj1 11 NULL NULL NULL NULL NULL NULL
9 Proj1 NULL 22 NULL NULL NULL NULL NULL
10 Proj1 NULL NULL 33 NULL NULL NULL NULL

select @str from
(select sum(Sun) as [Day 1],sum(Mon) as [Day 2],Sum(Tues) as [Day 3],SUM(Wed) as [Day 4],
 sum(Thur) as [Day 5], Sum(Fri) as [Day 6],Sum(Sat) as [Day 7]
 from a  where id < 8 group by project) ab,
( select sum(Sun) as [Day 8],sum(Mon) as [Day 9],Sum(Tues) as [Day 10],SUM(Wed) as [Day 11],
 sum(Thur) as [Day 12], Sum(Fri) as [Day 13],Sum(Sat) as [Day 14]
  from a  where id > 7 and id < 15 group by project)  cd
 
We need result like this:

Day1  Day2 Day3 Day4 Day5 Day6 Day7 Day8  Day9 Day10 Day11 Day12  Day13  Day14
———————————————————————————-
1 2   3     4    5     6   7     11   22   33   null  null   null    null

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Count the no. of incidents created on each day ie on Sunday , on Monday, On Tuesday …..on Saturday

 SELECT   
   @Sunday_0004 = SUM(CASE WHEN (DATEPART(WEEKDAY, SourceToolCreatedDate) = 1 AND
   ((DATEPART(HOUR, SourceToolCreatedDate) * 60) + DATEPART(MINUTE, SourceToolCreatedDate)) >= 0 AND
   ((DATEPART(HOUR, SourceToolCreatedDate) * 60) + DATEPART(MINUTE, SourceToolCreatedDate)) <= (4 * 60))
   THEN 1 ELSE 0 END),
   @Sunday_0408 = SUM(CASE WHEN (DATEPART(WEEKDAY, SourceToolCreatedDate) = 1 AND
   ((DATEPART(HOUR, SourceToolCreatedDate) * 60) + DATEPART(MINUTE, SourceToolCreatedDate)) > (4 * 60) AND
   ((DATEPART(HOUR, SourceToolCreatedDate) * 60) + DATEPART(MINUTE, SourceToolCreatedDate)) <= (8 * 60))
   THEN 1 ELSE 0 END),
   @Sunday_0812 = SUM(CASE WHEN (DATEPART(WEEKDAY, SourceToolCreatedDate) = 1 AND
   ((DATEPART(HOUR, SourceToolCreatedDate) * 60) + DATEPART(MINUTE, SourceToolCreatedDate)) > (8 * 60) AND
   ((DATEPART(HOUR, SourceToolCreatedDate) * 60) + DATEPART(MINUTE, SourceToolCreatedDate)) <= (12 * 60))
   THEN 1 ELSE 0 END),
   @Sunday_1216 = SUM(CASE WHEN (DATEPART(WEEKDAY, SourceToolCreatedDate) = 1 AND
   ((DATEPART(HOUR, SourceToolCreatedDate) * 60) + DATEPART(MINUTE, SourceToolCreatedDate)) > (12 * 60) AND
   ((DATEPART(HOUR, SourceToolCreatedDate) * 60) + DATEPART(MINUTE, SourceToolCreatedDate)) <= (16 * 60))
   THEN 1 ELSE 0 END),
   @Sunday_1620 = SUM(CASE WHEN (DATEPART(WEEKDAY, SourceToolCreatedDate) = 1 AND
   ((DATEPART(HOUR, SourceToolCreatedDate) * 60) + DATEPART(MINUTE, SourceToolCreatedDate)) > (16 * 60) AND
   ((DATEPART(HOUR, SourceToolCreatedDate) * 60) + DATEPART(MINUTE, SourceToolCreatedDate)) <= (20 * 60))
   THEN 1 ELSE 0 END),
   @Sunday_2024 = SUM(CASE WHEN (DATEPART(WEEKDAY, SourceToolCreatedDate) = 1 AND
   ((DATEPART(HOUR, SourceToolCreatedDate) * 60) + DATEPART(MINUTE, SourceToolCreatedDate)) > (20 * 60) AND
   ((DATEPART(HOUR, SourceToolCreatedDate) * 60) + DATEPART(MINUTE, SourceToolCreatedDate)) <= (24 * 60))
   THEN 1 ELSE 0 END),
  
   @Monday_0004 = SUM(CASE WHEN (DATEPART(WEEKDAY, SourceToolCreatedDate) = 2 AND
   ((DATEPART(HOUR, SourceToolCreatedDate) * 60) + DATEPART(MINUTE, SourceToolCreatedDate)) >= 0 AND
   ((DATEPART(HOUR, SourceToolCreatedDate) * 60) + DATEPART(MINUTE, SourceToolCreatedDate)) <= (4 * 60))
   THEN 1 ELSE 0 END),
   @Monday_0408 = SUM(CASE WHEN (DATEPART(WEEKDAY, SourceToolCreatedDate) = 2 AND
   ((DATEPART(HOUR, SourceToolCreatedDate) * 60) + DATEPART(MINUTE, SourceToolCreatedDate)) > (4 * 60) AND
   ((DATEPART(HOUR, SourceToolCreatedDate) * 60) + DATEPART(MINUTE, SourceToolCreatedDate)) <= (8 * 60))
   THEN 1 ELSE 0 END),
   @Monday_0812 = SUM(CASE WHEN (DATEPART(WEEKDAY, SourceToolCreatedDate) = 2 AND
   ((DATEPART(HOUR, SourceToolCreatedDate) * 60) + DATEPART(MINUTE, SourceToolCreatedDate)) > (8 * 60) AND
   ((DATEPART(HOUR, SourceToolCreatedDate) * 60) + DATEPART(MINUTE, SourceToolCreatedDate)) <= (12 * 60))
   THEN 1 ELSE 0 END),
   @Monday_1216 = SUM(CASE WHEN (DATEPART(WEEKDAY, SourceToolCreatedDate) = 2 AND
   ((DATEPART(HOUR, SourceToolCreatedDate) * 60) + DATEPART(MINUTE, SourceToolCreatedDate)) > (12 * 60) AND
   ((DATEPART(HOUR, SourceToolCreatedDate) * 60) + DATEPART(MINUTE, SourceToolCreatedDate)) <= (16 * 60))
   THEN 1 ELSE 0 END),
   @Monday_1620 = SUM(CASE WHEN (DATEPART(WEEKDAY, SourceToolCreatedDate) = 2 AND
   ((DATEPART(HOUR, SourceToolCreatedDate) * 60) + DATEPART(MINUTE, SourceToolCreatedDate)) > (16 * 60) AND
   ((DATEPART(HOUR, SourceToolCreatedDate) * 60) + DATEPART(MINUTE, SourceToolCreatedDate)) <= (20 * 60))
   THEN 1 ELSE 0 END),
   @Monday_2024 = SUM(CASE WHEN (DATEPART(WEEKDAY, SourceToolCreatedDate) = 2 AND
   ((DATEPART(HOUR, SourceToolCreatedDate) * 60) + DATEPART(MINUTE, SourceToolCreatedDate)) > (20 * 60) AND
   ((DATEPART(HOUR, SourceToolCreatedDate) * 60) + DATEPART(MINUTE, SourceToolCreatedDate)) <= (24 * 60))
   THEN 1 ELSE 0 END),
   
   @Tuesday_0004 = SUM(CASE WHEN (DATEPART(WEEKDAY, SourceToolCreatedDate) = 3 AND
   ((DATEPART(HOUR, SourceToolCreatedDate) * 60) + DATEPART(MINUTE, SourceToolCreatedDate)) >= 0 AND
   ((DATEPART(HOUR, SourceToolCreatedDate) * 60) + DATEPART(MINUTE, SourceToolCreatedDate)) <= (4 * 60))
   THEN 1 ELSE 0 END),
   @Tuesday_0408 = SUM(CASE WHEN (DATEPART(WEEKDAY, SourceToolCreatedDate) = 3 AND
   ((DATEPART(HOUR, SourceToolCreatedDate) * 60) + DATEPART(MINUTE, SourceToolCreatedDate)) > (4 * 60) AND
   ((DATEPART(HOUR, SourceToolCreatedDate) * 60) + DATEPART(MINUTE, SourceToolCreatedDate)) <= (8 * 60))
   THEN 1 ELSE 0 END),
   @Tuesday_0812 = SUM(CASE WHEN (DATEPART(WEEKDAY, SourceToolCreatedDate) = 3 AND
   ((DATEPART(HOUR, SourceToolCreatedDate) * 60) + DATEPART(MINUTE, SourceToolCreatedDate)) > (8 * 60) AND
   ((DATEPART(HOUR, SourceToolCreatedDate) * 60) + DATEPART(MINUTE, SourceToolCreatedDate)) <= (12 * 60))
   THEN 1 ELSE 0 END),
   @Tuesday_1216 = SUM(CASE WHEN (DATEPART(WEEKDAY, SourceToolCreatedDate) = 3 AND
   ((DATEPART(HOUR, SourceToolCreatedDate) * 60) + DATEPART(MINUTE, SourceToolCreatedDate)) > (12 * 60) AND
   ((DATEPART(HOUR, SourceToolCreatedDate) * 60) + DATEPART(MINUTE, SourceToolCreatedDate)) <= (16 * 60))
   THEN 1 ELSE 0 END),
   @Tuesday_1620 = SUM(CASE WHEN (DATEPART(WEEKDAY, SourceToolCreatedDate) = 3 AND
   ((DATEPART(HOUR, SourceToolCreatedDate) * 60) + DATEPART(MINUTE, SourceToolCreatedDate)) > (16 * 60) AND
   ((DATEPART(HOUR, SourceToolCreatedDate) * 60) + DATEPART(MINUTE, SourceToolCreatedDate)) <= (20 * 60))
   THEN 1 ELSE 0 END),
   @Tuesday_2024 = SUM(CASE WHEN (DATEPART(WEEKDAY, SourceToolCreatedDate) = 3 AND
   ((DATEPART(HOUR, SourceToolCreatedDate) * 60) + DATEPART(MINUTE, SourceToolCreatedDate)) > (20 * 60) AND
   ((DATEPART(HOUR, SourceToolCreatedDate) * 60) + DATEPART(MINUTE, SourceToolCreatedDate)) <= (24 * 60))
   THEN 1 ELSE 0 END),
  
   @Wednesday_0004 = SUM(CASE WHEN (DATEPART(WEEKDAY, SourceToolCreatedDate) = 4 AND
   ((DATEPART(HOUR, SourceToolCreatedDate) * 60) + DATEPART(MINUTE, SourceToolCreatedDate)) >= 0 AND
   ((DATEPART(HOUR, SourceToolCreatedDate) * 60) + DATEPART(MINUTE, SourceToolCreatedDate)) <= (4 * 60))
   THEN 1 ELSE 0 END),
   @Wednesday_0408 = SUM(CASE WHEN (DATEPART(WEEKDAY, SourceToolCreatedDate) = 4 AND
   ((DATEPART(HOUR, SourceToolCreatedDate) * 60) + DATEPART(MINUTE, SourceToolCreatedDate)) > (4 * 60) AND
   ((DATEPART(HOUR, SourceToolCreatedDate) * 60) + DATEPART(MINUTE, SourceToolCreatedDate)) <= (8 * 60))
   THEN 1 ELSE 0 END),
   @Wednesday_0812 = SUM(CASE WHEN (DATEPART(WEEKDAY, SourceToolCreatedDate) = 4 AND
   ((DATEPART(HOUR, SourceToolCreatedDate) * 60) + DATEPART(MINUTE, SourceToolCreatedDate)) > (8 * 60) AND
   ((DATEPART(HOUR, SourceToolCreatedDate) * 60) + DATEPART(MINUTE, SourceToolCreatedDate)) <= (12 * 60))
   THEN 1 ELSE 0 END),
   @Wednesday_1216 = SUM(CASE WHEN (DATEPART(WEEKDAY, SourceToolCreatedDate) = 4 AND
   ((DATEPART(HOUR, SourceToolCreatedDate) * 60) + DATEPART(MINUTE, SourceToolCreatedDate)) > (12 * 60) AND
   ((DATEPART(HOUR, SourceToolCreatedDate) * 60) + DATEPART(MINUTE, SourceToolCreatedDate)) <= (16 * 60))
   THEN 1 ELSE 0 END),
   @Wednesday_1620 = SUM(CASE WHEN (DATEPART(WEEKDAY, SourceToolCreatedDate) = 4 AND
   ((DATEPART(HOUR, SourceToolCreatedDate) * 60) + DATEPART(MINUTE, SourceToolCreatedDate)) > (16 * 60) AND
   ((DATEPART(HOUR, SourceToolCreatedDate) * 60) + DATEPART(MINUTE, SourceToolCreatedDate)) <= (20 * 60))
   THEN 1 ELSE 0 END),
   @Wednesday_2024 = SUM(CASE WHEN (DATEPART(WEEKDAY, SourceToolCreatedDate) = 4 AND
   ((DATEPART(HOUR, SourceToolCreatedDate) * 60) + DATEPART(MINUTE, SourceToolCreatedDate)) > (20 * 60) AND
   ((DATEPART(HOUR, SourceToolCreatedDate) * 60) + DATEPART(MINUTE, SourceToolCreatedDate)) <= (24 * 60))
   THEN 1 ELSE 0 END),
  
   @Thursday_0004 = SUM(CASE WHEN (DATEPART(WEEKDAY, SourceToolCreatedDate) = 5 AND
   ((DATEPART(HOUR, SourceToolCreatedDate) * 60) + DATEPART(MINUTE, SourceToolCreatedDate)) >= 0 AND
   ((DATEPART(HOUR, SourceToolCreatedDate) * 60) + DATEPART(MINUTE, SourceToolCreatedDate)) <= (4 * 60))
   THEN 1 ELSE 0 END),
   @Thursday_0408 = SUM(CASE WHEN (DATEPART(WEEKDAY, SourceToolCreatedDate) = 5 AND
   ((DATEPART(HOUR, SourceToolCreatedDate) * 60) + DATEPART(MINUTE, SourceToolCreatedDate)) > (4 * 60) AND
   ((DATEPART(HOUR, SourceToolCreatedDate) * 60) + DATEPART(MINUTE, SourceToolCreatedDate)) <= (8 * 60))
   THEN 1 ELSE 0 END),
   @Thursday_0812 = SUM(CASE WHEN (DATEPART(WEEKDAY, SourceToolCreatedDate) = 5 AND
   ((DATEPART(HOUR, SourceToolCreatedDate) * 60) + DATEPART(MINUTE, SourceToolCreatedDate)) > (8 * 60) AND
   ((DATEPART(HOUR, SourceToolCreatedDate) * 60) + DATEPART(MINUTE, SourceToolCreatedDate)) <= (12 * 60))
   THEN 1 ELSE 0 END),
   @Thursday_1216 = SUM(CASE WHEN (DATEPART(WEEKDAY, SourceToolCreatedDate) = 5 AND
   ((DATEPART(HOUR, SourceToolCreatedDate) * 60) + DATEPART(MINUTE, SourceToolCreatedDate)) > (12 * 60) AND
   ((DATEPART(HOUR, SourceToolCreatedDate) * 60) + DATEPART(MINUTE, SourceToolCreatedDate)) <= (16 * 60))
   THEN 1 ELSE 0 END),
   @Thursday_1620 = SUM(CASE WHEN (DATEPART(WEEKDAY, SourceToolCreatedDate) = 5 AND
   ((DATEPART(HOUR, SourceToolCreatedDate) * 60) + DATEPART(MINUTE, SourceToolCreatedDate)) > (16 * 60) AND
   ((DATEPART(HOUR, SourceToolCreatedDate) * 60) + DATEPART(MINUTE, SourceToolCreatedDate)) <= (20 * 60))
   THEN 1 ELSE 0 END),
   @Thursday_2024 = SUM(CASE WHEN (DATEPART(WEEKDAY, SourceToolCreatedDate) = 5 AND
   ((DATEPART(HOUR, SourceToolCreatedDate) * 60) + DATEPART(MINUTE, SourceToolCreatedDate)) > (20 * 60) AND
   ((DATEPART(HOUR, SourceToolCreatedDate) * 60) + DATEPART(MINUTE, SourceToolCreatedDate)) <= (24 * 60))
   THEN 1 ELSE 0 END), 
  
   @Friday_0004 = SUM(CASE WHEN (DATEPART(WEEKDAY, SourceToolCreatedDate) = 6 AND
   ((DATEPART(HOUR, SourceToolCreatedDate) * 60) + DATEPART(MINUTE, SourceToolCreatedDate)) >= 0 AND
   ((DATEPART(HOUR, SourceToolCreatedDate) * 60) + DATEPART(MINUTE, SourceToolCreatedDate)) <= (4 * 60))
   THEN 1 ELSE 0 END),
   @Friday_0408 = SUM(CASE WHEN (DATEPART(WEEKDAY, SourceToolCreatedDate) = 6 AND
   ((DATEPART(HOUR, SourceToolCreatedDate) * 60) + DATEPART(MINUTE, SourceToolCreatedDate)) > (4 * 60) AND
   ((DATEPART(HOUR, SourceToolCreatedDate) * 60) + DATEPART(MINUTE, SourceToolCreatedDate)) <= (8 * 60))
   THEN 1 ELSE 0 END),
   @Friday_0812 = SUM(CASE WHEN (DATEPART(WEEKDAY, SourceToolCreatedDate) = 6 AND
   ((DATEPART(HOUR, SourceToolCreatedDate) * 60) + DATEPART(MINUTE, SourceToolCreatedDate)) > (8 * 60) AND
   ((DATEPART(HOUR, SourceToolCreatedDate) * 60) + DATEPART(MINUTE, SourceToolCreatedDate)) <= (12 * 60))
   THEN 1 ELSE 0 END),
   @Friday_1216 = SUM(CASE WHEN (DATEPART(WEEKDAY, SourceToolCreatedDate) = 6 AND
   ((DATEPART(HOUR, SourceToolCreatedDate) * 60) + DATEPART(MINUTE, SourceToolCreatedDate)) > (12 * 60) AND
   ((DATEPART(HOUR, SourceToolCreatedDate) * 60) + DATEPART(MINUTE, SourceToolCreatedDate)) <= (16 * 60))
   THEN 1 ELSE 0 END),
   @Friday_1620 = SUM(CASE WHEN (DATEPART(WEEKDAY, SourceToolCreatedDate) = 6 AND
   ((DATEPART(HOUR, SourceToolCreatedDate) * 60) + DATEPART(MINUTE, SourceToolCreatedDate)) > (16 * 60) AND
   ((DATEPART(HOUR, SourceToolCreatedDate) * 60) + DATEPART(MINUTE, SourceToolCreatedDate)) <= (20 * 60))
   THEN 1 ELSE 0 END),
   @Friday_2024 = SUM(CASE WHEN (DATEPART(WEEKDAY, SourceToolCreatedDate) = 6 AND
   ((DATEPART(HOUR, SourceToolCreatedDate) * 60) + DATEPART(MINUTE, SourceToolCreatedDate)) > (20 * 60) AND
   ((DATEPART(HOUR, SourceToolCreatedDate) * 60) + DATEPART(MINUTE, SourceToolCreatedDate)) <= (24 * 60))
   THEN 1 ELSE 0 END),
   
   @Saturday_0004 = SUM(CASE WHEN (DATEPART(WEEKDAY, SourceToolCreatedDate) = 7 AND
   ((DATEPART(HOUR, SourceToolCreatedDate) * 60) + DATEPART(MINUTE, SourceToolCreatedDate)) >= 0 AND
   ((DATEPART(HOUR, SourceToolCreatedDate) * 60) + DATEPART(MINUTE, SourceToolCreatedDate)) <= (4 * 60))
   THEN 1 ELSE 0 END),
   @Saturday_0408 = SUM(CASE WHEN (DATEPART(WEEKDAY, SourceToolCreatedDate) = 7 AND
   ((DATEPART(HOUR, SourceToolCreatedDate) * 60) + DATEPART(MINUTE, SourceToolCreatedDate)) > (4 * 60) AND
   ((DATEPART(HOUR, SourceToolCreatedDate) * 60) + DATEPART(MINUTE, SourceToolCreatedDate)) <= (8 * 60))
   THEN 1 ELSE 0 END),
   @Saturday_0812 = SUM(CASE WHEN (DATEPART(WEEKDAY, SourceToolCreatedDate) = 7 AND
   ((DATEPART(HOUR, SourceToolCreatedDate) * 60) + DATEPART(MINUTE, SourceToolCreatedDate)) > (8 * 60) AND
   ((DATEPART(HOUR, SourceToolCreatedDate) * 60) + DATEPART(MINUTE, SourceToolCreatedDate)) <= (12 * 60))
   THEN 1 ELSE 0 END),
   @Saturday_1216 = SUM(CASE WHEN (DATEPART(WEEKDAY, SourceToolCreatedDate) = 7 AND
   ((DATEPART(HOUR, SourceToolCreatedDate) * 60) + DATEPART(MINUTE, SourceToolCreatedDate)) > (12 * 60) AND
   ((DATEPART(HOUR, SourceToolCreatedDate) * 60) + DATEPART(MINUTE, SourceToolCreatedDate)) <= (16 * 60))
   THEN 1 ELSE 0 END),
   @Saturday_1620 = SUM(CASE WHEN (DATEPART(WEEKDAY, SourceToolCreatedDate) = 7 AND
   ((DATEPART(HOUR, SourceToolCreatedDate) * 60) + DATEPART(MINUTE, SourceToolCreatedDate)) > (16 * 60) AND
   ((DATEPART(HOUR, SourceToolCreatedDate) * 60) + DATEPART(MINUTE, SourceToolCreatedDate)) <= (20 * 60))
   THEN 1 ELSE 0 END),
   @Saturday_2024 = SUM(CASE WHEN (DATEPART(WEEKDAY, SourceToolCreatedDate) = 7 AND
   ((DATEPART(HOUR, SourceToolCreatedDate) * 60) + DATEPART(MINUTE, SourceToolCreatedDate)) > (20 * 60) AND
   ((DATEPART(HOUR, SourceToolCreatedDate) * 60) + DATEPART(MINUTE, SourceToolCreatedDate)) <= (24 * 60))
   THEN 1 ELSE 0 END)
  
   FROM wsiincident WITH (NOLOCK)

=================================================================================================

/////////////////////////////////////////////////////////////////////////////////////////////////////////////

U are having a sp. Internally  u are calling second sp. in second sp u r calling third sp.
Second sp is return a select statement. lly third sp is returning a select statement means a set of records.

In second sp u r calling third sp and u r inserting those third sp returned records into a temporary table.
In first sp u r calling second sp and u r inserting those second sp returned records into a temporary table.

U can make looping of temporary tables.

Error : An INSERT EXEC statement cannot be nested.
Problem explanation:

WELL WE HAVE BEEN TRYING TO AUTOMATE A PROCEDURE OUT HERE,AND WE ARE TRYING TO CONVERT MOST OF THE THINGS INTO PROCEDURES.

BUT WE ARE GETTING A FEW HICCUPS. PLS HELP

THIS IS HOW IT GOES :-

CREATE PROCEDURE MY_PROC1
AS
BEGIN
ST1 ………;
ST2……….;
END
CREATE PROCEDURE MY_PROC2
AS
BEGIN

CREATE TABLE #TMP2
(COL1 DATATYPE
COL2 DATATYPE)

INSERT INTO #TMP2
EXEC MY_PROC1

ST1 ………;
ST2……….;

END

THIS PROCEDURE TOO RUNS WELL ,AFTER TAKING THE DATA FROM THE FIRST PROC IT MANIPUATES THE DATA ACCORDING TO THE CRITERIA SPECIFIED

NO PROBLEM TILL NOW…….

BUT,

CREATE PROCEDURE MY_PROC3
AS
BEGIN

CREATE TABLE #TMP3
(COL1 DATATYPE
COL2 DATATYPE)

INSERT INTO #TMP3
EXEC MY_PROC2

ST1 ………;
ST2……….;

END

THEN IT GIVES AN ERROR AS :-

“An INSERT EXEC statement cannot be nested.”

CAN’T WE , FROM A PROCEDURE CALL A PROCEDURE WHICH CALLS A PROCEDURE……..

WHAT IS THE NESTING LEVEL OF A PROCEDURE ?

IS THERE ANY WAY AROUND IT OR CAN IT BE DONE BY CHANGING SOME SETTINGS ?
Answer:

http://www.sqlservercentral.com/articles/Stored+Procedures/2977/

The Problem
Let us say, we are working on an Inventory Management System. When a transaction (sales order, invoice, receipt of goods, inventory adjustment etc) takes place, we need to update the available inventory of the items affected by the transaction. We already have a stored procedure to save/update each transaction. Each of those stored procedures needs to update the inventory of all the items affected by the current transaction.

Please note that, the word ‘Transaction’ above, does not refer to Database Transactions. They refer to the various Inventory Operations supported by the application.

Since the inventory needs to be updated from different places, it makes sense to move that part of the code to a separate stored procedure. Then this new stored procedure needs to be called from different places from where the inventory is to be updated. So far it looks simple. But the difficult part is to pass the items to be updated.

A TABLE variable would look to be the ideal solution. If we could pass a TABLE variable containing the list of items to be updated, then the complexity can be reduced to a great extend. But SQL Server does not allow to pass a TABLE variable as a parameter to a stored procedure. So what is the next option?

In this article, I am trying to present a solution to the above scenario by using XML as the format to pass a table to a stored procedure. The CALLER can transform the table (Query result) to an XML variable and pass to the stored procedure. The CALLEE can either convert the XML parameter back to a TABLE variable or directly use XQuery on the XML variable.

The Caller
The CALLER should transform the table to an XML variable. The DATA may come from a table or a query. The following example shows how to create an XML variable from the results of a query. 

    1 /*

    2     Let us first create sample table.

    3 */

    4

    5 CREATE TABLE [dbo].[OrderDetails](

    6     [OrderDetailID] [int] IDENTITY(1,1) NOT NULL,

    7     [ItemNumber] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    8     [Qty] [int] NULL

    9 ) ON [PRIMARY]

   10

   11 /*

   12     Populate the sample table with values

   13 */

   14 INSERT INTO OrderDetails(ItemNumber, Qty)

   15     SELECT ‘A001′, 10

   16     UNION SELECT ‘A002′, 20

   17     UNION SELECT ‘A003′, 30

   18 /*

   19     The query below returns the results in XML format.

   20 */

   21

   22 SELECT ItemNumber, Qty FROM OrderDetails FOR XML RAW(‘item’), ROOT(‘items’)

   23

   24 /*

   25 OUTPUT:

   26

   27 <items>

   28   <item ItemNumber=”A001″ Qty=”10″ />

   29   <item ItemNumber=”A002″ Qty=”20″ />

   30   <item ItemNumber=”A003″ Qty=”30″ />

   31 </items>

   32 */
In the article Advanced XML Processing – II, I have presented a few detailed examples which demonstrate the different ways to generate and format query results as XML.
Now, let us assign the resultant XML value to an XML variable.

    1 — Declare the variable

    2 DECLARE @x XML

    3

    4 — store the results of the Query to XML variable

    5 SET @x = (SELECT ItemNumber, Qty FROM OrderDetails FOR XML RAW(‘item’), ROOT(‘items’), TYPE)

    6

    7 — select the values from the XML variable (to make sure that we did it correctly)

    8 SELECT

    9     x.item.value(‘@ItemNumber[1]‘, ‘VARCHAR(20)’) AS ItemNumber,

   10     x.item.value(‘@Qty[1]‘, ‘INT’) AS Qty

   11 FROM @x.nodes(‘//items/item’) AS x(item)

At this stage, we have an XML variable ready, which we could pass to a child procedure/function. The XML variable contains the values that we want the child procedure/function to process/update. The child procedure can either transform the XML variable back to a TABLE or it can directly read the values from the XML variable.

The Callee
So far, we have seen how to create an XML variable from the results of a query. This XML variable can be passed to another stored procedure which can update the inventory data based on the item information passed to the procedure. The simplest way is to create a wrapper view around the XML variable and use it as if it is a table.

Let us create another sample table, Inventory, which will be updated with the information passed through the XML parameter. The following script will create the sample table.

    1 CREATE TABLE [dbo].[Inventory](

    2     [InventoryID] [int] IDENTITY(1,1) NOT NULL,

    3     [ItemNumber] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    4     [Stock] [int] NULL

    5 ) ON [PRIMARY]

    6

    7 INSERT INTO Inventory (ItemNumber, Stock)

    8     SELECT ‘A001′, 0

    9     UNION SELECT ‘A002′, 0

   10     UNION SELECT ‘A003′, 0

The following sample code shows the implementation needed at the side of the ‘callee’.

    1 CREATE PROCEDURE [dbo].[UpdateInventory1]

    2 (

    3     @x XML

    4 )

    5 AS

    6

    7 SET NOCOUNT ON

    8

    9 /*

   10     The code below creates a wrapper view around the XML variable and updates the

   11     “inventory” table with the information.

   12 */

   13

   14 UPDATE Inventory SET

   15     stock = stock + v.Qty

   16 FROM Inventory inv

   17 INNER JOIN (

   18     SELECT

   19         x.item.value(‘@ItemNumber[1]‘,’varchar(20)’) AS ItemNumber,

   20         x.item.value(‘@Qty[1]‘,’INT’) AS Qty

   21     FROM @x.nodes(‘//items/item’) AS x(item)

   22 ) v ON (v.ItemNumber = inv.ItemNumber)

   23

   24 RETURN

Execute
Let us execute the procedure now. Run the following code.

    1 — Declare the variable

    2 DECLARE @x XML

    3

    4 — store the results of the Query to XML variable

    5 SET @x = (SELECT ItemNumber, Qty FROM OrderDetails FOR XML RAW(‘item’), ROOT(‘items’), TYPE)

    6

    7 — execute the stored procedure

    8 EXECUTE UpdateInventory1 @x

    9

   10 — review the results

   11 SELECT * FROM inventory

Updated Procedure
The sample code above, creates a wrapper view around the XML variable. This is a pretty simple and straight-forward approach. You could still access the values as if it is coming from a table/view. The complexity of XML processing is absorbed in the inner view.

The example below, demonstrates another syntax, which updates the table directly from the XML variable.

    1 CREATE PROCEDURE [dbo].[UpdateInventory2]

    2 (

    3     @x XML

    4 )

    5 AS

    6

    7 SET NOCOUNT ON

    8

    9 /*

   10     This version of the stored procedure has a slightly enhanced version of the

   11     TSQL code. This version updates the table directly from the XML variable,

   12     rather than converting the XML data to a view.

   13 */

   14

   15 UPDATE Inventory SET

   16     stock = stock + x.item.value(‘@Qty[1]‘,’INT’)

   17 FROM Inventory inv

   18 INNER JOIN @x.nodes(‘//items/item’) x(item) ON

   19     (x.item.value(‘@ItemNumber[1]‘,’varchar(20)’) = inv.ItemNumber)

   20

   21 RETURN
///////////////////////////////////////////////////////////////////////////////////////////////////////////

If you want dynamic column names, the only way is to use Dynamic SQL

—————————————————–

http://msdn.microsoft.com/en-us/library/ms187928.aspx         cast and convert different formats in sqlserver
————————————————————–

Datatable c# sorting : http://www.blog.shancer.com/post/How-to-sort-a-DataTable.aspx
 private DataTable AlphabeticSort(DataTable dtTable, int sortOrder)   2:  {   3:      DataSet dsSorted = new DataSet();   4:      string columnKey = “TabName”;   5:      string sortDirection = “”;   6:      string sortFormat = “{0} {1}”;   7:      switch(sortOrder)   8:      {   9:          case 0:  10:              sortDirection = “ASC”;  11:              break;  12:          case 1:  13:              sortDirection = “DESC”;  14:              break;  15:          default:  16:              sortDirection = “ASC”;  17:              break;  18:      }  19:      dtTable.DefaultView.Sort = string.Format(sortFormat, columnKey, sortDirection);  20:      return dtTable.DefaultView.Table;  21:      }

————

How to find unused Stored Procedures in SQL Server 2005/2008
http://www.sqlservercentral.com/articles/SQL+Server/69676/

—————
How to write delete with inner join in  a query

1st example :

Delete with table join
http://www.java2s.com/Tutorial/SQLServer/0040__Insert-Delete-Update/Deletewithtablejoin.htm

create table Billings (
4>     BankerID           INTEGER,
5>     BillingNumber      INTEGER,
6>     BillingDate        datetime,
7>     BillingTotal       INTEGER,
8>     TermsID            INTEGER,
9>     BillingDueDate     datetime ,
10>     PaymentTotal       INTEGER,
11>     CreditTotal        INTEGER
12>
13> );
14> GO
1>
2> INSERT INTO Billings VALUES (1, 1, ’2005-01-22′, 165, 1,’2005-04-22′,123,321);
3> GO

(1 rows affected)
1> INSERT INTO Billings VALUES (2, 2, ’2001-02-21′, 165, 1,’2002-02-22′,123,321);
2> GO

2>
3> create table Bankers(
4>    BankerID             Integer,
5>    BankerName           VARCHAR(20),
6>    BankerContactLName   VARCHAR(20),
7>    BankerContactFName   VARCHAR(20),
8>    BankerCity           VARCHAR(20),
9>    BankerState          VARCHAR(20),
10>    BankerZipCode        VARCHAR(20),
11>    BankerPhone          VARCHAR(20)
12> )
13> GO
1>
2> insert into Bankers values (1, ‘ABC Inc.’,'Joe’,'Smith’,'Vancouver’,'BC’,’11111′,’111-111-1111′);
3> GO

(1 rows affected)
1> insert into Bankers values (2, ‘DEF Inc.’,'Red’,'Rice’, ‘New York’, ‘DE’,’22222′,’222-222-2222′);
2> GO

3>
4> DELETE Billings
5> FROM Billings JOIN Bankers
6>     ON Billings.BankerID = Bankers.BankerID
7> WHERE BankerName = ‘Blue Cross’
8> GO

2nd example:

http://www.sqlservercentral.com/Forums/Topic493913-338-1.aspx
DELETE px
FROM #prodextend px
INNER JOIN #product p ON p.din = px.din
AND p.pkgSize = px.pkgSize
INNER JOIN #manu_clients mc ON mc.clientCode = p.clientCode

——————-

sq l tip

right click on sp or table (to generate script) Right click and  select copy to clipboard
—————-

Truncate tables script for a database

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=65341

use [#database_name#]

Set NoCount ON
Declare @tableName varchar(200)
Declare @tableOwner varchar(100)
Declare @skipident varchar(3)
Declare @identInitValue int

set @tableName = ”
set @tableOwner = ”
set @skipident = ‘NO’
set @identInitValue=1

/*
Step 1: Disable all constraints
*/

exec sp_MSforeachtable ‘ALTER TABLE ? NOCHECK CONSTRAINT ALL’
exec sp_MSforeachtable ‘ALTER TABLE ? DISABLE TRIGGER ALL’

/*
Step 2: Delete the data for all child tables & those which has no relations
*/

While exists
(
select T.table_name from INFORMATION_SCHEMA.TABLES T
left outer join INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
on T.table_name=TC.table_name where (TC.constraint_Type =’Foreign Key’
or TC.constraint_Type is NULL) and
T.table_name not in (‘dtproperties’,'sysconstraints’,'syssegments’)
and Table_type=’BASE TABLE’ and T.table_name > @TableName
)
Begin
Select top 1 @tableOwner=T.table_schema,@tableName=T.table_name from INFORMATION_SCHEMA.TABLES T
left outer join INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
on T.table_name=TC.table_name where (TC.constraint_Type =’Foreign Key’
or TC.constraint_Type is NULL) and
T.table_name not in (‘dtproperties’,'sysconstraints’,'syssegments’)
and Table_type=’BASE TABLE’ and T.table_name > @TableName
order by t.table_name
–Delete the table
Exec(‘DELETE FROM ‘+ @tableOwner + ‘.’ + @tableName)

–Reset identity column
If @skipident = ‘NO’
If exists(
SELECT * FROM information_schema.columns
WHERE COLUMNPROPERTY(OBJECT_ID(
QUOTENAME(table_schema)+’.'+QUOTENAME(@tableName)),
column_name,’IsIdentity’)=1
)
begin
set @identInitValue=1
set @identInitValue=IDENT_SEED(@tableOwner + ‘.’ + @tableName)
DBCC CHECKIDENT (@tableName, RESEED, @identInitValue)
end

checkpoint
End

/*
Step 3: Delete the data for all Parent tables
*/

set @TableName=”
set @tableOwner=”

While exists
(
select T.table_name from INFORMATION_SCHEMA.TABLES T
left outer join INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
on T.table_name=TC.table_name where TC.constraint_Type =’Primary Key’
and T.table_name <>’dtproperties’and Table_type=’BASE TABLE’
and T.table_name > @TableName
)
Begin
Select top 1 @tableOwner=T.table_schema,@tableName=T.table_name from INFORMATION_SCHEMA.TABLES T
left outer join INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
on T.table_name=TC.table_name where TC.constraint_Type =’Primary Key’
and T.table_name <>’dtproperties’and Table_type=’BASE TABLE’
and T.table_name > @TableName
order by t.table_name

–Delete the table
Exec(‘DELETE FROM ‘+ @tableOwner + ‘.’ + @tableName)

–Reset identity column
If @skipident = ‘NO’
If exists(
SELECT * FROM information_schema.columns
WHERE COLUMNPROPERTY(OBJECT_ID(
QUOTENAME(table_schema)+’.'+QUOTENAME(@tableName)),
column_name,’IsIdentity’)=1
)
begin
set @identInitValue=1
set @identInitValue=IDENT_SEED(@tableOwner + ‘.’ + @tableName)
DBCC CHECKIDENT (@tableName, RESEED, @identInitValue)
end

checkpoint

End

/*
Step 4: Enable all constraints
*/

exec sp_MSforeachtable ‘ALTER TABLE ? CHECK CONSTRAINT ALL’
exec sp_MSforeachtable ‘ALTER TABLE ? ENABLE TRIGGER ALL’

Set NoCount Off
————————————

How To: Reset Identity column in SQL Server
This is one of those simple tip posts that may seem obvious and taken for granted by those of us who have been working with SQL Server for a while now but maybe a newbie or two out there will find this helpful.

Every so often (just this morning!) I find myself resetting an identity column value back to 0 after I’ve deleted all the existing records so the table gets a fresh start at primary key 1. Yes, I know all about primary keys not changing and how the value in the primary key doesn’t matter and so on. Sometimes I just like the primary keys starting at 1.

The following line resets the Identity value for the Customer table to 0 so that the next record added starts at 1.

DBCC CHECKIDENT(‘Customer’, RESEED, 0)

Have a day.

————–

Notice the “for xml auto” part of our query above? This determines the way in which SQL Server 2000 shapes our data. There are three shaping methods:
“for xml auto”: Returns XML elements that are nested, based on which tables are listed in the “from” part of the query, and which fields are listed in the “select” part.
“for xml raw”: Returns XML elements with the “row” prefix (ex: “<row tProduct …>”). Each column in a table is represented as an attribute and null column values aren’t included.
“for xml explicit”: Explicit mode is the most complex shaping method used in SQL Server 2000. It allows users to query a data source in such a way that the names and values of the returned XML are specified before the query batch is executed.
http://www.sqlservercentral.com/articles/SS2K5+-+XML/3022/
http://www.mssqltips.com/tip.asp?tip=1077
http://msdn.microsoft.com/en-us/library/bb510462(v=SQL.105).aspx
http://msdn.microsoft.com/en-us/library/bb522647.aspx
http://www.simple-talk.com/sql/t-sql-programming/beginning-sql-server-2005-xml-programming/
http://blah.winsmarts.com/2007-2-Bak2Basics__Learn_T-SQL_-_FOR_XML.aspx
http://www.15seconds.com/issue/001102.htm

Clause
 Description
 
XML
 When specified, the results of a query are returned as an XML document. One of the three options, from RAW, AUTO and EXPLICIT, must be specified
 
RAW
 Transforms each row in the result set into an XML element with a generic identifier <row /> as the element tag
 
AUTO
 Returns query results in a simple, nested XML tree. For each table in the FROM clause, for which there is at least one column listed in the SELECT clause, an XML element is created. The columns are mapped to the appropriate element attributes
 
EXPLICIT
 Specifies the shape of the resulting XML tree explicitly. Using this mode, queries must be written in a particular way so that additional information about the nesting is specified
 
XMLDATA
 Returns the schema, but does not add the root element to the result
 
ELEMENTS
 Specifies that the columns are returned as sub-elements to the table element. Otherwise, they are mapped as attributes
 
BINARY BASE64
 Specifies that the query returns the binary data in binary base64-encoded format
 
eg:
select AddressOfInstitution,NameOfInstitution
        ,AffiliatedUniversity,Qualification
        ,Percentage,MarksOrGrade,YearOfPassing
        ,PeriodFrom,PeriodTo
        ,Specialization,FathersName
        ,MothersName,DOB
        ,RespondentName,RespondentDesignation
        ,RespondentContactNo,RespondentEmail
        ,TypeOfRevert,VerificationDate
        from App_VCaseComponentV_EducationCheck
        where   islatest=’Y’ and component_iid in (
        select component_iid from app_vcasecomponent
        where verificationchecktype_iid = 2 and vcase_iid =1)

        for xml auto, ROOT(‘items’),elements XSINIL
———————-
SELECT currentversionNo,
    ROW_NUMBER() OVER (ORDER BY Component_IID) AS ‘RowNumber’
from app_vcasecomponent
—————————-
SELECT 1 as Tag,
NULL as Parent,
 s.stor_id as [store!1!Id],
 s.stor_name  as [store!1!Name],
 NULL  as[sale!2!OrderNo],
 NULL  as [sale!2!Qty]
 FROM stores s

UNION ALL

– The Sale Data
SELECT 2, 1,
 s.stor_id,
 s.stor_name,
 sa.ord_num,
 sa.qty
FROM stores s, sales sa

WHERE s.stor_id = sa.stor_id
ORDER BY [store!1!name]

FOR XML EXPLICIT
-=———————
executescalar  returns first integer column
executenonquery
I want to return a datatable :
use adapter and fill method and executenonquery

I want to return a integer value from frontend
use return convert.toint(cmd.ExecuteScalar());
I want to return two or more values from backend and capturing in front end
use adapter and fill and executenonquery
or
best sol: use output parameters for backend(u can have n number of output parameters in sp) and in front end use parameter.outputdirection

 prms[7] = new SqlParameter(“@ReturnOperation”, SqlDbType.Int);
                prms[7].Direction = ParameterDirection.Output;

                dHandler.ExecuteNonQuery(storedProcedure, QueryType.StoredProcedure, prms);

                wsiAgingReport.ReturnOperation = Convert.ToInt32(prms[7].Value);

                int retVal = Convert.ToInt32(prms[6].Value);

—————————————————

SqlServer Notes

The security validation for this page is invalid. Click Back in your Web browser, refresh the page, and try your operation again.
—–

operation is not valid due to the current state of the object=

——
http://snahta.blogspot.com/2009/04/unable-to-evaluate-expression-because.html

Scenario:
While using RunWithElevatedPrivileges you got following error.

Error : Unable to evaluate expression because the code is optimized or a native frame is on top of the call stack when use new spsite

Solution:
SPSite and SPWeb object should be created inside delegate(), example below. If not you might get the above error.

Wrong Code:

view plaincopy to clipboardprint?
string siteUrl = @”http://localhost“; 
SPSite site = null ;  
SPWeb web = null ; 
SPSecurity.RunWithElevatedPrivileges(delegate() 
{ 
    site = new SPSite(siteUrl); 
    web = SPSDestSite.OpenWeb(); 
     
    // To do ( run code here ) 
     
}); 
web.Close(); 
site.Close(); 

string siteUrl = @”http://localhost“;
SPSite site = null ;
SPWeb web = null ;
SPSecurity.RunWithElevatedPrivileges(delegate()
{
    site = new SPSite(siteUrl);
    web = SPSDestSite.OpenWeb();
   
    // To do ( run code here )
   
});
web.Close();
site.Close();Correct Code:view plaincopy to clipboardprint?
string siteUrl = @”http://localhost“; 
SPSecurity.RunWithElevatedPrivileges(delegate() 
{ 
     using (SPSite site = new SPSite(siteUrl)) 
     { 
          using (SPWeb web = SPSDestSite.OpenWeb()) 
         { 
              // To do ( run code here ) 
          } 
      } 
}); 

————
export import doc library:
http://www.law.com/jsp/lawtechnologynews/PubArticleLTN.jsp?id=1202443237483
http://www.dev4side.com/community/blog/2010/6/13/sharepoint-api-for-content-migration-and-deployment.aspx
http://vspug.com/dez/2007/11/30/moving-copying-documents-between-libraries-with-metadata-including-version-history/
http://social.msdn.microsoft.com/Forums/en/sharepointserverknowledgenetwork/thread/c5756bc0-75c1-4d84-9f62-3b0e8d5456a5
———-

{“The security validation for this page is invalid. Click Back in your Web browser, refresh the page, and try your operation again.”}

http://ktskumar.wordpress.com/2009/03/09/the-security-validation-for-this-page-is-invalid-click-back-in-your-web-browser-refresh-the-page-and-try-your-operation-again/

———-

http://www.devexpertise.com/2009/02/04/deleting-list-items-in-a-sharepoint-list/
Deleting a item from a sp doc library
Deleting List Items in a SharePoint List
Posted by DevExpert on February 4th, 2009

Occasionally you may encounter a need to delete list items from a SharePoint list.  This sounds like an easy enough task – just loop through the SPList’s Items collection and delete the item.  That would be the intuitive way, but then again this is the SharePoint Object Model.  Let’s take a look a bunch of ways you may try to accomplish this task, and a single way which actually works.

To run these tests, I created a simple custom list named Widgets that contains 10 items:

Incorrect Method #1
The first approach that is probably taken is to loop through the list items with a For..Each loop, and call the Delete() method of each list item, like the following:

using (SPSite siteCollection = new SPSite(“http://server“)) {
    using (SPWeb site = siteCollection.OpenWeb()) {
        SPList list = site.Lists["Widgets"];

        foreach (SPListItem item in list.Items) {
            item.Delete();
        }
    }
}
However, whenever you run this you encounter the following error:

Collection was modified; enumeration operation may not execute.

To help explain this, it will help to first understand the IEnumerable interface, from with the SPListItemCollection ultimately implements. Unfortunately the MSDN documentation is a little scarce on modifying collections with this interface, but the IEnumerator interface contains a lot of good information that applies to IEnumerable as well. 

Basically, enumerators can be used to read the data in the collection, but they cannot be used to modify the underlying collection.  An enumerator remains valid as long as the collection remains unchanged. If changes are made to the collection (such as deleting), the enumerator is irrecoverably invalidated.
Incorrect Method #2

Ok, learning from the mistake in method 1, it makes sense to eliminate the For..Each loop and use a traditional For loop. This method uses an index which increments at each loop, and we call the Delete(index) method of the list item which accepts an item index:

for (int i = 0; i < list.Items.Count; i++) {
    Console.WriteLine(“Deleting {0}”, list.Items[i].Title);
    list.Items.Delete(i);
}
To visualize the results, I’m outputting the item it’s deleting so we can see if everything is working as it should:

What the heck is happening here?  Why is it deleting every other item?  Well, every time you delete an item from the Items collection, the number of items in that collection decreases and thus the indexes no longer match up.
Incorrect Method #3

Hmm, OK…if it’s deleting every other item because the collection is adjusted when items are deleted from it, let’s just be safe and always try to delete the first item at index 0:

for (int i = 0; i < list.Items.Count; i++) {
    Console.WriteLine(“Deleting {0}”, list.Items[0].Title);
    list.Items.Delete(0);
}
The result is exactly like Method #2 – it deletes every other item:
Incorrect Method #4

For kicks and giggles, what if the code is adjusted to delete the item at index 0 inside a For..Each loop?:

foreach (SPListItem item in list.Items) {
    Console.WriteLine(“Deleting {0}”, list.Items[0].Title);
    list.Items.Delete(0);
}
Woah, that worked! As you can see, it deleted every item:

But wait… What if we need to delete an item only if a condition is met?  Let’s try to loop through the collection and only delete the item whose title is Sample Item #5:

foreach (SPListItem item in list.Items) {
    if (item.Title == “Sample Item 5″) {
        Console.WriteLine(“Deleting {0}”, list.Items[0].Title);
        list.Items.Delete(0);
    }
}
The result is what you should expect after deleting the first item in the index – it deletes the first item, not the item that met the condition:
Correct Method

Ok, finally here’s the correct method for deleting list items.  The trick is to use a decrementing For loop.  The For loop in the following example counts downward (i–) instead of upward (i++), because items are being deleted and the number of items decreases with each increment:

for (int i = list.Items.Count – 1; i >= 0; i–) {
    list.Items.Delete(i);
}
Which produces:

What about deleting an item based on a condition?:

for (int i = list.Items.Count – 1; i >= 0; i–) {
    if (list.Items[i].Title == “Sample Item 5″) {
        Console.WriteLine(“Deleting {0}”, list.Items[i].Title);
        list.Items.Delete(i);
    }
}
It works!:

Hopefully you will always remember to delete list items (actually, the same goes for most other SharePoint collections too, such as SPFieldCollection, SPWebCollection, etc.) using a decrementing counter.  Happy coding!
——————

There are six type of join in SQL 20001) INNER   JOIN2) OUTER   JOIN3) CROSS   JOIN4) EQUI    JOIN5) NATURAL JOIN6) SELF    JOIN

1) INNER JOIN :-

PRODUCESS THE RESULT SET OF MATCHING ROWS                 ONLY FROM THE SPECIFIED TABLES.EXAMPLE—SELECT COLUMN_LIST FROM 1ST_TABLE_NAME JOIN 2ND_TABLE_NAMEON 1ST_TABLE_NAME.MATCING_COLUMN=2ND_TABLE_NAME.MATCING_COLUMN2)

OUTER JOIN :-

DISPLAY ALL THE ROWS FROM THE FIRST TABLE                 AND MATCHING ROWS FROM THE SECOND TABLE.EXAMPLE—SELECT COLUMN_LIST FROM 1ST_TABLE_NAME OUTER JOIN 2ND_TABLE_NAMEON 1ST_TABLE_NAME.MATCING_COLUMN=2ND_TABLE_NAME.MATCING_COLUMN   THERE ARE THREE TYPES OF OUTER JOIN:A)LEFT  OUTER JOIN.B)RIGHT OUTER JOIN.

C)FULL  OUTER JOIN

A)LFET OUTER JOIN :- DISPLAYS ALL THE ROWS FROM THE FIRST                        TABLE AND MATCHING ROWS FROM THE                     SECOND TABLE.EXAMPLE—SELECT COLUMN_LIST FROM 1ST_TABLE_NAME LEFT OUTER JOIN 2ND_TABLE_NAME      ON 1ST_TABLE_NAME.MATCING_COLUMN=2ND_TABLE_NAME.MATCING_COLUMNA)

RIGHT OUTER JOIN :- DISPLAYS ALL THE ROWS FROM THE                        SECOND TABLE AND MATCHING ROWS FROM                         THE FIRST TABLE.EXAMPLE—SELECT COLUMN_LIST FROM 1ST_TABLE_NAME RIGHT OUTER JOIN 2ND_TABLE_NAME        ON 1ST_TABLE_NAME.MATCING_COLUMN=2ND_TABLE_NAME.MATCING_COLUMNA)FULL OUTER JOIN :- DISPLAYS ALL MATCHING AND NONMATCHING                     ROWS  OF BOTH THE TABLES.EXAMPLE—SELECT COLUMN_LIST  FROM 1ST_TABLE_NAME FULL OUTER JOIN 2ND_TABLE_NAME     ON 1ST_TABLE_NAME.MATCING_COLUMN=2ND_TABLE_NAME.MATCING_COLUMN3)

CROSS JOIN :- IN THIS TYPE OF JOIN, EACH ROWS FROM THE                   JOIN WITH EACH ROWS FROM THE SECOND TABLE                WITHOUT ANY CONDTION.                ALSO CALLED AS CARTESIAN PRODUCT.EXAMPLE—SELECT COLUMN_LIST FROM 1ST_TABLE_NAME CROSS JOIN 2ND_TABLE_NAME

4) EQUI JOIN :- DISPLAYS ALL THE MATHCING ROWS FROM JOINED                  TABLE. AND ALSO DISPLAYS REDUNDANT VALUES.                IN THIS WE USE * SIGN TO JOIN THE TABLE.EXAMPLE—SELECT * FROM 1ST_TABLE_NAME JOIN 2ND_TABLE_NAMEON 1ST_TABLE_NAME.MATCING_COLUMN=2ND_TABLE_NAME.MATCING_COLUMN 

5)NATURAL JOIN :- DISPLAYS ALL THE MATHCING ROWS FROM                       JOINED  TABLE.IT RESTRICT                                REDUNDANT VALUES.6)SELF JOIN :- IN THIS TABLE JOIN WITH ITSELF WITH                 DIFFERENT ALIAS NAME. ASSUME DEPARTMENT IS A TABLE:SELECT A.DEP_NAME,B.MANAGER_ID(COLUMN LIST) FROM DEPARTMENT A JOIN DEPARTMENT BON A.MANAGER_ID=B.MANAGER_ID
——————-
1…What are the default databases that comes with SP.?
Portalname_SITE
Portalname_PROF
Portalname_SERV
 and a config db..

2… How to make two  webparts communicate?
If it is ur custom web part then the code has to implement ICellConsumer,ICellProvider /  IRowConsumer,IRowProvider etc interfaces.

If it is SP provided web parts then u have the options enabled if the web parts are eligible to be connected

3.  How many ways can a webpart be created?
like asp.net webpart,,,,sharepoint provided webpart
There r only 2 basic distinction

1. WP provided by SP.
2. Custom WP created by you.

4..where these webparts are created …Is it in Asp.net or ..

where these methods CreateChild,RenderChild,.,,,comes into picture

U can create WP using any .Net lang….The methods listed are used to create controls necessary for display (Create….) ….and render it  into HTML tags (Render….)…

5..How to get the details from database to populate a treeview webpart???

I haven’t done any thing of that sort….if u rn’t very keen on db u can even choose SPObject model

what r the tools that are present in sharepoint toolbox?
Forms Authentication:
http://weblog.vb-tech.com/nick/archive/2006/06/14/1617.aspx

Overall Features:
http://office.microsoft.com/en-us/sharepointserver/default.aspx
http://www.readify.net/uncovering+sharepoint+2007.aspx
Reports Center:

http://www.microsoft.com/technet/community/events/moss2007/dsk-100.mspx
http://technet2.microsoft.com/Office/en-us/library/fbe7fe46-8d60-4de2-a699-7921c13dbdc91033.mspx?mfr=true
KPIs:

http://office.microsoft.com/en-us/sharepointserver/HA100800271033.aspx?pid=CH101785431033

http://management.about.com/cs/generalmanagement/a/keyperfindic.htm
http://jopx.blogspot.com/2006/08/report-center-and-kpis-in-moss-2007.html
http://blogs.3sharp.com/Blog/davidg/archive/2006/07/05/1529.aspx

MySite:

http://office.microsoft.com/en-us/sharepointserver/CH100964111033.aspx

http://office.microsoft.com/search/redir.aspx?assetid=CH100964111033&QueryID=hsOAGEQTn0&respos=24&rt=2

BI Features:

http://office.microsoft.com/en-us/sharepointserver/HA100872181033.aspx
Screen Casts:

http://betterecm.wordpress.com/2006/10/25/moss-2007-videos-screencasts/
http://betterecm.wordpress.com/sharepoint-2007-stuff/

RSS Feeds:

http://office.microsoft.com/en-us/sharepointserver/HA100214251033.aspx
Blogs:

http://office.microsoft.com/en-us/sharepointserver/CH101788371033.aspx
WorkFlows:

http://office.microsoft.com/en-us/sharepointserver/CH101782961033.aspx
http://office.microsoft.com/en-us/sharepointserver/CH101248821033.aspx

Dotnet Notes

Launch disk cleanup tool from command line
c:> cleanmgr
=============
Below diagram shows the lifecycle during the initial load of a page, and how ViewState is involved during a PostBack.

Initial Load: InitàLoadàPreRenderàSaveViewStateàRenderàDispose

In Init, controls are created and added to the control tree, in PreRender “CreateChildControls” is executed and controls are prepared for render, In SaveViewState controls save current states, in Render controls render themselves and finally in Dispose controls and Page are disposed.

Controls on a page take the opportunity to save their data to the ViewState state bag just before they render. Another diagram shown below shows how pages and controls use the ViewState data carried along and returned during a PostBack to synthesize events for the page developer’s use.

PostBack: InitàLoadViewStateàLoadàPostBack dataàPostBack eventsàPreRenderàSaveViewStateàRenderàDispose
============

Ramanujam number 1729 (1 cube+ 12 cube, 9 cube +10 cube, 7x13x19 all odds) .
====================

In ASP.Net  TextBox Control

When your TextMode is set to “MultiLine”, the MaxLenght property has no affect. You’ll have
to use a JavaScript function or RegularExpressionValidator instead.
<asp:RegularExpressionValidator ID=”rgConclusionValidator2″
                                ControlToValidate=”txtSpecialInstruction” ErrorMessage=”Special instruction can’t exceed 200 characters”
                                ValidationExpression=”^[\s\S]{0,200}$” runat=”server” Display=”None”  SetFocusOnError=”true” />

————————–

SELECT TOP 97 PERCENT DT.*
FROM
(    SELECT TOP 97 PERCENT *
    FROM Products
    ORDER BY UnitPrice ASC
) AS DT
ORDER BY UnitPrice DESC

Out of 80 records if you want top 20 records then u can use query like
select top 25 percent * from table
—————-
 
order by and union problem

Posted – 07/01/2008 :  09:42:35    
——————————————————————————–
 

I HAVE CREATED A QUERY LIKE BELOW AND FOUND ERROR
Incorrect syntax near the keyword ‘union’.

SELECT TOP 10 PERCENT ID FROM wsitimesheetdetails
ORDER BY ID ASC
UNION
SELECT TOP 30 PERCENT ID FROM wsitimesheetdetails  ORDER BY ID ASC

error is syntax error at union

 
Then Solution is :

select * from(

SELECT TOP 10 PERCENT ID FROM wsitimesheetdetails
ORDER BY ID ASC) a

UNION
select * from(
SELECT TOP 30 PERCENT ID FROM wsitimesheetdetails  ORDER BY ID ASC)b

=============================================================

Problem
I’m designing a table and I’ve decided to create an auto-generated primary key value as opposed to creating my own scheme or using natural keys. I see that SQL Server offers globally unique identifiers (GUIDs) as well as identities to create these values. What are the pros and cons of these approaches?

Solution
Yes, there are a number of ways you can auto-generate key values for your tables. The most common ways are via the use of the IDENTITY column property or by specifying a uniqueidentifier (GUID) data type along with defaulting with either the NEWID() or NEWSEQUENTIALID() function. Futhermore, GUIDs are heavily used in SQL Server Replication to uniquely identify rows in Merge Replication or Transactional Replication with updating subscriptions.

The most common, well known way to auto-generate a key value is via the use of the IDENTITY column property on a column that’s typically declared as an integer. Once defined, the engine will automatically generate a sequential number based on the way the property has been declared on the column. The IDENTITY property takes an initial seed value as its first parameter and an increment vaAnother way to auto-generate key values is to specify your column as a type of uniqueidentifier and DEFAULT using NEWID() or NEWSEQUENTIALID(). Unlike IDENTITY, a DEFAULT constraint must be used to assign a GUID value to the column.

How do NEWID() and NEWSEQUENTIALID() differ? NEWID() randomly generates a guaranteed unique value based on the identification number of the server’s network card plus a unique number from the CPU clock. In contrast, NEWSEQUENTIALID() generates these values in sequential order as opposed to randomly.

Let’s create new tables that use a uniqueidentifier along with both NEWID() and NEWSEQUENTIALID()
lue as its second
As you can see, the first table which uses NEWID() generates random values while the second table that uses NEWSEQUENTIALID() generates sequential values. As opposed to the integers generated by the IDENTITY approach, the GUID values generated are not as friendly to look at or work with. There is one other item to note. SQL Server keeps the last generated identity value in memory which can be retrieved right after an INSERT using SCOPE_IDENTITY(), @@IDENTITY, or CHECK_IDENT (depending on the scope you require). There is nothing similar to capture the last generated GUID value. If you use a GUID, you’ll have to create your own mechanism to capture the last inserted value (i.e. retrieve the GUID prior to insertion or use the SQL Server 2005 OUTPUT clause).

Now that we understand how to auto generate key values and what they look like, let’s examine the storage impacts of each approach. As part of the previously created table definitions, I added a column of CHAR(2000) to mimic the storage of additional column data. Let’s examine the physical storage of the data:

Looking at this output, you can see that the NEWID() test table is very fragmented as evidenced by its fragmentation percentage of 98%. Furthermore, you can see that the rows were dispersed among 490 pages. This is due to the page splitting that occurred due to the random nature of the key generation. In contrast, the IDENTITY and NEWSEQUENTIALID() test tables show minimal fragmentation since their auto generated keys occur in sequential order. As a result, they don’t suffer from the page splitting condition that plagues the NEWID() approach. Though you can defragment the NEWID() table, the random nature of the key generation will still cause page splitting and fragmentation with all future table INSERTs. However, page splitting can be minimized by specifying an appropriate FILL FACTOR.

Looking at the NEWSEQUENTIALID() test table, we see it generated fewer pages than the NEWID() approach but it still generated more pages than the IDENTITY approach. Why is this? It’s because the uniqueidentifier data type consumes 16 bytes of disk space as opposed to the 4 bytes used by the integer data type that was used for the IDENTITY. Considering that SQL Server pages are generally capped at 8K or roughly 8060 bytes (as of SQL Server 2005, there is a row-overflow mechanism that can kick in but that’s for another discussion), this leads to more pages generated for the NEWSEQUENTIALID() approach as opposed to the IDENTITY approach.

Now also consider this, since a uniqueidentifier data type consumes 16 bytes of data, the size of any defined non-clustered indexes on a table using a GUID as a clustered index are also affected because the leaf level of these non-clustered indexes contains the clustered index key as a pointer. As a result, the size of any non-clustered indexes would end up being larger than if an IDENTITY were defined as integer or bigint.

It’s evident that using IDENTITY to auto-generate key values offers a few advantages over the GUID approaches:

IDENTITY generated values are configurable, easy to read, and easier to work with
Fewer database pages are required to satisfy query requests
In comparison to NEWID(), page splitting (and its associated overhead) is eliminated
Database size is minimized
System functions exist to capture the last generated IDENTITY value (i.e. SCOPE_IDENTITY(), etc)
Some system functions – such as MIN() and MAX(), for instance – cannot be used on uniqueidentifier columns

Next Steps

Read more about NEWSEQUENTIALID() in the SQL Server 2005 Books Online
Read Using uniqueidentifier Data in the SQL Server 2005 Books Online
If you’re not in a situation where you require a globally unique value, consider if an IDENTITY makes sense for auto-generating your key values.
Regardless if you decide on a GUID or IDENTITY, consider adding a meaningful UNIQUE key based on the real data in your table.
REfer : http://www.mssqltips.com/tip.asp?tip=1600

——————————————–=========================================
http://blog.sqlauthority.com/2007/06/22/sql-server-explanation-and-comparison-of-nullif-and-isnull/
Explanation of NULLIF
Syntax:
NULLIF ( expression , expression )

Returns a null value if the two specified expressions are equal. NULLIF returns the first expression if the two expressions are not equal. If the expressions are equal, NULLIF returns a null value of the type of the first expression. NULLIF is equivalent to a searched CASE function in which the two expressions are equal and the resulting expression is NULL.

Following is good example of NULLIF and CASE from BOL:
USE AdventureWorks;
GO
SELECT ProductID, MakeFlag, FinishedGoodsFlag,
NULLIF(MakeFlag,FinishedGoodsFlag)AS ‘Null if Equal’
FROM Production.Product
WHERE ProductID < 10;
GO
SELECT ProductID, MakeFlag, FinishedGoodsFlag,’Null if Equal’ =
CASE
WHEN MakeFlag = FinishedGoodsFlag THEN NULL
ELSE MakeFlag
END
FROM Production.Product
WHERE ProductID < 10;
GO

Explanation of ISNULL
Syntax:
ISNULL ( check_expression , replacement_value )

Replaces NULL with the specified replacement value. The value of check_expression is returned if it is not NULL; otherwise, replacement_value is returned after it is implicitly converted to the type of check_expression, if the types are different.

Following is good example of ISNULL from BOL:
USE AdventureWorks;
GO
SELECT AVG(ISNULL(Weight, 50))
FROM Production.Product;
GO

Observation:
Interesting observation is NULLIF returns null if it comparison is successful, where as ISNULL returns not null if its comparison is successful. In one way they are opposite to each other.
———————————-

Refer : http://www.eggheadcafe.com/software/aspnet/32410183/checking-empty-value.aspx

In a stored procedure there is an uniqueidentifier input parameter and it is
used for an insert statement.

Some times that param comes from front-end application with all zero value
(because of Guid.Empty from C#).
And when it comes like that ,  (when the value is
’00000000-0000-0000-0000-000000000000′ ) i want to insert null value.

How can i check if this input parameter are empty ?

Solution:
———
CREATE PROCEDURE InsertFoo
@guid UNIQUEIDENTIFIER
AS
INSERT INTO Foo (guid_col)
SELECT NULLIF(@guid, ’00000000-0000-0000-0000-000000000000′);
============================================================================
Getting date only
SELECT dateonly = CONVERT(CHAR(8),dt,112) FROM #foo

Getting time only
SELECT timeonly = CONVERT(CHAR(8),dt,8) FROM #foo

—————
USe of Case statement

1.
http://www.craigsmullins.com/ssu_0899.htm

SELECT title, price,
        Budget = CASE price
         WHEN price > 20.00 THEN ‘Expensive’
          WHEN price BETWEEN 10.00 AND 19.99 THEN ‘Moderate’
          WHEN price < 10.00 THEN ‘Inexpensive’
          ELSE ‘Unknown’
        END,
FROM titles

2.
http://www.4guysfromrolla.com/webtech/102704-1.shtml
SELECT
  SUM(CASE
         WHEN DateDiff(d, InvoiceDate, getdate()) BETWEEN 0 AND 29 THEN InvoiceAmount
      END) as Current,
  SUM(CASE
         WHEN DateDiff(d, InvoiceDate, getdate()) BETWEEN 30 AND 59 THEN InvoiceAmount
      END) as ThirtyToSixty,
  SUM(CASE
         WHEN DateDiff(d, InvoiceDate, getdate()) BETWEEN 60 AND 89 THEN InvoiceAmount
      END) as SixtyToNinty,
  SUM(CASE
         WHEN DateDiff(d, InvoiceDate, getdate()) >= 90 THEN InvoiceAmount
      END) as NintyPlus
FROM Invoices     
=====================================