Difference between revisions of "DD Class10"
(→Single SQL SProc) |
(→Single SQL SProc) |
||
Line 229: | Line 229: | ||
GO | GO | ||
+ | </sql> | ||
+ | |||
+ | and the SQL statement to call the stored procedure | ||
+ | |||
+ | <sql> | ||
+ | AddUserAndRole | ||
+ | @salutationID = 1, | ||
+ | @firstName = 'fnTransTest' , | ||
+ | @lastName ='lnTransTest', | ||
+ | @logonName ='lnTransTest', | ||
+ | @passwd ='pwTransTest', | ||
+ | @roleTypeID = 1 | ||
</sql> | </sql> | ||
Revision as of 03:42, 5 April 2007
Contents
Create a new user account
To insert into a new user we need to to combine three steps.
- INSERT into the People Table
- Get the new PeopleID
- INSERT into the roles table
<sql> INSERT into People (salutationID, firstName, lastName, logonName, passwd) values (1, 'jeff', 'meyers', 'jmeyers', 'monkey') </sql>
Check the new users PeopleID
<sql> SELECT PeopleID from People WHERE firstName = 'jeff' and lastName='meyers' and logonName='jmeyers' </sql>
results PeopleID = 1
Give the new user a Role by inserting the PeopleID and a roleID into the roles table
<sql> INSERT into roles ( PeopleID, roleTypeID, RoleActive ) values ( 1, 1, 1) </sql>
Since all of these steps need to happen or fail as a complete unit we must use an transaction. Of course in .NET there are several way to acomplish this.
Test page one
Test page one with form
Stored Porcedures
Default Template
<sql> -- ================================================ -- Template generated from Template Explorer using: -- Create Procedure (New Menu).SQL -- -- Use the Specify Values for Template Parameters -- command (Ctrl-Shift-M) to fill in the parameter -- values below. -- -- This block of comments will not be included in -- the definition of the procedure. -- ================================================ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: <Author,,Name> -- Create date: <Create Date,,> -- Description: <Description,,> -- ============================================= CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName> -- Add the parameters for the stored procedure here <@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>, <@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0> AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2> END GO </sql>
SProc to INSERT into people
<sql> -- ================================================ -- Template generated from Template Explorer using: -- Create Procedure (New Menu).SQL -- -- Use the Specify Values for Template Parameters -- command (Ctrl-Shift-M) to fill in the parameter -- values below. -- -- This block of comments will not be included in -- the definition of the procedure. -- ================================================ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: Jeff -- Create date: -- Description: -- ============================================= CREATE PROCEDURE PeopleInsert -- Add the parameters for the stored procedure here @salutationID int = 1, @firstName varchar(50), @lastName varchar(50), @logonName varchar(50),
@passwd varchar(50)
AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. -- SET NOCOUNT ON;
-- Insert statements for procedure here
INSERT INTO People (salutationID, firstName, lastName, logonName, passwd) VALUES (@salutationID, @firstName, @lastName, @logonName, @passwd) END GO </sql>
test by running
<sql> PeopleInsert
@salutationID = 1, @firstName ='sprocTestFname', @lastName='sprocTestLname', @logonName ='sprocTestLogonName',
@passwd ='monkey'
</sql>
all one line is fine
<sql> PeopleInsert @salutationID = 1, @firstName ='sprocTestFname', @lastName='sprocTestLname', @logonName ='sprocTestLogonName', @passwd ='monkey' </sql>
GetPeopleID
<sql>
CREATE PROCEDURE GetPeopleID
-- Add the parameters for the stored procedure here
@firstName varchar(50),
@lastName varchar(50),
@logonName varchar(50)
AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT PeopleID FROM People WHERE firstName = @firstName AND lastName= @lastName AND logonName= @logonName END </sql>
Again we can runn it like this <sql> GetPeopleID @firstName ='sprocTestFname', @lastName='sprocTestLname', @logonName ='sprocTestLogonName' </sql>
Transactions
Whenever you are making changes (insert/update/delete) two more than one table or the results of a select statement are used to update another table you need to use a tracation.
Locks
Commit
Rollback
Single SQL SProc
full InsertPersonAndRole
<sql> -- ============================================= -- Author: jeff -- Create date: -- Description: -- ============================================= CREATE PROCEDURE AddUserAndRole -- Add the parameters for the stored procedure here @salutationID int = 1, @firstName varchar(50) , @lastName varchar(50), @logonName varchar(50), @passwd varchar(50), @roleTypeID int AS BEGIN --Start transaction BEGIN TRANSACTION;
--INSERT into People
INSERT into People
(salutationID, firstName, lastName, logonName, passwd)
values
(@salutationID, @firstName, @lastName, @logonName, @passwd)
--Get PeopleID DECLARE @PeopleID int
Select @PeopleID = @@identity
--INSERT into Roles INSERT into roles (PeopleID, RoleTypeID) values (@PeopleID, @roleTypeID)
--Commit Transaction ROLLBACK TRANSACTION END GO
</sql>
and the SQL statement to call the stored procedure
<sql> AddUserAndRole @salutationID = 1, @firstName = 'fnTransTest' , @lastName ='lnTransTest', @logonName ='lnTransTest', @passwd ='pwTransTest', @roleTypeID = 1 </sql>
Stored Procedures
Fourth Normal Form
Fourth Normal Form
- separates independent multi-valued facts stored in one table into separate tables.
Fifth Normal Form
Fifth Normal Form
- breaks out data redundancy that is not covered by any of the previous normal forms.
HomeWork
Course adder example
http://iam.colum.edu/dd/classsource/class8/student.aspx
SQL Subquery
<sql>SELECT CourseID, CourseName, CourseNumber FROM Course WHERE (
CourseID not in ( SELECT CourseID FROM StudentsCourses_vw WHERE StudentID = @StudentID )
) </sql>