CRUD Stored proceedures for GUID PK FK using newsequentialid()

hello experts,

I was wondering how do I write stored procs in sql server 2008 for CRUD that use GUID as pk and fk with newsequentialid()...

As well as a stored procedure script to update a table in a different db on same server with the same pk fk configuration and create new schema,tables and stored procs when other table is updated ?

sample scripts would be most appreciated.
LVL 7
XGISAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

BodestoneCommented:
The below code shows a sample procedure which covers basic CRUD combined in one depending on which parameters are supplied. It's based on very simple parent/child with the PK/FK as UNIQUEIDENTIFIER.

There is no error handling though, since it is just an example and can probably be broken quite easily by mucking about with different params.

As for creating new schema items every time a table is updated that would have the be one for another question probably but may I ask why you would need to do so? It may be that there is a better way.
/**
*  Drop and create sample tables
**/
IF EXISTS (SELECT 1 FROM sysobjects WHERE xtype = 'u' AND name = 'ParentTable') BEGIN DROP TABLE ParentTable END;
IF EXISTS (SELECT 1 FROM sysobjects WHERE xtype = 'u' AND name = 'ChildTable') BEGIN DROP TABLE ChildTable END;
IF EXISTS (SELECT 1 FROM sysobjects WHERE xtype = 'p' AND name = 'TestProc') BEGIN DROP PROCEDURE TestProc END; 
GO
CREATE TABLE ParentTable(ParentID UNIQUEIDENTIFIER DEFAULT NEWID() NOT NULL, ParentName VARCHAR(50))
CREATE TABLE ChildTable(ChildID UNIQUEIDENTIFIER DEFAULT NEWID() NOT NULL, ParentID UNIQUEIDENTIFIER DEFAULT NEWID() NOT NULL, ChildName VARCHAR(50))
GO



CREATE PROCEDURE TestProc(@parentID UNIQUEIDENTIFIER, @childID UNIQUEIDENTIFIER, @parentName VARCHAR(50), @childName VARCHAR(50))
AS
-- Using a variable to create new ID on insert rather than relying on table default means it can be used late.
DECLARE @newID UNIQUEIDENTIFIER
--If no ID is supplied but a name is then assume a new parent record.
IF @parentID IS NULL AND @parentName IS NOT NULL
BEGIN 
  SELECT  @newID = NEWID()
  INSERT  ParentTable(ParentID, ParentName)
  SELECT  @newID, @parentName
END
--Name and ID provided means an update
ELSE IF @parentID IS NOT NULL AND @parentName IS NOT NULL
BEGIN
  UPDATE  ParentTable
  SET   ParentName = @parentName
  WHERE ParentID = @parentID
END
--ID but no name on the parent means a delete as long as there is no child name (indicating adding a child to an existing parent)
ELSE IF @parentID IS NOT NULL AND @parentName IS NULL AND @childName IS NULL
BEGIN
  --children first for dependency
  DELETE  ChildTable
  WHERE ParentID = @parentID
  DELETE  ParentTable
  WHERE ParentID = @parentID
END

--If no ID is supplied but a name is then assume a new child record.
IF @childID IS NULL AND @childName IS NOT NULL
BEGIN
  --Nothign to do if there is no parent to associate the new file with
  IF @parentID IS NOT NULL OR @newID IS NOT NULL
  BEGIN
    --unless you need it for more you can rely on the default for childID
    INSERT  ChildTable(ParentID, ChildName)
    SELECT  COALESCE(@newID,@parentID), @childName
  END
END
--Name and ID provided means an update
ELSE IF @childID IS NOT NULL AND @childName IS NOT NULL
BEGIN
  UPDATE  ChildTable
  SET   ChildName = @childName -- option for changing parentID , ParentID = COALESCE(@parentID, @newID, ParentID)
  WHERE ChildID = @childID
