Dinamik “WHERE” Cümleciği
Select * From Products
WHERE ((@SupplierID is null) or SupplierID=@SupplierID)) AND ((@CategoryID is null) or (CategoryID=@CategoryID));
şeklinde kullanarak parametrelerinizi parametrik hale getirebilirsiniz.
Bu sayade sadece SupplierID’ye veya sadece CategoryID’ye bağlı veya
her ikisine bağlı olarak verilerinizi çekebilirsiniz.
Source: rss
PIVOT deyimi kullanarak aşağıdaki şekilde satırlarınızı sütun haline getirebilirsiniz.
SELECT
[1] AS ‘January’
,[2] AS ‘February’
,[3] AS ‘March’
,[4] AS ‘April’
,[5] AS ‘May’
,[6] AS ‘June’
,[7] AS ‘July’
,[8] AS ‘August’
,[9] AS ‘September’
,[10] AS ‘October’
,[11] AS ‘November’
,[12] AS ‘December’
FROM (
SELECT
MONTH(OrderDate) AS ‘MonthNumber’
,COUNT(*) AS ‘OrderCount’
FROM Orders WHERE YEAR(OrderDate) = 1997
GROUP BY MONTH(OrderDate)
) AS Data
PIVOT(
SUM(OrderCount)
FOR MonthNumber IN([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
) AS PVT
Source: rss
ROW_NUMBER fonksiyonunu kullanarak verileriniz sayfalı olarak gelmesini sağlayabilirsiniz.
Özellikle “Satır” sayısının fazla olduğu tablolarda size büyük performans sağlayacaktır.
SELECT OrderID,CustomerID,OrderDate From
(SELECT ROW_NUMBER() OVER (ORDER BY OrderDate DESC) AS Row,OrderID,CustomerID,OrderDate From Orders)
As OrdersWithRowNumbers WHERE Row >= 1 AND Row <= 10
İkinci sayfa için;
SELECT OrderID,CustomerID,OrderDate From
(SELECT ROW_NUMBER() OVER (ORDER BY OrderDate DESC) AS Row,OrderID,CustomerID,OrderDate From Orders)
As OrdersWithRowNumbers WHERE Row >= 11 AND Row <= 20
Source: rss
if EXISTS (select * from authors where au_id = ‘172-32-1176’)
Print ‘Record exits – Update’
ELSE
Print ‘Record doesn”t exist – Insert’
Select -1 As CategoryID,‘Hepsi’ As CategoryName
Union ALL
Select CategoryID,CategoryName From Categories
kullanarak Hepsi değerine -1 verebilirsiniz…
Source: rss