Storing the NewSequentialID() after Inserting using the Output Clause

A little while ago i wrote this post about returning a NewSequentialID() after inserting a row… Sometimes though you want to be able to do something with that ID within the stored procedure that you are in the scope of…

You can use the output clause to insert certain columns in the inserted table into a temporary table. For example:

The table we are working with…

CREATE TABLE dbo.junk (
      id uniqueidentifier NOT NULL
        DEFAULT newsequentialid()
    , name varchar(100)
)
GO

CREATE TABLE dbo.junk_history (
      id uniqueidentifier NOT NULL
    , name varchar(100)
    , date datetime DEFAULT getutcdate()
)
GO

The first table is the table we are inserting into, the second table we want to have filled with the newly inserted values including the id column…

The Stored procedure we have is as follows:

CREATE PROCEDURE dbo.junk_insert
    @name nvarchar(100)
AS
BEGIN
    INSERT INTO dbo.junk (name)
        OUTPUT inserted.id, inserted.name INTO dbo.junk_history (id, name)
        SELECT @name
END
GO

In the insert statement I’m getting the id and name from the row inserted and inserting that into the junk_history table… You can insert the data into a temporary table variable or an actual table.

DECLARE	@return_value int

EXEC	@return_value = [dbo].[junk_insert]
		@name = N'Jimbo Jumbalock'

SELECT	'Return Value' = @return_value

GO

SELECT * FROM dbo.junk
SELECT * FROM dbo.junk_history

The above script calls the sproc and shows you the contents of both tables…

Note: that this was a very quickly put together script to get an example working, so please ignore any weirdness 🙂

References:

Adding new Table Constraints to Already Existing Tables

Below is an Sql Server script showing examples of how to create primary key, foreign key, unique and default constraints on on a table that already exists.

See here for example of how to do constraints in the CREATE TABLE statement itself.

Update: Added Index (unique)

Update 2010-01-16: Added notes about composite primary key (primary key including multiple columns) . Same with UNIQUE constraint. Added reference to another post I’ve done on similar topic

Adding Constraints After Table Creation.sql

— Drop tables if they exist
IF EXISTS (SELECT 1 FROM sys.objects  WHERE object_id = OBJECT_ID(N’dbo.Dog’))
BEGIN
    DROP TABLE dbo.Dog;
    PRINT ‘Dropped: dbo.Dog’;
END

IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N’dbo.DogTypes’))
BEGIN
    DROP TABLE dbo.DogTypes;
    PRINT ‘Dropped: dbo.DogTypes’;
END
GO

 

— Create tables
CREATE TABLE dbo.DogTypes (
    [Type] CHAR(1) NOT NULL
)

— Insert ‘F’at, ‘S’kinny and ‘A’norexic. :o( 
INSERT INTO dbo.DogTypes 
    SELECT ‘F’ UNION ALL SELECT ‘S’ UNION ALL SELECT ‘A’

CREATE TABLE dbo.Dog (
    DogID uniqueidentifier NOT NULL,
    [Type] CHAR(1) NOT NULL,
    [RegoNo] int NOT NULL
);
GO

 

/*
    Add Primary Key Constraints
*/
ALTER TABLE dbo.DogTypes
ADD CONSTRAINT PK_DogTypes_Type PRIMARY KEY ([Type]);

ALTER TABLE dbo.Dog
ADD CONSTRAINT PK_Dog_DogID PRIMARY KEY ([DogID]);

Note: Inside PRIMARY KEY () you can add a comma delimited list of columns for your composite key.

/*
    Add Foreign Key Constraint
*/
ALTER TABLE dbo.Dog
ADD CONSTRAINT FK_Dog_Type FOREIGN KEY ([type])
    REFERENCES dbo.DogTypes([Type])

/*
    Add Default Constraint
*/
ALTER TABLE dbo.Dog
ADD CONSTRAINT DF_Dog_DogID DEFAULT (NewSequentialID()) FOR [DogID];

/*
    Add unique constraint
*/
ALTER TABLE dbo.Dog
ADD CONSTRAINT U_Dog_RegoNo UNIQUE([RegoNo])
GO

Note: Inside UNIQUE() you can add a comma delimited list of columns

/*
    Add Clustered Index
    Note: You wouldn’t do this normally, Just as an example!!!
*/
CREATE UNIQUE CLUSTERED INDEX I_Dog_DogID
    ON [dbo].[Dog](DogID);
GO

EXCEPT operator in Sql Server 2005+

I came across the EXCEPT operator in Sql Server 2005 and went’ and did a little bit of searching.

Pinal Dave has a nice simple example and shows that the execution plan between EXCEPT and NOT IN is the same making it no different except in how you code it obviously.

http://blog.sqlauthority.com/2007/05/22/sql-server-2005-comparison-except-operator-vs-not-in/

IT seems the Except operator can work over multiple columns it seems. So it tests all of the columns in both sides of the EXCEPT which makes it somewhat of an easily avoidable keyword it seems…(That is there seems to be other ways that you can do the same things without having to learn this new keyword, even though it is rather simple)

Basic Transaction Template

Here is a simple Transaction template for Sql Server 2005+

BEGIN TRANSACTION;
BEGIN TRY
: – code that could throw an error here!
END TRY
BEGIN CATCH
    EXEC RethrowError @@ProcID
    IF (@@TRANCOUNT > 0)
        ROLLBACK TRANSACTION;
END CATCH;

IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;
 

The RethrowError is a stored procedure call. This stored procedure would then take the error details and throw an error, though you might want to name the stored procedure differectly!

The RethrowError stored procedure will then copy the values from the ERROR_* functions to variables and call RAISERROR with the appropriate values. It essentially saves you doing the same thing in the catch block!

Updating Foreign Keys upon Primary Key changing in Sql Server

I’ve been doing a little (lot) of reading and wanted to try something I’ve never actually used in Sql Server. That is cascade updates.

The scenario is this!

  1. I have a table that has a primary key…. wow hey!
  2. I have another table that has a foreign key that references the primary key
  3. I want to update the primary key and have the foreign keys change their values

In this case the primary key is simply going to be a gender which will be a key that is char(10)

Please ignore my use of IDENTITY…  if you don’t like it… I’m beginning to understand you I think. I also didn’t really need it for the example. I added it early on, then changed a lot of things. I’m not going to remove it now.

/* This script shows how you can automatically update Foreign keys when you change a primary key. ********************************** IMPORTANT ********************************** I would create a test database to run this script on. It creates 3 tables and then deletes them. ******************************************************************************* */ /* Create tables */ -- Contains the list of keys that the Person and Contact table will -- be constrained to have in the Gender column... No other value can be put there CREATE TABLE GenderTypes ( Gender CHAR(10) PRIMARY KEY ) -- List of People. Has a Gender CREATE TABLE Person ( PersonID int PRIMARY KEY IDENTITY, Gender CHAR(10) FOREIGN KEY REFERENCES GenderTypes(Gender) ON UPDATE CASCADE ) -- List of Contacts. Has a Gender CREATE TABLE Contact ( ContactID int PRIMARY KEY IDENTITY, Gender CHAR(10) FOREIGN KEY REFERENCES GenderTypes(Gender) ON UPDATE CASCADE ) GO /* Insert some data into the tables. */ -- These are the catagories. Male, Female and Unknown (I know they are not -- really categories, but you will get the idea!) INSERT INTO GenderTypes(Gender) VALUES('Male') INSERT INTO GenderTypes(Gender) VALUES('Female') INSERT INTO GenderTypes(Gender) VALUES('Unknown') -- Create People with one of each Gender INSERT INTO Person(Gender) VALUES('Male') INSERT INTO Person(Gender) VALUES('Female') INSERT INTO Person(Gender) VALUES('Unknown') -- Create Contacts with one of each Gender INSERT INTO Contact(Gender) VALUES('Male') INSERT INTO Contact(Gender) VALUES('Female') INSERT INTO Contact(Gender) VALUES('Unknown') GO /* Change the "Unknown" Gender in the Categories table and watch the foreign keys change! */ -- Before the change SELECT s.PersonID, s.Gender AS SHGender, gt.Gender as Gender FROM Person s JOIN GenderTypes gt ON s.Gender = gt.Gender -- Change the Unknown Gender to UNKNOWN G UPDATE GenderTypes SET Gender = 'UNKNOWN G' WHERE Gender = 'Unknown' -- See if the Gender key has changed in both of the foreign keys that point to the -- GenderTypes tabels primary key SELECT s.PersonID, s.Gender AS SHGender, gt.Gender as Gender FROM Person s JOIN GenderTypes gt ON s.Gender = gt.Gender SELECT c.ContactID, c.Gender AS SHGender, gt.Gender as Gender FROM Contact c JOIN GenderTypes gt ON c.Gender = gt.Gender GO -- This just makes a bit of a division between the 6 results sets returned (well the -- (6 important ones!) SELECT '--------------- DIVIDER ----------------' GO /* Change the Gender back again and watch the foreign keys change! */ SELECT p.PersonID, p.Gender AS SHGender, gt.Gender as Gender FROM Person p JOIN GenderTypes gt ON p.Gender = gt.Gender -- Change the UNKNOWN G Gender back to Unknown UPDATE GenderTypes SET Gender = 'Unknown' WHERE Gender = 'UNKNOWN G' -- See if the Gender key has changed in both of the foreign keys that point to the -- GenderTypes tabels primary key. Should be back to Unknown. SELECT p.PersonID, p.Gender AS SHGender, gt.Gender as Gender FROM Person p JOIN GenderTypes gt ON p.Gender = gt.Gender SELECT c.ContactID, c.Gender AS SHGender, gt.Gender as Gender FROM Contact c JOIN GenderTypes gt ON c.Gender = gt.Gender GO -- Drop the tables! DROP TABLE Contact DROP TABLE Person DROP TABLE GenderTypes GO

The above is commented and I hope that they explain what is happening.

We’ve got the GenderTypes table which contains one column called Gender with a CHAR(10) data type. This column is the primary key.

We’ve got two tables called Contact and Person which basically have the same structure. In the declarations for the table I’ve added foreign key constrains to the GenderTypes tables Gender column. and also put the ON UPDATE CASCADE.

This will result in the following:

  1. The Person.Gender and Contact.Gender columns can only contain values that are in the GenderTypes table.
  2. When one of the rows in the GenderTypes is changed (That is the primary key is changed) the Person.Gender and Contact.Gender columns will be updated for every Foreign key that matched the primary key on the GenderTypes table before the update.

Concatenating Sql Scripts

To concatenate Sql Scripts into one big sql script I usually use the following rules and a batch file:

  • Always put a “GO” at the end of each script. This ensures that one script doesn’t blend with another one in the same batch.
  • Always put at least one blank line at the end and start of the script (I just do this to prevent concatenation of files resulting in the GO and something else joining together and causing an error)
  • Each file needs to be in the same encoding. See here
  • I try and order the scripts by putting a number at the start of each script. The number will help keep them in order in Explorer and also will mean they should concatenate in the correct order

I create a batch file called Concatenate.bat which has the following contents:

DEL @MyBigScript.sql
COPY /A *.sql @MyBigScript.sql

This removes the file called @MyBigScript.sql and then concatenates all .sql files into a new file called @MyBigScript.sql.

Update: The @ at the start of the filename will make the @MyBigScript.sql file show at the top of Explorer.

Saving an Sql Script in a Different Encoding

In some situations I create scripts and store them in a folder. Later I will concatenate the contents using a batch file which doesn’t work very well if the files do not use the same encoding.If you have one in Codepage 1200 and another in Codepage 1252 then you will have some very odd characters in your resulting script

When writing a script in Sql Server Management Studio, you can save them with different encoding. I learnt the idea of a previous employee at work and found it useful.

By default Management Studio seems to want to save new scripts (press “New Query” button) as “Western European (Windows) – Codepage 1252”, yet when you click “Modify” to edit a stored procedure, for example, and then try and save the script it will, by default, save as “Unicode – Codepage 1200″… and these don’t concatenate together very well as already explained.

There seems to be know way within Management Studio to set a default for this, so unless I’m mistaken, we are stuck doing things manually. Here is how:

When you have the Save File dialog, click the little drop down box at the right of the “Save” button, and click “Save with Encoding…” option.

Sql Management Studio - Save with Encoding...

Sql Management Studio - Save with Encoding...

You can now select the type of encoding.

I had done a fair bit of work with Management Studio before I came across this small drop down on the save button. It’s not really obvious as I was expecting a drop down box on the actual dialog. Hopefully this will be helpful to someone else also.