Various Sizes And Lengths In Database

But, it is suggested to use 30 as maximum column name length in SQL Server.

Feature, SQL Server 2000, Oracle 9i Database

database name length, 128, 8
column name length, 128, 30
index name length, 128, 30
table name length, 128, 30
view name length, 128, 30
stored procedure name length, 128, 30
max columns per index, 16, 32
max char() size 8000, 2000
max varchar() size, 8000, 4000
max columns per table, 1024, 1000
max table row length, 8036, 255000
max query size, 16777216, 16777216
recursive subqueries, 40, 64
constant string size in SELECT, 16777207, 4000
constant string size in WHERE, 8000, 4000


http://www.mssqlcity.com/Articles/Compare/sql_server_vs_oracle.htm

Dynamic SQL in T-SQL

First of all, the dynamic SQL is not part of the stored procedure, but constitutes its own scope. So, it is a independent thread. Your task indeed is to pass the bind variable and/or parameter into that thread and retrieve the result back to your stored procedure.

There are two ways to invoke dynamic SQL, EXEC sp_executesql and EXEC(). EXEC() is simple and useful when SQL Server version is older than 2005. Since EXEC only permits string literals and string variables to be concatenated and not arbitrary expressions, and since you cannot use parameters, you cannot as easily get values out from EXEC(). Let us make it easier: Never use EXEC() in stored procedure. Let DBA use it for their tasks.

In EXEC sp_executesql, the first built-in parameter @stmt is the SQL query statement in string. It is best to declare it as NVARCHAR datatype. So does for the second built-in parameter @params.

If your own parameter is of column name and table name, please use this way: quotename(@column_or_table_name). Else, it won't work. By doing this, you indeed is to embed column name and table name as bind variable into the SQL script. It thus no longer the parameters of built-in stored procedure sp_executesql. So, please declare it outside the sp_executesql, and do not include it in the built-in parameter @params.

To get the query result out from the sp_executesql, in your SQL query statement, the following SELECT style is necessary:

SET @sqlStatement = 'SELECT @avgResult = AVG(' + quotename(@Column_Name) + ' ) FROM Tab_Name'

In addition, your own parameter for sp_executesql needs to be declared as OUTPUT.

After first two built-in parameters for sp_executesql, you will need to assign values to your own parameters for sp_executesql.

Okey, here is an example:

DECLARE @result INT
DECLARE @EachColumn VARCHAR(30)
DECLARE curs CURSOR FOR
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Temp_Transform_Result'
OPEN curs
FETCH NEXT FROM curs INTO @EachColumn
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @exeString NVARCHAR(2000)
SET @exeString = ' SELECT @result = CONVERT(INT, AVG(' + quotename(@EachColumn) + '))
FROM Temp_Transform_Result
'
EXEC sp_executesql @exeString
, N'@result INT OUTPUT'
, @result OUTPUT
SET @exeString = ' INSERT INTO Temp_Transform_Input
VALUES ( ''IQM''
, @EachColumn
, @result
)
'
EXEC sp_executesql @exeString
, N'@EachColumn VARCHAR(30)
,@result INT
'
, @EachColumn = @EachColumn
, @result = @result
FETCH NEXT FROM curs INTO @EachColumn
END
CLOSE curs
DEALLOCATE curs

In first sp_executesql, @EachColumn is an embed bind variable rather than parameters. On contrast, in second sp_executesql, it is parameter. You may note, same parameter names used for sp_executesql and outsider stored procedure. So you see

, @EachColumn = @EachColumn
, @result = @result

The left hand side is parameter for sp_executesql, and the right hand side is the variable for stored procedure.


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

Error Message: The SELECT item identified by the ORDER BY number 1 contains a variable as part of the expression identifying a column position...

This SQL Server full error message is: "The SELECT item identified by the ORDER BY number 1 contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name."

It happens when a bind variable is placed in ORDER BY clause. For dynamic SQL, the solution is Database Engine Stored Procedures sp_executesql. For example:

Your failed original statement:
SET @stddev = SELECT STDEVP(@EachColumn)
FROM Temp_IQM_Input
ORDER BY @EachColumn

The solution:
DECLARE @stddevString NVARCHAR(1000)
SET @stddevString = ' SELECT @out = STDEVP(@para) '
+ ' FROM Temp_IQM_Input '
+ ' ORDER BY @para '
DECLARE @stddev INT
EXECUTE sp_executesql @stddevString
, N'@para VARCHAR(32), @out INT OUTPUT'
, @para = @EachColumn
, @out = @stddev OUTPUT
;

For sp_executesql statement, the 1st line is the sql query in string, 2nd line to define the bind variables, if the parameter used for output, it needs to be stated. The rest lines are for variables assignments.

