Syntax Error

Code samples and hints

Script Table Data with TSQL

 

-- Script all records from the drugcard rule table
EXEC sp_generate_inserts
@table_name = 'rule_med_drugcard'

 

-- Script all active records from the drugcard rule table
EXEC sp_generate_inserts
@table_name = 'rule_med_drugcard', 
@from = 'FROM dbo.rule_med_drugcard WHERE active = 1'

 

Download here

Instructions and more

Search for column name across all tables

Alle Tabellen welche eine bestimmte Spalte besitzen ausgeben ...

 

DECLARE @ColumnName       varchar(1000)

SET @ColumnName = 'PlayerId'

    select o.name as TableName,
           c.name as [ColumnName]
      from sysobjects o 
inner join syscolumns c on c.id = o.id
      where o.type = 'u' and c.name = @ColumnName       
   Order by TableName

 

Quelle

Case sensitive DISTINCT

To get a case sensitive DISTINCT result, use the COLLATE keyword ... 

SELECT 
   DISTINCT Column_With_Case_Values COLLATE sql_latin1_general_cp1_cs_as
FROM  
   Case_Insensitive_Table

Change Database-Table Collations

To change the Database Table Collations dynamically, you can generate update scripts with the following snippet

declare  @toCollation sysname 
      
SET    @toCollation = 'Latin1_General_100_CI_AS' --  << insert your destination collation name

SELECT 'ALTER TABLE ' + INFORMATION_SCHEMA.COLUMNS.TABLE_NAME +
       '   ALTER COLUMN ' + COLUMN_NAME + ' ' + DATA_TYPE +
       CASE WHEN CHARACTER_MAXIMUM_LENGTH = -1 then '(max)'
            WHEN DATA_TYPE in ('text','ntext') then ''
            WHEN CHARACTER_MAXIMUM_LENGTH IS NOT NULL 
             THEN '('+(CONVERT(VARCHAR,CHARACTER_MAXIMUM_LENGTH)+')' )
            ELSE isnull(CONVERT(VARCHAR,CHARACTER_MAXIMUM_LENGTH),' ') END
       +' COLLATE ' + @toCollation+ ' ' + CASE IS_NULLABLE
                                           WHEN 'YES' THEN 'NULL'
                                           WHEN 'No' THEN 'NOT NULL' 

END
FROM INFORMATION_SCHEMA.COLUMNS INNER JOIN INFORMATION_SCHEMA.TABLES
ON  INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = INFORMATION_SCHEMA.TABLES.TABLE_NAME
AND INFORMATION_SCHEMA.COLUMNS.TABLE_SCHEMA  = INFORMATION_SCHEMA.TABLES.TABLE_SCHEMA
WHERE DATA_TYPE IN ('varchar' ,'char','nvarchar','nchar','text','ntext')
AND TABLE_TYPE = 'BASE TABLE'
and COLLATION_NAME <> @toCollation

Solution: It takes much longer to run a stored procedure than run the code inside the stored procedure

If you have the problem that a stored procedure takes much longer to execute than the exactly same code direct on the MS SQL Server Management Studio, consider the possibility of side-effects from "Parameter Sniffing".
 
Example:
CREATE Procedure [dbo].[SlowExcecution]
@MyParam NVARCHAR(50)
AS

SELECT * FROM XYZ WHERE A = @MyParam
GO
 
If this code runs fast without SP, but slow as SP, then add a new local variable and copy the parameter into this variable. Then use this variable instead of the parameter.
CREATE Procedure [dbo].[FastExcecution]
@MyParam NVARCHAR(50)
AS

DECLARE @MyLocalVariable NVARCHAR(50)
@MyLocalVariable = @MyParam

SELECT * FROM XYZ WHERE A = @MyLocalVariable
GO
 
For details and background informations take a look at this great blog ....

Script to generate INSERT statements

This procedure generates INSERT statements using existing data from the given tables and views. Later, you can use these INSERT statements to generate the data. It's very useful when you have to ship or package a database application. This procedure also comes in handy when you have to send sample data to your vendor or technical support provider for troubleshooting purposes. 

http://vyaskn.tripod.com/code.htm#inserts

ROW_NUMBER(), RANK(), and DENSE_RANK()

One of the most handy features introduced in SQL 2005 were the ranking functions; ROW_NUMBER(), RANK(), and DENSE_RANK(). For anyone who hasn’t been introduced to these syntactic gems, here’s a quick rundown:

http://thehobt.blogspot.com/2009/02/rownumber-rank-and-denserank.html

ROW_NUMBER()

Aufsteigende Zeilen-Nummer gemäss ORDER BY, optional beschränkt auf Subsets gegeben durch PARTITION BY.

