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);

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: