SQL SELECT multi-columns INTO multi-variable

SQL Server > Scripts >  SELECT multi columns INTO multi variables

Example:

CREATE TABLE #tmp

(

    id       int PRIMARY KEY,

    name     varchar(20),
    age             int
)
GO

INSERT #tmp(id, name, age) VALUES (1, 'John',20)

INSERT #tmp(id, name, age) VALUES (2, 'Dan',40)
GO

declare @minage int,

        @maxage int
SELECT
   @minage = min(age),
   @maxage = max(age)
FROM
   #tmp
select
   @minage as minage, @maxage as maxage

drop table #tmp

While SQL Server

SQL Server >  Control of Flow > While

While represents repeated execution of an SQL statement or statement block.

• as long as the specified condition is true the statements are executed repeatedly 
• the execution of statements in the WHILE loop can be controlled from inside the loop with the BREAK and CONTINUE keywords

Syntax:

WHILE Boolean_expression { statement_block | BREAK | CONTINUE }


Example

declare @t nvarchar(max)

declare @t1 nvarchar(max)
declare @p int

set @t = 'fld1:"val1";fld2:"val2","val3";fld3:"val4";'

set @p = CHARINDEX(';', @t);

while @p > 0
begin
     set @t1 = SUBSTRING (@t, 0, @p)
     set @t = SUBSTRING (@t, @p+1, len(@t) - @p)
     set @p = CHARINDEX(';', @t);
        select @t1
     if @p = 0
      break
    else
      continue
end

Result:

CHARINDEX (SQL Server)

SQL Server > Built-in Functions > CHARINDEX

CHARINDEX SQL Server function searches an expression for another expression and returns its starting position if found.

Example

declare @t nvarchar(max)

set @t = 'blue; red; green'

SELECT CHARINDEX(';', @t);

Result
(No column name)

5

SELECT CHARINDEX(':', @t);

Result
(No column name)

0

timestamp (Transact-SQL)

SQL Server > Data types > TimeStamp

Timestamp in SQL Server is a data type that exposes automatically generated, unique binary numbers within a database.

• is generally used as a mechanism for version-stamping table rows. 
• the storage size is 8 bytes. 
• the timestamp data type is just an increment number and does not preserve a date or a time. 
• to record a date or time, use a datetime data type.

Example

IF OBJECT_ID(N'tbl1', N'U') IS NOT NULL

       DROP TABLE tbl1;
GO

CREATE TABLE tbl1

(
       id     int,
       code   timestamp
);
GO

INSERT INTO tbl1(id) values  (1)

SELECT
       *
FROM
       tbl1

Result:

id code
1  0x00000000005C0E69

Update table join SQL Server

SQL Server > Update > Using Join


UPDATE
  tbl1 AS A
  INNER JOIN tbl2 AS B ON A.F1=B.F1
SET
  A.X = 1,
  A.Y = 2
WHERE
  A.id = 100

nvarchar data type SQL Server

SQL Server > Data Types > nvarchar

• variable length 
• Unicode string data.
• length can be a value from 1 through 4,000.
• maximum storage size is 2 GB

Example

CREATE TABLE dbo.Person
(
   Name nvarchar(100)
);

Unable to evaluate expression because the code is optimized or a native frame is on top of the call stack

ASP.NET > Errors

This error normally occurs when we use Response.Redirect or Server.Transfer or Response.End in your code before completing the actual process the page was doing.

In this case what you have to do is

In place of Response.End you have to use HttpContext.Current.ApplicationInstance.CompleteRequest

in place of Response.Redirect, use the Response.Redirect ("home.aspx", false)
in place of Server.Transfer, use the Server.Execute method

DATEADD SQL Server Example

SQL Server > Built-in Functions > DATEADD

Add a number to a specified datepart of that date.

DATEADD (datepart , number , date )

datepart: is the part of date to which an integer number is added.

Example:

SELECT DATEADD(month,1, GetDate()) -- add 1 month to current day

SELECT DATEADD(day,1, GetDate()) -- add 1 month to current day
SELECT DATEADD(year,1, GetDate()) -- -- add 1 year to current day


Find last day of previous month