END
--ID but no name on the parent means a delete
ELSE IF @childID IS NOT NULL AND @childName IS  NULL
BEGIN
  DELETE  ChildTable
  WHERE ChildID = @childID
END


GO
DECLARE @testID UNIQUEIDENTIFIER
--Insert parent Barny with child Mortimer
EXEC TestProc NULL, NULL, 'Barny', 'Mortimer'
--Insert Childless Ermintrude
EXEC TestProc NULL, NULL, 'Ermintrude', NULL
--Give Barny a new child called Archibald
SELECT @testID = ParentID FROM ParentTable WHERE ParentName = 'Barny'
EXEC TestProc @testID, NULL, NULL, 'Archibold'

SELECT  p.ParentName, c.ChildName
FROM  ParentTable p
LEFT JOIN ChildTable c
  ON  c.ParentID = p.ParentID


/**
*  clean up
**/
IF EXISTS (SELECT 1 FROM sysobjects WHERE xtype = 'u' AND name = 'ParentTable') BEGIN DROP TABLE ParentTable END;
IF EXISTS (SELECT 1 FROM sysobjects WHERE xtype = 'u' AND name = 'ChildTable') BEGIN DROP TABLE ChildTable END;
IF EXISTS (SELECT 1 FROM sysobjects WHERE xtype = 'p' AND name = 'TestProc') BEGIN DROP PROCEDURE TestProc END; 

Open in new window

0
XGISAuthor Commented:
Cheers for the code snippet, what will happen is that when a user (company) creates a new profile and selects what databases they intend to use i need to record in my customers database who belongs to what schema and as the user specifies the schema name that gets written to the config database i need that info to record (via stored procedure) in my customers database after confirmed payment and one the record has been added (schemaId) to the customer in my db a create schema script or stored procedure will execute and create the users schema (with templated tables and some blanks for custom fields) accross the selected dbs (hopefully horizontal partitioning ).... I hope that makes sense lol this is my first attempt to build such a system...lol
0
BodestoneCommented:
I used a single procedure to demonstrate various aspects but often people like to separate creation, update and deletion procedures. It makes it clearer what is going on in each and makes it easier to add error handling but has the downside of having to maintain all 3 for things like schema changes (but 3 simple ones rather than 1 possibly messy one)

As for the schema creation I am assuming that the base DBO schema will contain the user data and then each client company will have its own schema.

That one is something that is pretty big and probably merits its own question. I've not had a lot of experience with templated DDL stuff myself and there may be a far easier way than snynamic sql. I think most applications would hold the template at the application level though.

You can easily script the database creation in SSMS by right clicking on the database and selecting Tasks\Generate Scripts.

Unfortunately elements like database, schema and object names can only be literal not variables but it may get you started.

0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

XGISAuthor Commented:
gday mate,

I ran ur script and it runs fine but I am having trouble splitting it and making real sp
0
BodestoneCommented:
Well it is a real procedure, just one that is deleted after it has been tested.
Here's a quick example (again with little in the way of error handling e.g. roll back and do not attempt to add child if adding parent fails) of one just for creation of a parent-child
/**
*  Drop and create sample tables
**/
IF EXISTS (SELECT 1 FROM sysobjects WHERE xtype = 'u' AND name = 'ParentTable') BEGIN DROP TABLE ParentTable END;
IF EXISTS (SELECT 1 FROM sysobjects WHERE xtype = 'u' AND name = 'ChildTable') BEGIN DROP TABLE ChildTable END;
IF EXISTS (SELECT 1 FROM sysobjects WHERE xtype = 'p' AND name = 'TestProc') BEGIN DROP PROCEDURE TestProc END; 
GO
CREATE TABLE ParentTable(ParentID UNIQUEIDENTIFIER DEFAULT NEWID() NOT NULL, ParentName VARCHAR(50))
CREATE TABLE ChildTable(ChildID UNIQUEIDENTIFIER DEFAULT NEWID() NOT NULL, ParentID UNIQUEIDENTIFIER DEFAULT NEWID() NOT NULL, ChildName VARCHAR(50))
GO


