[SQL Server] Output column value to csv

DECLARE @CsvTaskGuid     NVARCHAR(MAX) = NULL;
SELECT @CsvTaskGuid = COALESCE(@CsvTaskGuid+',', '') + QUOTENAME(TaskGuid, '''')
    FROM [dbo].[TableName]
PRINT @CsvTaskGuid

A Pleasant New C# Syntax for String Interpolation

For the first several versions of C#, we formatted strings using the standard string.Format API:

var formattedOutput = string.Format("{0}, {1} is {2} years old", 
    person.LastName, person.FirstName, person.Age);

This API had minimal improvements over the syntax used with printf and related APIs that were first developed for the C language. Those APIs date back to the mid-1970s or earlier.

We should have progressed beyond this API in all those years. Finally, with C# 6, the new features for string interpolation will make your code much more clear when you're producing strings that are a combination of text and computed values. Working with formatted text is so increasingly common that this could be the killer feature for C# 6.

In this article, I show you the shipping syntax for string interpolation and discuss many of the scenarios in which you'll use it. I also discuss some of the syntax changes that this feature underwent from its initial public preview until its final release. Some resources on the Web still refer to previous prerelease syntaxes for this feature, so it's important to know what's current.

Let's begin by covering the current syntax through a simple example. The following line of code produces the same value as in the previous example:

var formattedOutput = $"{person.LastName}, {person.FirstName} is {person.Age} years old";

This example provides the basic syntax used in string interpolation in C# 6. You introduce string interpolation by starting the format string with the dollar sign ($) character. The "holes" in the format string are noted by the brace ({ }) characters. The main improvement is inside the braces, where you place C# expressions instead of positional indices to later parameters. This is a great improvement in readability—and much easier to get correct. Instead of using {0} and looking for that parameter, you find {person.LastName}, which instantly tells you what will be placed in the formatted string. Notice that one of the arguments I'm using is an integer (person.Age). Just like with string.Format, we can use any object. When that object is not a string, the framework will call ToString() to convert it to a string. The same construct could be written this way:

var formattedOutput =
    $"{person.LastName}, {person.FirstName} is {person.Age.ToString()} years old";

Remember that you can put any valid C# expression between the braces; you're not limited to variables. For example, you could write a point and its distance from the origin in this way:

var str =
    $"{{{pt.X}, {pt.Y}}} is {Math.Sqrt(pt.X * pt.X + pt.Y * pt.Y)} from the origin";

Two concepts are new. Let's go through this example carefully, and you'll see how these concepts work.

First, the final expression is a call to Math.Sqrt, where the parameter is X^2 + Y^2 (using the fundamental Pythagorean theorem to compute the hypotenuse of a right triangle):

{Math.Sqrt(pt.X * pt.X + pt.Y * pt.Y)}

Any valid C# expression is allowed inside the { } characters in an interpolated string. That includes—but is not limited to—method calls, LINQ queries, computations, and conditionals.

The extra braces in the statement (red in the syntax highlighting) provide an example of how to write an opening or closing brace ({) or (}) in an interpolated string. Writing a double left brace ({{) produces the opening brace ({) in the output. Writing a double right brace (}}) produces the closing brace (}) in the output.

{{{pt.X}, {pt.Y}}

For example, if a point has the values (3, 4) for x, y, this statement will set str to the value {3, 4} is 5 from the origin.

Most likely, though, the values of x and y, and almost certainly the distance, are doubles that don't have a nice output. The default representation will have any number of decimal places, and it won't be formatted nicely for you. The string interpolation feature allows you to specify format strings as part of the replacement. You can specify a floating-point value with two digits to the right of the decimal point as follows:

var str =
$"{{{pt.X:F2}, {pt.Y:F2}}} is {Math.Sqrt(pt.X * pt.X + pt.Y * pt.Y):F2} from the origin"; 

You can place any valid format string in the "hole" for that expression. Place a colon (:) after the expression, and the format string following the colon. The valid format strings depend on the type of the expression preceding the colon. Here, my format strings are all F2, which displays two digits following the decimal point for a floating-point number.

After these changes, my line of code is getting rather long. Let's use the verbatim string literal to split the string:

var str = $@"{{{pt.X:F2}, {pt.Y:F2}}} is 
 {Math.Sqrt(pt.X * pt.X + pt.Y * pt.Y):F2} from the origin";

Yes, you can combine the verbatim string literal with the interpolated string constructs.

All Valid C# Expressions Are Okay

Now let's explore some of the edges of this feature. I said any valid C# expression is legal inside the braces ({}) for string interpolation.

Many APIs can take a string parameter. For example, the following line formats the current date in a custom format:

var formattedDate = $"The current date is {DateTime.Now.ToString("MMM d, yyyy")}";

Notice that no special characters are needed to escape the quotes where the current date is displayed. All the text you place between the opening and closing braces in a formattable string will be parsed as C# source code. It will not be interpreted as a literal string. Any legal C# expression is valid. Whenever I demo this feature at a conference or user group, people in the audience always try to come up with a C# construct that won't work. They haven't managed it yet. One person even suggested a string.Format call inside an interpolated string. Yes, it works. But it's really ugly. And yes, you can nest interpolated strings. Please don't write this kind of construct in your own code! I wrote the following line just to show that the C# compiler processes interpolated string arguments as regular C#:

var nameDisplay = $@"{(hello ? $"Hello {person.FirstName} {person.LastName}" 
    : $"Goodbye {person.LastName}, {person.FirstName}")}";

Well, that is very ugly. But the nested interpolated strings parse correctly.

CAUTION

Notice that I had to surround the conditional expression with parentheses (boldfaced below) to keep the compiler from interpreting the colon as the beginning of a format string:

{(hello ? $"Hello {person.FirstName} {person.LastName}" 
    : $"Goodbye {person.LastName}, {person.FirstName}")}

There is no need to write anything this crazy. I strongly recommend against it. But the strength of the parser brings some very strong advantages. One area that I leverage often is Razor views. If you build a site using ASP.NET 5 (the major upgrade coming to ASP.NET), you can use the string interpolation feature in your user views. For example, the existing ASP.NET templates create this code in the _LoginPartial.cshtml file:

@Html.ActionLink("Hello " + User.Identity.GetUserName() + "!", "Index", "Manage",
        routeValues: null, htmlAttributes: new { title = "Manage" })

The updated template creates this code:

<a asp-controller="Manage" asp-action="Index" title="Manage">Hello
    @User.GetUserName()!</a>

Notice more here than just the changes for interpolated strings. The new attributes provide a more concise syntax for the Html.ActionLink call. I really like how the Razor syntax adopted its own natural constructs to use string interpolation in views. You just add the "at" symbol (@) to any C# expression in your HTML. As I've adopted this, my Razor views have reduced in size by almost one-third.

Internationalization (and a Bit of History)

One of the final updates to this feature involved a bit of a change that made string interpolation much richer in scenarios where your code must format strings for a culture or language that differs from the existing culture.

All the examples shown so far have created strings. The compiler will format the string using the current culture. But the dollar sign ($) character doesn't have to be limited to creating a string. You can force the compiler to create a different type, FormattableString. This type represents a composite format string, along with the arguments to be formatted. It enables you to reach into the pipeline and have greater control over the final output.

You might not want the string interpolation to use the current culture when formatting objects (numbers, dates, and so on). You can specify a particular culture for the format operation by calling an overload of string.Format. The following example formats a string containing a number for Germany, where the period (.) character used in the U.S. to separate the whole number from the fractional part should be replaced with the comma (,) character:

FormattableString fStr = $"This follows the German text format: {42.0 / 19.0}";
var output = string.Format(
    System.Globalization.CultureInfo.CreateSpecificCulture("de-de"),
    fStr.Format,
    fStr.GetArguments());

This feature was added later in the development cycle, in answer to many requests from developers who need to create output for a different culture than that of the current location. This feature was especially important for developers creating web applications.

That wasn't the only change during the development of this feature. In its earliest implementations, this feature simply replaced the positional placeholders with named arguments in any call to string.Format. To light up the feature, the brace ({ }) characters were escaped:

var formattedOutput = string.Format(
"\{person.LastName}, \{person.FirstName} is \{person.Age} years old");

But this usage had many limitations. It wasn't easy to print the braces in a formatted string. Also, being available only in a call to string.Format limited many scenarios.

Later, the $ syntax was introduced, making formatting much easier, and opening other scenarios, including the Razor-specific syntax in ASP.NET 5 that I mentioned earlier. The last changes supported the specific culture formatting.

You can see these changes in the history of the language design notes, available on GitHub. Some of the earlier notes were published when the Roslyn code was on CodePlex. Those notes may be migrating to GitHub over time.

Initial Guidance on String Interpolation in C#

I really enjoy this new feature. It has completely replaced any idiom where I used {0} and {1} in any code I write using the latest version of C#. The simplicity improves the code quality immensely. However, I haven't taken the time to go back over any existing code to change it. The new string interpolation feature compiles down to almost exactly the same constructs that we used in earlier versions. Although I prefer the new code, unless I'm changing the function in which an old construct was used, I don't update it. The benefits aren't great enough for the extra churn. If I'm making major updates to a routine, I update the code to use this new feature; I don't do it for bug fixes or new features elsewhere in a class.

from : http://www.informit.com/articles/article.aspx?p=2422807

How get yesterday and tomorrow datetime in c#

You can find this info right in the API reference.

var today = DateTime.Now;
var tomorrow = today.AddDays(1);
var yesterday = today.AddDays(-1);

from : https://stackoverflow.com/questions/8203900/how-get-yesterday-and-tomorrow-datetime-in-c-sharp

SQL Server Pagination

For 2005 / 2008 / 2008 R2

;WITH cte AS
(
    SELECT  Journals.JournalId, 
            Journals.Year, 
            Journals.Title, 
            ArticleCategories.ItemText,
            ROW_NUMBER() OVER 
                     (ORDER BY Journals.JournalId,ArticleCategories.ItemText) AS RN
    FROM    Journals LEFT OUTER JOIN
            ArticleCategories 
             ON Journals.ArticleCategoryId = ArticleCategories.ArticleCategoryId
)
    SELECT  JournalId, 
            Year, 
            Title, 
            ItemText
FROM cte
WHERE RN BETWEEN 11 AND 20

For 2012 this is simpler

SELECT Journals.JournalId,
       Journals.Year,
       Journals.Title,
       ArticleCategories.ItemText
FROM   Journals
       LEFT OUTER JOIN ArticleCategories
         ON Journals.ArticleCategoryId = ArticleCategories.ArticleCategoryId
ORDER  BY Journals.JournalId,
          ArticleCategories.ItemText 
OFFSET  10 ROWS 
FETCH NEXT 10 ROWS ONLY 

from : https://stackoverflow.com/questions/5620758/t-sql-skip-take-stored-procedure

Comparing performance for different SQL Server paging methods

For this test we will run each paging method through a SQL Server 2012 database and compare their explain plans as well as some basic performance statistics using SQL Profiler. Here is the complete test script. Note: To update the query to fetch the first 50 records simply update the start/end variables.

--SQL 2000 Paging Method
DECLARE @Start INT
DECLARE @End INT
SELECT @Start = 14000,@End = 14050

            
CREATE TABLE #employees (RowNumber INT IDENTITY(1,1),
                LastName VARCHAR(100),FirstName VARCHAR(100),
                EmailAddress VARCHAR(100))

  
INSERT INTO #employees (LastName, FirstName, EmailAddress)
SELECT LastName, FirstName, EmailAddress 
  FROM Employee 
 ORDER BY LastName, FirstName, EmailAddress
SELECT LastName, FirstName, EmailAddress 
  FROM #employees
 WHERE RowNumber > @Start AND RowNumber <= @End

  
DROP TABLE #employees

  
GO

  
--SQL 2005/2008 Paging Method Using Derived Table
DECLARE @Start INT
DECLARE @End INT
SELECT @Start = 14000,@End = 14050

  
SELECT LastName, FirstName, EmailAddress
FROM (SELECT LastName, FirstName, EmailAddress,
      ROW_NUMBER() OVER (ORDER BY LastName, FirstName, EmailAddress) AS RowNumber
      FROM Employee) EmployeePage
WHERE RowNumber > @Start AND RowNumber <= @End
ORDER BY LastName, FirstName, EmailAddress
GO

  
--SQL 2005/2008 Paging Method Using CTE
DECLARE @Start INT
DECLARE @End INT
SELECT @Start = 14000,@End = 14050;

  
WITH EmployeePage AS
(SELECT LastName, FirstName, EmailAddress,
 ROW_NUMBER() OVER (ORDER BY LastName, FirstName, EmailAddress) AS RowNumber
 FROM Employee)
SELECT LastName, FirstName, EmailAddress
FROM EmployeePage
WHERE RowNumber > @Start AND RowNumber <= @End
ORDER BY LastName, FirstName, EmailAddress
GO

  
--SQL SERVER 2012
SELECT LastName, FirstName, EmailAddress
FROM Employee
ORDER BY LastName, FirstName, EmailAddress
OFFSET 14000 ROWS
FETCH NEXT 50 ROWS ONLY;

Explain Plans

First let's take a look at the explain plans for each of these queries. The following explain plans appear in the same order as the queries in the test script above.

SQL 2000 Paging Method

---

SQL 2005/2008 Paging Method Using Derived Table

---

SQL 2005/2008 Paging Method Using CTE

---

SQL SERVER 2012

---

After analyzing these explain plans it's pretty safe to assume that when we look at the profiler results, the SQL Server 2000 query is probably going to perform the worst given that it basically has to run two queries and the second query is a complete scan of a temporary table. Interesting thing I noted after reviewing these explain plans was that the SQL Server 2005/2008 version generates the exact same query plan for both the CTE and Derived table version. Comparing these two plans to the SQL Server 2012 version we might expect that the 2012 version will perform slightly better given that it only has to perform one sort. Let's take a look at the performance numbers to confirm.

SQL Profiler Results

To analyze the performance we'll run the above queries for the first page returned as well as for a page of data somewhere in the middle of the result set. I ran the queries above 10 times and the numbers in the chart below represent the averages of the statistics collected with SQL Profiler.

Query First Page
Version	CPU (ms)	Reads	Writes	Duration
2000	328	24427	444	327
2005/2008 Derived	125	9242	0	178
2005/2008 CTE	127	9242	0	173
2012	46	8733	0	44
Query Middle Page
Version	CPU (ms)	Reads	Writes	Duration
2000	312	24658	444	313
2005/2008 Derived	157	9472	0	173
2005/2008 CTE	156	9472	0	175
2012	125	8963	0	135

Looking at these SQL Profiler results we can confirm what we had suspected after viewing the explain plans. The SQL Server 2000 version performs much poorer than the other methods and the new OFFSET and FETCH clause performs only slightly better that it's 2005/2008 conterpart using ROW_NUMBER(). The only exception to this is that when querying the first page of the result set, which all applications usually do, it performs quite a bit better than all the other methods. Almost three times faster than using the ROW_NUMBER() function.

Summary

As we can see from all the results, the new SQL Server 2012 feature is the best option for implementing a server-side page of a query result. The other added benefit is that it is also by far the most straightforward and easy to understand of all the methods.

from : https://www.mssqltips.com/sqlservertip/2696/comparing-performance-for-different-sql-server-paging-methods/

Sql Server bulk update in stored procedure

CREATE PROCEDURE [dbo].[Update_Customers]

      @tblCustomers CustomerType READONLY

AS

BEGIN

      SET NOCOUNT ON;

      --UPDATE EXISTING RECORDS

      UPDATE Customers

      SET Name = c2.Name

      ,Country = c2.Country

      FROM Customers c1

      INNER JOIN @tblCustomers c2

      ON c1.CustomerId = c2.Id

 

      --INSERT NON-EXISTING RECORDS

      INSERT INTO Customers

      SELECT Id, Name, Country

      FROM @tblCustomers

      WHERE Id NOT IN(SELECT CustomerId FROM Customers)

END

from : https://www.aspsnippets.com/Articles/SqlBulkCopy--Bulk-Insert-records-and-Update-existing-rows-if-record-exists-using-C-and-VBNet.aspx