RANK()

Aufsteigende Zeilen-Wertigkeit/Rang (gleiche Zeileninhalte ergeben gleicher Rang) gemäss ORDER BY, optional beschränkt auf Subsets gegeben durch PARTITION BY. Es können Lücken entstehen, wenn mehrere Zeilen den gleichen Rang haben (Bsp. 1, 2, 2, 4, 5 <-- 3 wird ausgelassen, da 2 zwei Mal vorkommt).

DENSE_RANK()

Identisch zu RANK() mit der Ausnahme, dass keine Lücken in der Nummerierung entstehen (Bsp. 1, 2, 2, 3, 4 <-- inkl. 3)

SQL Server 2005 - Reindex der gesamten Datenbank

USE myDB
GO
EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 90)"
GO
EXEC sp_updatestats
GO

SQL SERVER 2005 uses ALTER INDEX syntax to reindex database. SQL SERVER 2005 supports DBREINDEX but it will be deprecated in future versions.

Laufnummer/Sequenz berechnen

Laufnummer/Sequenz berechnen

Berechnet für alle Einträge eine fortlaufende Nummer, basierend auf einer gegebenen Sortierung und beginnend mit der höchsten bereits vorhandenen Laufnummer.

SELECT
       ID,
       ROW_NUMBER() OVER(ORDER BY ID) + (SELECT MAX(LaufNr) FROM MyTable) LaufNr
FROM
       [MyTable]

Daten in XML-Spalte suchen

Daten in XML-Spalte suchen

CREATE TABLE #mal
(
   id INT,
   n xml
)


INSERT INTO #mal (id, n) VALUES (1, '<dd><a>Micky</a><b>Maus</b></dd>') 
INSERT INTO #mal (id, n) VALUES (1, '<dd><a>Donald</a><b>Duck</b></dd>') 


SELECT *, n.value('(/dd/a)[1]', 'varchar(max)') FROM #mal
WHERE n.value('(/dd/a)[1]', 'varchar(max)') = 'Micky'


DROP TABLE #mal

für kompliziertere XMLs:

<Parameters xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
   <Parameter>
     <Name>RETURN_VALUE</Name>
   </Parameter>
   <Parameter>
     <Name>FirstName</Name>
     <Value xsi:type="xsd:string">Tom</Value>
   </Parameter>
   <Parameter>
     <Name>LastName</Name>
     <Value xsi:type="xsd:string">Binggeli</Value>
   </Parameter>
   <Parameter>
     <Name>NationalityID</Name>
     <Value xsi:type="xsd:int">2</Value>
   </Parameter>
</Parameters>


select * from t_databaseaudit
where
parameters.exist('/Parameters/Parameter[Name="LastName"][Value="Binggeli"]')=1

Simpler XML Export

Simpler XML Export

SELECT * FROM MyTable FOR XML RAW ('MyTable'), ELEMENTS XSINIL, ROOT('Export')

Daten für XML hierarchisch ausgeben

As described in the topic, Constructing XML Using FOR XML, RAW and AUTO mode do not provide much control over the shape of the XML generated from a query result. However, EXPLICIT mode provides the most flexibility in generating the XML you want from a query result.

The EXPLICIT mode query must be written in a specific way so that the additional information about the required XML, such as expected nesting in the XML, is explicitly specified as part of the query. Depending on the XML you request, writing EXPLICIT mode queries can be cumbersome. You may find that Using PATH Mode with nesting is a simpler alternative to writing EXPLICIT mode queries.

Because you describe the XML you want as part of the query in EXPLICIT mode, you must ensure that the generated XML is well formed and valid.

http://msdn2.microsoft.com/en-us/library/ms189068.aspx

Erster nicht-NULL Wert aus einer Liste

Erster nicht-NULL Wert aus einer Liste.

CASE 
         WHEN COALESCE(PRS_Name, PRS_Zusatzname, PRS_Vorname) IS NULL THEN '' 
         ELSE ', ' + ISNULL(PRS_Name + ' ','') + ISNULL(PRS_Zusatzname + ' ','') + ISNULL(PRS_Vorname,'') 
END

How to Share Data Between Stored Procedures

This article tackles two related questions:

  • How can I use the result set from one stored procedure in another, also expressed as How can I use the result set from a stored procedure in a SELECT statement?
  • How can I pass a table as a parameter from one stored procedure to another?

In this article I will discuss a number of methods, and also point out their advantages and drawbacks. Some of the methods apply only when you want to reuse a result set, whereas others apply in both situations. In the case you want to reuse a result set, most methods require you to rewrite the stored procedure in one way or another, but there are some methods that do not.

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