CRUD Stored proceedures for GUID PK FK using newsequentialid()

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; 

Select allOpen in new window

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

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.

gday mate,

I ran ur script and it runs fine but I am having trouble splitting it and making real sp

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....

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.

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.

one more question do I need to worry about WITH (ROWLOCK) on the update procedure??

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.

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....

https://www.experts-exchange.com/questions/25893862/Add-new-columns-to-database-during-runtime.html

https://www.experts-exchange.com/questions/25893818/Dynamicly-Add-controls-to-web-from-from-sql.html