/**
*  Create the SP
**/
CREATE PROCEDURE TestProc(@parentName VARCHAR(50), @childName VARCHAR(50))
AS
--Just for adding
IF @parentName IS NULL OR @childName IS NULL
BEGIN
	--do nothign and return 1 (error free return is 0)
	RETURN 1;
END

DECLARE	@newID UNIQUEIDENTIFIER

SELECT	@newID = NEWID()
INSERT	ParentTable(ParentID, ParentName)
SELECT	@newID, @parentName

INSERT	ChildTable(ParentID, ChildName)
SELECT	@newID, @childName

GO
/**
*  SP ENDS HERE
*  this is just to test it
**/

EXEC TestProc 'Barny', 'Mortimer'
SELECT	p.ParentName, c.ChildName
FROM	ParentTable p
LEFT JOIN	ChildTable c
	ON	c.ParentID = p.ParentID


/**
*  clean up
**/
IF EXISTS (SELECT 1 FROM sysobjects WHERE xtype = 'u' AND name = 'ParentTable') BEGIN DROP TABLE ParentTable END;
IF EXISTS (SELECT 1 FROM sysobjects WHERE xtype = 'u' AND name = 'ChildTable') BEGIN DROP TABLE ChildTable END;
IF EXISTS (SELECT 1 FROM sysobjects WHERE xtype = 'p' AND name = 'TestProc') BEGIN DROP PROCEDURE TestProc END; 

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
XGISAuthor Commented:
I am sorry I don't mean to sound dense but I am still not understanding how to create insert and update and read stored procedures....
0
BodestoneCommented:
OK, assuming a one column table called Names. One column: Name.

CREATE Procedure AddNewName (@name VARCHAR(50))
AS
INSERT Names
SELECT @name

-----------------------------------------------------------------------------------

CREATE Procedure DeleteName (@name VARCHAR(50))
AS
DELETE Names
WHERE Name = @name

-----------------------------------------------------------------------------------

CREATE Procedure ChangeName (@oldName VARCHAR(50), @newName VARCHAR(50))
AS
UPDATE Names
SET Name =  @newName
WHERE Name = @oldName

-----------------------------------------------------------------------------------

Usage:
EXEC AddNewName 'Fred'
EXEC DeleteName 'Barney'
EXEC ChangeName 'Mr Apollo', 'Madam Butterfly'

That's basically it at it's simplest. No linked tables, no ID columns and no unique constraints so you could add 500 marvins and if you ran the changename they would all change but it goves the very basics.
Sorry if I assumed a better understanding than you have just now but the initial question of joining tables on UIDs and so on kind of indicated it. We can build up from here though.
0
XGISAuthor Commented:
lol .... I have intermediate understanding as all of my experience is with identity columns and I was on fused at the fieldName uniqueidentifier OUTPUT and the whether or not I had to deal with the SCOPE_Identity......

and as this Is my first time with GUID PK i was a little confused

but as you said it is simple it just dawned on me that all I had to do was check out the asp.net membership stored procedures lol cheers for your patience.
0
XGISAuthor Commented:
one more question do I need to worry about WITH (ROWLOCK) on the update procedure??
0
BodestoneCommented:
Chances are it is not absolutely necessary to explicitly specify the rowlock. On update statements it should be the default locking level as an absolute minimum anyway.

Yes, I generally deal with identities rather than UIDs jsut because they are a damned site easier. Also, good call on ripping into built in SPs to get code. It's a large part of how I tought myself SQL before signing up for foums and learning how to do it better and more efficiently but it is a great way to see how it all fits together and getting your hands dirty near the start is no bad thing.
0
XGISAuthor Commented:
Bodestone: since you seem highly competant in sql I was wondering if you would like to have a look at this question but it is not only sql....

http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/Q_25893862.html

http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/Q_25893818.html
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.