It is painful because there are too many WIP parameters; but it is the way.

Set Vs Select in T-SQL

Select will allow more than one records to to assigned to variable:

SELECT @var = singleColumn
FROM tab

Set either allow one record, or generate an error while the original value of variable remains unchanged:

SET @var =
( SELECT singleColumn
FROM tab
)


http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/05/18/449.aspx

Max Length for Varchar Datatype Variable in T-SQL

There is a limit on how long a varchar datatype variable can be. It is 8,000. It obviously not enough since this kind of variable generally used dynamical SQL, especially when use with loop statement to add up with the query statement.

The solution? Don't concatenate it. Instead, execute it often and save into a WIP temporary tables. Sure, you would need to programmatically delete these temporary tables at the end of your procedure.

http://www.fotia.co.uk/fotia/DY.13.VarCharMax.aspx

Tutorial: JavaScript：Defining Array

Array in JavaScript is class derived from Object. There are several ways of defining an array:

1. Regular array. Pass an optional integer argument to control array's size. However, if it is defined, no more element can be defined exceeding this number.
； var myfriends = new Array(optionalElementNumber)
； myfriends[0] = "John"
； myfriends[1] = "Bob"
； myfriends[2] = "Sue"

2. Condensed array:
； var myfriends = new Array("John", "Bob", "Sue")

3. Literal array：
； var myfriends = ["John", "Bob", "Sue"]

4. Empty array:
; var myArray = []
; myArray.push("John")
; myArray.push("Bob")
; myArray.push("Sue")

The elements/values of an array can be numeric, string, another array and object, or reference points to these:
； var var_0 = "John"
； var var_1 = "Bob"
； var var_2 = "Sue"
； var myfriends = new Array(var_0, var_1, var_2)
or
； var myfriends = [var_0, var_1, var_2]

When an element is another array, it becomes multidimensional array.

The Calculation of Chute Time

Chute time is the period from crew accepts the task to the engine first started before actual departure in emergency services field. In terms of reviewing historic data, the point of actual departure usually is quite clear. However, the point of crew accepts is ambiguous. It is norm in aviation industry there are several accepts and declines co-existing for a trip. The following algorithm may help to determine this point:

The oldest accept event which is newer than newest decline event.

However, if the working system does flag the last accepting event being the true acceptance, for which the crew being occupied, then the last event should be used.

Following is a real example in T-SQL how to calculate chute time based on IQM and based on first approach:

USE ArchiveOPTIMAS
GO

IF EXISTS( SELECT *
FROM QM.SYS.TABLES
WHERE NAME = 'SSRS_Program_Temp_Legs'
)
DROP TABLE QM.dbo.SSRS_Program_Temp_Legs
IF EXISTS( SELECT *
FROM QM.SYS.TABLES
WHERE NAME = 'SSRS_Program_Temp_CrewTime'
)
DROP TABLE QM.dbo.SSRS_Program_Temp_CrewTime

DECLARE @Type VARCHAR(4)
DECLARE @Con VARCHAR(1)
SET @Type = 'URG'
SET @Con = 'D'
SELECT DISTINCT trip.trip_id
, t.transport_id
, tl.actual_departure AS Patient_Leg_Departure
, tl.actual_arrival AS Patient_Leg_Arrival
INTO QM.dbo.SSRS_Program_Temp_Legs
FROM dbo.transport AS t
INNER JOIN dbo.trip_leg_patient AS tlp ON tlp.transport_id = t.transport_id
INNER JOIN dbo.trip_leg AS tl ON tl.trip_leg_id = tlp.trip_leg_id
INNER JOIN dbo.trip ON trip.trip_id = tl.trip_id
INNER JOIN QM.dbo.dw_Legs AS dl on dl.Patient_id = t.transport_id
WHERE tl.trip_leg_status_cd = 'COMP'
AND t.transport_status_cd = 'COMP'
AND tl.actual_departure IS NOT NULL
AND tl.actual_arrival IS NOT NULL
AND t.transport_priority_cd = @Type
AND dl.Contract = @Con

