Here is *EXACTLY* how I got it all to work in VWD Express 2005.
(1) In the Database Explorer, I made sure I had a connection to the database I wanted to add the tables and SPs to. It just so happened that I had SQLExpress database named, what else, "test.dbo".
(2) I clicked on that DB in the Database Explorer to open it.
(3) I clicked on Stored Procedures within that DB to be sure I would be stepping on a previously created SP.
(4) I RIGHT-CLICKED on the line "Stored Procedures" and, from the mini popup menu, clicked on "Add New Stored Procedure."
(5) In the window that pops up to allow you to create a SP, I entered the following:
Code:
CREATE PROCEDURE dbo.MakeTables
AS
CREATE TABLE dbo.Complainants(
complainantID INT IDENTITY PRIMARY KEY,
lastname NVARCHAR(50),
firstname NVARCHAR(50)
)
CREATE TABLE dbo.Offenders(
offenderID INT IDENTITY PRIMARY KEY,
lastname NVARCHAR(50),
firstname NVARCHAR(50)
)
CREATE TABLE dbo.DutyOfficers(
officerID INT IDENTITY PRIMARY KEY,
lastname NVARCHAR(50),
firstname NVARCHAR(50)
)
CREATE TABLE dbo.ComplaintStatuses(
statusID INT IDENTITY PRIMARY KEY,
statusText NVARCHAR(200)
)
CREATE TABLE dbo.Complaints(
complaintID INT IDENTITY PRIMARY KEY,
complainantID INT REFERENCES Complainants(complainantID),
offenderID INT REFERENCES Offenders(offenderID),
officerID INT REFERENCES DutyOfficers(officerID),
statusID INT REFERENCES ComplaintStatuses(statusID),
complaintDesc NTEXT,
recommendation NTEXT
)
(6) I clicked the SAVE button.
(7) I right-clicked on the name of the SP ("MakeTables") and then clicked on "Execute". Presto, all the tables described there were created, including all the relationships.
(8) Back to the line that says "Stored Procedures" and RIGHT CLICK again and again choose "Add New Stored Procedure."
(9) In the window that pops up to allow you to create a SP, I entered the following:
Code:
ALTER Procedure AddFullComplaint(
@complainant_last NVARCHAR(50),
@complainant_first NVARCHAR(50),
@offender_last NVARCHAR(50),
@offender_first NVARCHAR(50),
@officer_last NVARCHAR(50),
@officer_first NVARCHAR(50),
@statusText NVARCHAR(200),
@complaintDesc NTEXT,
@Recommendation NTEXT
) AS
DECLARE @complainantid INT, @offenderid INT, @officerid INT, @statusid INT
SET NOCOUNT ON
SELECT @complainantid = complainantID FROM dbo.Complainants
WHERE lastname = @complainant_last AND firstname = @complainant_first
IF ISNULL(@complainantid,0) = 0
BEGIN
INSERT INTO Complainants( lastname, firstname ) VALUES(@complainant_last,@complainant_first)
SELECT @complainantid = SCOPE_IDENTITY()
END
SELECT @offenderid = offenderID FROM dbo.Offenders
WHERE lastname = @offender_last AND firstname = @offender_first
IF ISNULL(@offenderid,0) = 0
BEGIN
INSERT INTO dbo.Offenders( lastname, firstname ) VALUES(@offender_last,@offender_first)
SELECT @offenderid = SCOPE_IDENTITY()
END
SELECT @officerid = officerID FROM dbo.DutyOfficers
WHERE lastname = @officer_last AND firstname = @officer_first
IF ISNULL(@officerid,0) = 0
BEGIN
INSERT INTO dbo.DutyOfficers( lastname, firstname ) VALUES(@officer_last,@officer_first)
SELECT @officerid = SCOPE_IDENTITY()
END
SELECT @statusid = statusID FROM dbo.ComplaintStatuses
WHERE statusText = @statusText
IF ISNULL(@statusid,0) = 0
BEGIN
INSERT INTO dbo.ComplaintStatuses( statusText ) VALUES(@statusText)
SELECT @statusid = SCOPE_IDENTITY()
END
INSERT INTO Complaints( complainantID, offenderID, officerID, statusID, complaintDesc, recommendation )
VALUES( @complainantid, @offenderid, @officerid, @statusid, @complaintDesc, @recommendation )
RETURN @@IDENTITY
(Not as pretty as using the functions, but it's actually in total a tad less code.)
(10) I clicked the SAVE button.
Now the mildly fun part:
(11) I RIGHT-CLICKED on the name of the database (my "test.dbo" but use whatever matches your scenario) and chose "New Query".
(12) The ad hoc query window opens up. I tried two different queries:
Code:
AddFullComplaint 'Whiner', 'Wilma', 'Joe', 'Sloppy', 'Bach', 'PDQ', 'awaiting court date', 'he never mows his yard', 'buy him a goat'
AddFullComplaint 'Whiner', 'Wilma', 'Doll', 'Barbie', 'Bach', 'PDQ', 'dismissed', 'she shows off her legs', 'buy Wilma a curtain'
Don't worry that the first time you try to execute a SP like that you get a message that it can't be parsed, or words to that effect. Doesn't matter. Just continue and it will work, just fine.
(13) Go look in the various tables, Complainants, Offenders, etc. You will see that when the Complainant is repeated, no new entry is created. When the offender is a new offender, a new entry *IS* created. Etc.
*** IT WORKS ***
'nuff said?
TRY THIS !!!!
