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:

Advertisements

Sql Server 2008 – Spatial Data

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)

Firewall Rule for Sql Server 2005 on Windows 7

I have Sql Server 2005 installed on Windows 7 and have been trying to get a remote web application to connect to a database on my Windows 7 machine. I have been prevented from doing so while the firewall is on.

It was bit difficult to find the firewall rules and add the correct rule. The following process seems to work for me: (Information was found here under the “To access a program through the Windows Firewall” )

  1. Open the Control Panel
  2. Click “System and Security” if you are in “Category” view mode.
  3. Click “Windows Firewall”. (The green text)
  4. Click “Advanced settings” along the left hand side.
  5. Click “Inbound Rules” along the left hand side.
  6. Click “New Rule…”  to the right in the actions pane.
  7. Ensure “Program” is selected and click “Next >”
  8. Select “This program path:” radio button and click browse.
  9. You need to select the sqlservr.exe file which for my 2005 instance was located “C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn”
  10. Click “Open”
  11. Click “Next >”
  12. Click “Next >” again
  13. Click “Next >” again
  14. Give it a name of “Sql Server 2005” or another appropriate name. I would suggest giving the service name maybe. I Called it this “Sql Server: Sql2005“
  15. Click “Finish”

You may need to also add the Sql Browser service so that any named instances can be found.

  1. Click “New Rule…” again in the actions pane.
  2. Select “Port” and click “Next >”
  3. Select “UDP”
  4. Select “Specific local ports” and enter the port number 1434
  5. Click “Next >”
  6. Click “Next >” again
  7. Click “Next >” again
  8. Enter a name like : “Sql Server Browser”

Sql Server 2005/2008 Object Definition – Getting all the contents of a stored procedure etc…

I thought you had to go to the sys.syscomments table to do this:

SELECT * FROM sys.procedures
WHERE [name] Like ‘GetAllElephants’
ORDER BY [name]

The above would return the stored procedure with the name ‘GetAllElephants’. one of the columns will contain the ID (object_id column) which you then pass to the OBJECT_DEFINTION function as shown below!

SELECT OBJECT_DEFINITION(1409815502);

This will then return all of the contents of the stored procedure … that is the actual code that IS the stored procedure.

Or you could just do this:

SELECT OBJECT_DEFINITION(OBJECT_ID(N’GetAllElephants’));

You can do this with

  • Stored Procedures
  • Views
  • Table Valued Functions
  • Scarlar Valued functions
  • Triggers
  • Inline table Valued Functions
  • and few other constraints and replication related items.. (see documentation below)

OBJECT_DEFINITION Documentation

Sql Server 2005

Sql Server 2008

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!