How to share data between stored procedures ? Good Link

http://www.sommarskog.se/share_data.html



Discusses in detail various methods :

This table summarises the methods:

Method	Input/ Output	SQL Server versions	Comment
Using OUTPUT Parameters	Output	All	Not generally applicable, but sometimes overlooked.
Table-valued Functions	Output	SQL 2000	Probably the best method for output, but has some restrictions.
Inline Functions			Use this when you want to reuse a single SELECT.
Multi-statement Functions			When you need to encapsulate more complex logic.
Using a Table	In/Out	All	Most general methods with no restrictions, but a little more complex to use.
Sharing a Temp Table			Mainly for single pair of caller/callee.
Process-keyed Table			Best choice for many callers to same callee.
Global Temp Tables			A variation of Process-Keyed.
INSERT-EXEC	Output	SQL 6.5	Does not require rewrite. Has some gotchas.
Table Parameters and Table Types	In/(Out)	SQL 2008	Could have been the final answer, but due to a restriction it is only mildly useful in this context.
Using the CLR	Output	SQL 2005	Does not require a rewrite. Clunky, but is useful as a last resort when INSERT-EXEC does not work.
OPENQUERY	Output	SQL 7	Does not require rewrite. Tricky with many pitfalls.
Using XML	In/Out	SQL 2005	A roundabout way that requires you to make a rewrite, but it has some advantages over the other methods.
Using Cursor Variables	Output	SQL 7	Not recommendable.

Have a look at other articles also on the home page :

http://www.sommarskog.se/index.html

The curse and blessings of dynamic SQL. How you use dynamic SQL, when you should - and when you should not. (Added June 2001, Completely revised: April 2006. Most recent update: December 2008.) German translations of an old version available.

Arrays and Lists in SQL Server. Several methods on how to pass an array of values from a client to SQL Server, and performance data about the methods. There are three articles: one for SQL 2008 which is devoted entirely to table-valued parameters, one for SQL 2005 and later (for situations when you cannot use TVPs), and one for SQL 2000 and earlier. (Added March 2003. Version for SQL 2005 added March 2007, major revision and addition of article for SQL 2008: January 2010. Most recent update October 2010.)

Dynamic Search Conditions. How to write a stored procedure that permits users to select among many search conditions, using both dynamic and static SQL. There are two articles: one for SQL 2008, and one for SQL 2005 and earlier. (Added May 2003. Most recent update: November 2009. French and German translations of the SQL 2005 version available.)

How to share data between stored procedures. Different ways of passing sets of data between stored procedures. (Added July 2001, Most recent update: January 2010)

Giving Permissions through Stored Procedures. SQL 2005 adds two new methods – signing with certificates and impersonation with EXECUTE AS – that can manage cases where the classic method of ownership chaining fails. I explain the two new methods, as well as the old one, and warn you about the pitfalls. (Added February 2006)

Slow in the Application, Fast in SSMS? Ever experienced the situation that your query runs slow in your application, but when you try it in SQL Server Management Studio, the response is immediate? This article explains why this may happen, what parameter sniffing is and suggests methods to analyse and address your performance problem. (Added February 2011.)

Build Your Own Index. This is a teaser for.. Well, follow the link and read on! (Added November 2009.)

Error Handling in SQL 2005 and Later. This is an unfinished article, which consists only of a jumpstart section that shows how to use BEGIN-TRY. The full article will appear ...sometime. The article also gives you links to my old articles for error handling in SQL 2000. (Added July 2003. Most recent update: November 2009 )

Connecting to MS SQL Server from Unix. A brief summary of what's available. Since I don't work with Unix myself, it's not extremely well-informed, but people have kept asking me about this. (Added Jan 1999, Latest update: June 2008)

How find the difference between two consecutive rows in a table ?

By using a self join , as below.
Note that this query assumes the table has an autoincrement column like ID . If it is not, then possibly it will have to generated using ROW_NUMBER. Note that using autoincrement is better than using something like TOP.



create table test ( ID int, val int) insert test( ID , val) select 1, 10union all select 2, 12union all select 3, 9union all select 4, 15union all select 5, 8union all select

 select



select

ID val
1 10
2 12
3 9
4 15
5 8
6 20



select a.ID , b.ID , a.val , b.val , a.val - b.val as diff from test a left join test b on


ID ID val val diff
1 NULL 10 NULL NULL
2 1 12 10 2
3 2 9 12 -3
4 3 15 9 6
5 4 8 15 -7
6 5 20 8 12 b.ID = a.ID -1 * from test a.ID , b.ID , a.val , b.val , a.val - b.val as diff from test a left join test b on b.ID = a.ID -1 6, 20

Very good articles on SQL Server

Cumulative Sum in SQL Server

http://stackoverflow.com/questions/2120544/how-to-get-cumulative-sum


declare  @t table (
    id int,
    SomeNumt int )



 insert into @t select 1,10 union select 2,12 union select 3,3 union select 4,15 union select 5,23

 select * from @t 




select t1.id, t1.SomeNumt, SUM(t2.SomeNumt) as sum from @t t1 inner join @t t2 on t1.id >= t2.id group by t1.id, t1.SomeNumt order by t1.id 

