Difference between revisions of "SQL Snippets"
From SpenchWiki
m |
(Clever EXISTS ( SELECT 1 ) JOIN trick) |
||
| Line 137: | Line 137: | ||
EXEC sp_executesql @sql, N'@b VARBINARY(MAX) OUTPUT', @b OUTPUT | EXEC sp_executesql @sql, N'@b VARBINARY(MAX) OUTPUT', @b OUTPUT | ||
GO | GO | ||
| + | </syntaxhighlight> | ||
| + | |||
| + | * Clever EXISTS ( SELECT 1 ) JOIN trick | ||
| + | |||
| + | <syntaxhighlight lang="tsql"> | ||
| + | SELECT r2.* | ||
| + | FROM ( | ||
| + | SELECT TOP 50 CustomerID | ||
| + | FROM ( | ||
| + | SELECT MovieID | ||
| + | FROM Ratings | ||
| + | WHERE CustomerID = 915 | ||
| + | ) q | ||
| + | JOIN Ratings r | ||
| + | ON r.MovieID = q.MovieID | ||
| + | AND CustomerID <> 915 | ||
| + | AND EXISTS | ||
| + | ( | ||
| + | SELECT 1 | ||
| + | FROM Ratings re | ||
| + | WHERE re.MovieID = 1 | ||
| + | AND re.CustomerID = r.CustomerID | ||
| + | ) | ||
| + | GROUP BY | ||
| + | CustomerID | ||
| + | ORDER BY | ||
| + | COUNT(*) DESC | ||
| + | ) ro | ||
| + | JOIN Ratings r2 | ||
| + | ON r2.MovieID = 1 | ||
| + | AND r2.CustomerID = ro.CustomerID | ||
</syntaxhighlight> | </syntaxhighlight> | ||
Revision as of 20:14, 11 April 2011
- Cannot have ORDER BY clause in a view's definition
- Finding a sproc in the meta-database
SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE SPECIFIC_NAME = N'uspFindComponent'
AND SPECIFIC_SCHEMA = N'<Schema_Name, sysname, Schema_Name>'
- Declaring a table variable
DECLARE @TableVar TABLE (Cola int PRIMARY KEY, Colb char(3))
INSERT INTO @TableVar VALUES (1, 'abc')
INSERT INTO @TableVar VALUES (2, 'def')
SELECT * FROM @TableVar
GO
- WHERE EXISTS condition
SELECT Version FROM LanguageVersion WHERE EXISTS
(
SELECT * FROM LanguageVersion
)
- Selecting results into a table variable & CTE using WITH
DECLARE @i int
DECLARE @t TABLE (L bigint, ID bigint, V nvarchar(64))
INSERT @t
EXEC @i = uspFindLanguageVersion 'My Language'
SELECT V FROM @t
SELECT @i AS 'Return'
GO
WITH DirReps(ManagerID, DirectReports) AS
(
SELECT Version, Language
FROM LanguageVersion AS e
)
SELECT ManagerID, DirectReports
FROM DirReps
ORDER BY ManagerID;
GO
- TRANSACTION
BEGIN TRANSACTION;
BEGIN TRY
-- Statements
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;
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
RETURN(1);
END CATCH;
IF @@TRANCOUNT > 0
COMMIT TRANSACTION;
RETURN(0);
- MSDN links
- INSERT
- CREATE FUNCTION
- CREATE PROCEDURE
- WITH
- Using Special Data
- EXECUTE
- LIKE
- OBJECT_ID
- SET @local_variable
- Performing Prefix Searches
- Derived query
SELECT ComponentName.Name, derivedtbl_1.ID
FROM ComponentName CROSS JOIN
(SELECT ID
FROM Component) AS derivedtbl_1
WHERE derivedtbl_1.ID = ComponentName.Component
- INNER JOIN
SELECT ComponentReference.Reference, ComponentReference.Component, Reference.Location, ComponentReference.ID
FROM ComponentReference INNER JOIN
Reference ON ComponentReference.Reference = Reference.ID
WHERE (ComponentReference.Component = 983)
- Generating a GUID
DECLARE @id uniqueidentifier
SET @id = NEWID()
DECLARE @txt nvarchar(36)
SET @txt = CONVERT(nvarchar(36), @id)
SELECT @id, @txt, LEN(@txt)
- Procedure for converting a hexadecimal string into the original binary data
CREATE PROCEDURE dbo.uspHexToBinary
@s nvarchar(MAX),
@b varbinary(MAX) OUT
AS
DECLARE @sql nvarchar(MAX)
SET @sql = 'SELECT @b = ' + @s
EXEC sp_executesql @sql, N'@b VARBINARY(MAX) OUTPUT', @b OUTPUT
GO
- Clever EXISTS ( SELECT 1 ) JOIN trick
SELECT r2.*
FROM (
SELECT TOP 50 CustomerID
FROM (
SELECT MovieID
FROM Ratings
WHERE CustomerID = 915
) q
JOIN Ratings r
ON r.MovieID = q.MovieID
AND CustomerID <> 915
AND EXISTS
(
SELECT 1
FROM Ratings re
WHERE re.MovieID = 1
AND re.CustomerID = r.CustomerID
)
GROUP BY
CustomerID
ORDER BY
COUNT(*) DESC
) ro
JOIN Ratings r2
ON r2.MovieID = 1
AND r2.CustomerID = ro.CustomerID