SELECT Rest.trip_id AS Trip_Id
, CASE WHEN DATEDIFF(MI, Chute_Time.Crew_Accepts, Rest.Prior_Departure)>0 THEN DATEDIFF(MI, Chute_Time.Crew_Accepts, Rest.Prior_Departure) ELSE NULL END AS S1_2
, CASE WHEN DATEDIFF(MI, Rest.Prior_Departure, Rest.Prior_Arrival)>0 THEN DATEDIFF(MI, Rest.Prior_Departure, Rest.Prior_Arrival) ELSE NULL END AS S2_3
, CASE WHEN DATEDIFF(MI, Rest.Prior_Arrival, Rest.Patient_Leg_Departure)>0 THEN DATEDIFF(MI, Rest.Prior_Arrival, Rest.Patient_Leg_Departure) ELSE NULL END AS S3_4
, CASE WHEN DATEDIFF(MI, Rest.Patient_Leg_Departure, Rest.Patient_Leg_Arrival)>0 THEN DATEDIFF(MI, Rest.Patient_Leg_Departure, Rest.Patient_Leg_Arrival) ELSE NULL END AS S4_5
, CASE WHEN DATEDIFF(MI, Rest.Patient_Leg_Arrival, Rest.After_Departure)>0 THEN DATEDIFF(MI, Rest.Patient_Leg_Arrival, Rest.After_Departure) ELSE NULL END AS S5_6
, CASE WHEN DATEDIFF(MI, Chute_Time.Crew_Accepts, Rest.After_Departure)>0 THEN DATEDIFF(MI, Rest.Prior_Departure, Rest.After_Departure) ELSE NULL END AS S1_6
INTO QM.dbo.SSRS_Program_Temp_CrewTime
FROM (
SELECT sptl.trip_id
, MIN(th.add_datetime) AS Crew_Accepts
FROM QM.dbo.SSRS_Program_Temp_Legs AS sptl
INNER JOIN dbo.transport_history AS th ON th.transport_id = sptl.transport_id
INNER JOIN dbo.transport_event AS te ON te.transport_event_id = th.transport_event_id
INNER JOIN
( SELECT sptl.trip_id
, MAX( CASE WHEN th.add_datetime IS NOT NULL AND te.transport_event_id = 129
THEN th.add_datetime
ELSE DATEADD(DAY, 1, '1900-01-01')
END
) AS Crew_Decline
FROM QM.dbo.SSRS_Program_Temp_Legs AS sptl
LEFT JOIN dbo.transport_history AS th ON th.transport_id = sptl.transport_id
LEFT JOIN dbo.transport_event AS te ON te.transport_event_id = th.transport_event_id
GROUP BY sptl.trip_id
) AS Decline_Events
ON Decline_Events.trip_id = sptl.trip_id
WHERE th.add_datetime >= Decline_Events.Crew_Decline
AND te.transport_event_id = 130
GROUP BY sptl.trip_id
) AS Chute_Time
FULL JOIN
( SELECT sptl.trip_id
, Before_After.Prior_Departure
, Before_After.Prior_Arrival
, sptl.Patient_Leg_Departure
, sptl.Patient_Leg_Arrival
, Before_After.After_Departure
FROM ( SELECT sptl.trip_id
, MAX(CASE WHEN tl.actual_departure < sptl.Patient_Leg_Departure THEN tl.actual_departure END) AS Prior_Departure
, MAX(CASE WHEN tl.actual_arrival < sptl.Patient_Leg_Departure THEN tl.actual_arrival END) AS Prior_Arrival
, MIN(CASE WHEN tl.actual_departure > sptl.Patient_Leg_Arrival THEN tl.actual_departure END) AS After_Departure
FROM QM.dbo.SSRS_Program_Temp_Legs AS sptl
INNER JOIN dbo.trip_leg AS tl ON tl.trip_id = sptl.trip_id
GROUP BY sptl.trip_id
) AS Before_After
INNER JOIN QM.dbo.SSRS_Program_Temp_Legs AS sptl
ON sptl.trip_id = Before_After.trip_id
) AS Rest
ON Rest.trip_id = Chute_Time.trip_id

SELECT SUM(IQM_Sep) AS IQM
, SUM(Stdevp_Sep) AS Stdevp
FROM ( SELECT AVG(S1_2) AS IQM_Sep
, NULL AS Stdevp_Sep
FROM QM.dbo.SSRS_Program_Temp_CrewTime AS Minut
WHERE S1_2 IS NOT NULL
AND Minut.Trip_Id NOT IN (SELECT TOP 25 PERCENT Trip_Id FROM QM.dbo.SSRS_Program_Temp_CrewTime WHERE S1_2 IS NOT NULL ORDER BY S1_2)
AND Minut.Trip_Id NOT IN (SELECT TOP 25 PERCENT Trip_Id FROM QM.dbo.SSRS_Program_Temp_CrewTime WHERE S1_2 IS NOT NULL ORDER BY S1_2 DESC)
UNION
SELECT NULL AS IQM_Sep
, STDEVP(S1_2) AS Stdevp_Sep
FROM QM.dbo.SSRS_Program_Temp_CrewTime
) AS Colum


http://www.math.carleton.ca/CanQueue-08/canq08Alanis.pdf