Difference between revisions of "SQL Snippets"

EXEC sp_updatestats

SELECT @@VERSION

WHERE	(ComponentName.Name = @name COLLATE SQL_Latin1_General_CP1_CS_AS)

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

-- Drop stored procedure if it already exists
/*IF EXISTS (
  SELECT * 
    FROM INFORMATION_SCHEMA.ROUTINES 
	WHERE SPECIFIC_NAME = N'uspFindComponent'
)
   DROP PROCEDURE uspFindComponent
--GO

CREATE PROCEDURE uspFindComponent
*/ALTER PROCEDURE [dbo].[uspFindComponent]

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

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

WITH (INDEX(<spatial index name>))

declare c cursor static for
select object_id, name, index_id from sys.indexes where type_desc = 'SPATIAL'
open c
      declare @obj int
      declare @statsname varchar(max)
      declare @indid int
      declare @tname varchar(max)
fetch next from c into @obj, @statsname, @indid
while (@@fetch_status = 0)
begin
      declare @internaltablename varchar(max)
      declare @stmt varchar(max)
      fetch next from c into @obj, @statsname, @indid
      select top 1 @tname = name from sys.objects where object_id = @obj
      set @internaltablename = 'sys.extended_index_' + cast(@obj as varchar(max)) + '_' + cast(@indid as varchar(max))
    select 'Showing statistics for ', @tname
      select @stmt = 'dbcc show_statistics (''' + @internaltablename + ''', ''' + @statsname + ''')'
      exec (@stmt)
end
close c
deallocate c