Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
BOOK: SQL Functions Programmer's Reference
This is the forum to discuss the Wrox book SQL Functions Programmer's Reference by Arie Jones, Ryan K. Stephens, Ronald R. Plew, Robert F. Garrett, Alex Kriegel; ISBN: 9780764569012
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: SQL Functions Programmer's Reference section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
 
 
Thread Tools Display Modes
  #11 (permalink)  
Old February 17th, 2009, 02:22 AM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default TRY THIS !!!!

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?
  #12 (permalink)  
Old February 17th, 2009, 02:26 AM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

N.B.: In place of step 12, there, you could instead RIGHT CLICK on the name of the SP, "AddFullComplaint" in the list of stored procedures and then click the "Execute" mini menu item.

You will be presented with a dialog box where you can type in an argument value for each of the parameters. If you use the same values I used in my ad hoc query, the results will be the same.

But play with it!
  #13 (permalink)  
Old July 25th, 2012, 08:39 AM
Registered User
Points: 3, Level: 1
Points: 3, Level: 1 Points: 3, Level: 1 Points: 3, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2012
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Any dataset would do. I don't want a search site. I want a direct link to the dataset. I need this for a data mining project.

Click here to obtain more information slimvida
 


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Fill a DataSet from another DataSet kamranzafar C# 1 February 7th, 2007 11:14 AM
Fill(dataset) or dataset.load() salemkoten SQL Server 2005 1 November 2nd, 2006 11:04 PM
Converting a untyped dataset to a typed dataset daphnean Visual Studio 2005 0 July 13th, 2006 02:16 AM
Copy dataset to another dataset kapila VB.NET 2 November 13th, 2005 06:25 AM
Re: SQL Server dataset to ACCESS dataset dazzer ADO.NET 0 March 22nd, 2004 05:28 AM



All times are GMT -4. The time now is 08:22 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.