Writing text lines to a file

using (System.IO.StreamWriter sw = new System.IO.StreamWriter(@"c:\TestFile.txt", true))
{
sw.WriteLine(rawString);
}

Foreign Key

Image


ALTER TABLE ChildTable
ADD CONSTRAINT LoremIpsum
FOREIGN KEY (ChildColumn) REFERENCES ParentTable (ParentColumn)


ALTER TABLE [dbo].[UserProducts]
ADD CONSTRAINT [FK_UserProducts_Users]
FOREIGN KEY (UserID) REFERENCES [dbo].[Users](UserID)

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