You may optionally enter a comparison operator (<, <=, >, >=, <> or =) at the beginning of each of your search values to specify how the comparison should be done.
select DATEPART(ISO_WEEK,CAST('2014-08-25' AS DATE))
213
T-SQL
Datenbank
DB-Funktion erstellen
-- Table-value
-- Variant 1
CREATE FUNCTION whichContinent
(@country nvarchar(15))
RETURNS TABLE
AS
RETURN
(
SELECT @country AS Country
)
-- Variant 2
CREATE FUNCTION whichContinent
(@country nvarchar(15))
RETURNS
@returnTableResult TABLE
(
id int
,currency VARCHAR(100)
)
AS
BEGIN
IF @country='Germany'
BEGIN
INSERT INTO @returnTableResult VALUES (1,'EURO')
END
RETURN
END
214
T-SQL
Exceptions
Try Catch Except
-- See also: http://msdn.microsoft.com/de-de/library/ms175976(v=sql.105).aspx
-- !!
-- TRY-CATCH cannot be used for all operations/situations in T-SQL.
-- Be careful and refer to the link above for details
-- !!
BEGIN TRY
SELECT 1/0;
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
217
T-SQL
Datenmengen
Anzahl Zeilen Resultset begrenzen
SELECT TOP 100 * FROM tableA
220
T-SQL
Strings
Position eines Teilstrings finden
DECLARE @searchFor_Charindex Varchar(10)
DECLARE @searchFor_Patindex Varchar(10)
DECLARE @text Varchar(100)
SET @searchFor_Charindex = 'simple'
SET @searchFor_Patindex = '%simple%' -- Remember the %. Patindex can also be used for RegEx!
SET @text = 'this is a simple text'
SELECT CHARINDEX(@searchFor_Charindex, @text) AS Position_Charindex
SELECT PATINDEX(@searchFor_Patindex, @text) AS Position_Patindex
-- 11
226
T-SQL
Systeminfo
Hostnamen ermitteln
SELECT HOST_NAME() AS Hostname;
-- prints myComputer1 or srvWindows1
241
T-SQL
Strings
Text in Großschrift oder Kleinschrift
SELECT
'my lovely Mr Singing Club' AS myString
,UPPER('my lovely Mr Singing Club') AS myStringUpper
,LOWER('my lovely Mr Singing Club') AS myStringLower
-- myString: my lovely Mr Singing Club
-- myStringUpper: MY LOVELY MR SINGING CLUB
-- myStringLower: my lovely mr singing club
244
T-SQL
Datenbank
SQL SELECT Statische Werte
SELECT
*
FROM (
VALUES
(1, 2),
(3, 4)
) AS q (col1, col2)
GO
-- the same as:
SELECT 1 AS col1, 2 AS col2 UNION ALL
SELECT 3 AS col1, 4 AS col2
GO
-- col1 | col2
-- ===========
-- 1 | 2
-- -----------
-- 3 | 4
253
T-SQL
Metadaten
Fremdschlüssel auflisten
SELECT
[FK_NAME] = obj.name
,[Schema_Name] = sch.name
,[Table] = tab1.name
,[Column] = col1.name
,[Referenced_Schema_Name] = sch2.name
,[Referenced_Table] = tab2.name
,[Referenced_Column] = col2.name
FROM sys.foreign_key_columns fkc
INNER JOIN sys.objects obj
ON obj.object_id = fkc.constraint_object_id
INNER JOIN sys.tables tab1
ON tab1.object_id = fkc.parent_object_id
INNER JOIN sys.schemas sch
ON tab1.schema_id = sch.schema_id
INNER JOIN sys.columns col1
ON col1.column_id = parent_column_id AND col1.object_id = tab1.object_id
INNER JOIN sys.tables tab2
ON tab2.object_id = fkc.referenced_object_id
INNER JOIN sys.columns col2
ON col2.column_id = referenced_column_id AND col2.object_id = tab2.object_id
INNER JOIN sys.schemas sch2
ON tab2.schema_id = sch.schema_id
285
Bash Script
Filehandling
Applikations-/Script-Verzeichnis ermitteln
#!/bin/sh
# The script is located in /tmp/abc/myscript.sh
export AppPath=$(dirname $0)
echo $0
# /tmp/abc/myscript.sh
echo $AppPath
# /tmp/abc