What is recommended practice for Response.Redirect ?

Use Response.Redirect ( URL , false);

The second parameter false is important, it avoids calling the End method  of calling page.


Response.Redirect uses HTTP 302 status code (Moved Temporarily) in the background.




http://msdn.microsoft.com/en-us/library/a8wa7sdt.aspx

When you use this method in a page handler to terminate a request for one page and start a new request for another page, set endResponse to false and then call the CompleteRequest() method. If you specify true for the endResponse parameter, this method calls the End method for the original request, which throws a ThreadAbortException exception when it completes. This exception has a detrimental effect on Web application performance, which is why passing false for the endResponse parameter is recommended. For more information, see the End method.

ASP.NET performs the redirection by returning a 302 HTTP status code.



http://support.microsoft.com/kb/312629/EN-US/

PRB: ThreadAbortException Occurs If You Use Response.End, Response.Redirect, or Server.Transfer

[GENERATING PAGE NUMBER IN SELECT QUERIES] I want to give a group number to rows, which will change on every 10th row. e.g. First 10 rows grp no 0, next 10 rows grp no 1, and so on. How can I do this ?

Give Row number to each row ( by using ROW_NUMBER)  , then divide this row_number by a particular no  say 10 to get grp no.


( Note that this 10 can be any number , it will be the group or page size)
select fuellevel , DIFF , timeparts , row_number() over ( order by timeparts ) as rownum , row_number() over ( order by timeparts ) /10 as grp from RSCFuelChart
e.g

fuellevel	DIFF	timeparts	rownum	grp
1.02	0	7:20:17	1	0
1.02	0	7:20:50	2	0
1.02	0	7:21:20	3	0
1.02	0	7:21:50	4	0
1.02	0	7:22:20	5	0
1.02	0	7:22:50	6	0
1.02	0	7:23:20	7	0
1.02	0	7:23:50	8	0
1.02	0	7:24:18	9	0
1.02	0	7:24:51	10	1
1.02	0	7:25:21	11	1
1.02	0	7:25:51	12	1
1.02	0	7:26:21	13	1
1.02	0	7:26:51	14	1
1.02	0	7:27:21	15	1
1.02	0	7:27:48	16	1
1.02	0	7:28:22	17	1
1.02	0	7:28:52	18	1
1.02	0	7:29:22	19	1
1.02	0	7:29:52	20	2
1.02	0	7:30:22	21	2
1.02	0	7:30:52	22	2
1.02	0	7:30:52	23	2
1.02	0	7:31:22	24	2
1.02	0	7:31:53	25	2
1.02	0	7:32:23	26	2
1.02	0	7:32:53	27	2
1.02	0	7:33:23	28	2
1.02	0	7:33:53	29	2
1.02	0	7:34:23	30	3
1.02	0	7:34:53	31	3
1.02	0	7:35:21	32	3
1.02	0	7:35:54	33	3
1.02	0	7:36:24	34	3

This can be very useful in smoothning the graphs, e.g. following query takes avg of every 10 records and max(time) of these 10 records.

select AVG(a.fuellevel) as fiellevel, MAX(timeparts) as timeparts from ( select fuellevel , DIFF , timeparts , row_number() over ( order by timeparts ) as rownum , row_number() over ( order by timeparts ) /10 as grp from RSCFuelChart ) A group

this essentially reduces no of recs from say 500 in all to 50 only to be plotted. by a.grp


==============================
So to summarize :


declare @pagesize int = 20 , @pagenumber int = 1select * from ( select fuellevel , DIFF , timeparts , row_number() over ( order by timeparts ) as rownum from RSCFuelChart ) A where ((a.rownum /@pagesize) + 1) = @pagenumber

Must set culture value for using CompareValidator for date formatting.

culture en-US : M/dd/yyyy
culture en-GB : dd/MM/yyyy , dd-MM-yyyy , dd.MM.yyyy


must set
System.Threading.Thread.CurrentThread.CurrentCulture

not
System.Threading.Thread.CurrentThread.CurrentUICulture



System.Threading.
System.Threading.Thread.CurrentThread.CurrentUICulture = new System.Globalization.CultureInfo("en-GB");Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-GB");


http://msdn.microsoft.com/en-us/goglobal/bb688124


The easiest and most efficient way of formatting dates in the .NET world is to take advantage of the DateTime structure, which provides methods allowing you to perform culture-sensitive operations on DateTime. Use the DateTimeFormatInfo class to format and display a DateTime based on a culture-specific standard. DateTimeFormatInfo defines how DateTime values are formatted and displayed, depending on the culture. For example, using the ShortDatePattern, the date April 24, 2001, is formatted as 4/24/2001 for the "en-US" culture and 24/04/2001 for the "en-GB"-English (United Kingdom)-culture.
An instance of DateTimeFormatInfo can be created for a specific culture, but not for a neutral culture. A neutral culture does not provide enough information to display the correct date format. You just saw that English (United States) and English (United Kingdom) share the same neutral culture-English-and yet have completely different formatting for the date.