SQL Snippets

SELECT *
    FROM INFORMATION_SCHEMA.ROUTINES 
   WHERE SPECIFIC_NAME = N'uspFindComponent'
     AND SPECIFIC_SCHEMA = N'<Schema_Name, sysname, Schema_Name>'

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

SELECT Version FROM LanguageVersion WHERE EXISTS
	(
		SELECT * FROM LanguageVersion
	)

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

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);

SELECT     ComponentName.Name, derivedtbl_1.ID
FROM         ComponentName CROSS JOIN
                          (SELECT     ID
                            FROM          Component) AS derivedtbl_1
	WHERE derivedtbl_1.ID = ComponentName.Component

SELECT     ComponentReference.Reference, ComponentReference.Component, Reference.Location, ComponentReference.ID
FROM         ComponentReference INNER JOIN
                      Reference ON ComponentReference.Reference = Reference.ID
WHERE     (ComponentReference.Component = 983)

DECLARE @id UNIQUEIDENTIFIER
SET @id = NEWID()
DECLARE @txt NVARCHAR(36)
SET @txt = CONVERT(NVARCHAR(36), @id)
SELECT @id, @txt, LEN(@txt)

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