Split stored procedures into subprocedures

Image


end

Exception Handling TRY-CATCH T-SQL

CREATE PROCEDURE UpdateClient
(
@ClientID int,
@Name varchar(50)
)
AS
BEGIN
declare @someID int

BEGIN TRY
BEGIN TRAN

SET @Name = NULLIF(@Name, '')

INSERT INTO ...
VALUES ...

SELECT @someID = scope_identity()

UPDATE ...
SET SomeID = @someID
WHERE ClientID = @ClientID

COMMIT TRAN
END TRY

BEGIN CATCH
ROLLBACK TRAN
EXEC ThrowException
END CATCH
END
GO

/*
*/

CREATE PROCEDURE ThrowException
AS
DECLARE @errorMessage nvarchar(4000),
@errorSeverity int

SELECT @errorMessage = ERROR_MESSAGE(),
@errorSeverity = ERROR_SEVERITY()

RAISERROR(@errorMessage, @errorSeverity, 1)
GO

DECLARE variables

in a stored procedure

declare @errorMessage nvarchar(4000),
@errorSeverity int

DECLARE
@ErrorMessage nvarchar(4000),
@ErrorNumber int,
@ErrorSeverity int,
@ErrorState int,
@ErrorLine int,
@ErrorProcedure nvarchar(200)

Add new columns to a table

-- add 2 columns
ALTER TABLE Users
ADD MinAmount decimal(18,2) NULL,
MaxAmount decimal(18,2) NULL
GO

related:
http://fcmendoza02.blogspot.com/2008/07/add-new-not-null-column.html

Remove column from table

-- Delete column
ALTER TABLE Users
DROP COLUMN Nickname
GO

Related:
http://fcmendoza02.blogspot.com/2008/11/remove-fk-from-table.html

Time Only

declare @date DateTime

set @date = GETDATE()

SELECT LEFT(CONVERT(VARCHAR(8), @date, 8), 5) AS TimeOnly

/*
TimeOnly
--------
15:04
*/

Grant Event Log permissions to an account

On Windows 2003 Server
go to HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\EventLog:



Then add permissions to a specific account.

related link: http://blogs.objectsharp.com/cs/blogs/bruce/archive/2003/11/03/180.aspx