WITH (SQL Server)

SQL Server > DML > WITH

Specifies a temporary named result set, known as a common table expression (CTE). This is derived from a simple query and defined within the execution scope of a single SELECT, INSERT, UPDATE, or DELETE statement.
This clause can also be used in a CREATE VIEW statement as part of its defining SELECT statement.
A common table expression can include references to itself. This is referred to as a recursive common table expression.






Examples:

1. Creating a simple common table expression

-- Define the CTE expression name and column list.

WITH Person_CTE (PersonID, PersonName, PersonSalary)

AS
-- Define the CTE query.
(
Select
 PersonID, PersonName, PersonSalary
FROM
 Person
)

-- Define the outer query referencing the CTE name.

SELECT

 *
FROM
 Person_CTE
ORDER BY
 PersonID;

2. Using a recursive common table expression to display multiple levels of recursion

WITH Emp(superiorid, id, username) AS

(
SELECT superiorid, id, username
FROM User
WHERE superiorid IS NULL

UNION ALL

SELECT e.superiorid, e.id, e.username
FROM User AS e
INNER JOIN IS_User AS e1
ON e.superiorid = e1.id
)

SELECT superiorid, id , username

FROM Emp
ORDER BY superiorid;

3. Get all children of parent

REPLACE (Transact-SQL)

SQL Server > Built-in Functions > REPLACE

Replaces a specified string value with another string value.

REPLACE ( expression , pattern , replacement )
Example

SELECT REPLACE('sample','ple','');

GO 

Result:sam

Height div to window height with scroll

Java > Height div to window height with scroll

<script type="text/javascript" language="javascript">

    window.onload = function () { SizeDiv(); }

    function getDocHeight() {

        var D = document;

        return Math.max(

        Math.max(D.body.scrollHeight, D.documentElement.scrollHeight),

        Math.max(D.body.offsetHeight, D.documentElement.offsetHeight),

        Math.max(D.body.clientHeight, D.documentElement.clientHeight)
    );

    }

    function SizeDiv() {
        
        document.getElementById('leftCol').style.height = getDocHeight()-175 + "px";
     
    }

</script>


SqlCommand, SqlParameter c#

C# > Data > SqlCommand & SqlParameter 

SqlCommand
Represents a Transact-SQL statement or stored procedure to execute against a SQL Server database.

SqlParameter 
Represents a parameter to a SqlCommand and optionally its mapping to DataSet columns

Example:

SqlCommand myCommand = new SqlCommand("dbo.[sp_copy]", connString);

myCommand.CommandType = CommandType.StoredProcedure;

SqlParameter param = new SqlParameter("@ret_id", SqlDbType.Int);

param.Direction = ParameterDirection.ReturnValue;
myCommand.Parameters.Add(param);

param = new SqlParameter("@Source", SqlDbType.Int);
param.Direction = ParameterDirection.Input;
param.Value = asset_id;
myCommand.Parameters.Add(param);

myCommand.ExecuteNonQuery();

id = Convert.ToInt32(myCommand.Parameters[0].Value);

Generate Method Stub

Visual Studio

Generate Method Stub is an IntelliSense Automatic Code Generation feature that provides an easy way to have Visual Studio create a new method declaration at the time you are writing a method call. Visual Studio infers the declaration from the call.

---

SET NOCOUNT SQL Server

SQL Server > Transact-SQL > SET NOCOUNT           

Stops the message indicating the number of rows affected by a Transact-SQL statement from being returned as part of the results.

SET NOCOUNT { ON | OFF }

Remarks

• ON the count is not returned
• OFF the count is returned

Trunc Date Only No Time SQL Server

SQL Server > Built-in Functions > CAST > Trunc

Trunc date in SQL Server

SELECT Cast(GetDate() AS date) Results:2012-08-18