Stored Procedure Question 1

[darude]

Hi All,
I am new stored procedures and want to write one that:

First, check to see if a Contact already exists in our data

If not, then I want to do an append query

If it does, then I want to send back a message that "contact already exists"

Thank you in advance.

 

[RiverGuy]

Is the stored procedure going to be used in an application, or is it something that is going to be executed from a Query Analyzer/Management Studio query window?

 

[darude]

It will be called from a MS Access application.

 

[RiverGuy]

Well, something like the following would work:

CREATE PROCEDURE AddContract
@ContractNumber INT
AS

IF EXISTS (SELECT 1 FROM Contracts WHERE ContractNumber = @ContractNumber)
  BEGIN
    SELECT 0 AS RowsInserted
  END
ELSE
  BEGIN
    INSERT INTO Contracts (ContractNumber) SELECT @ContractNumber
    SELECT 1 AS RowsInserted
  END

Then, if you're calling this sproc from VBA within Access, the pseudocode would look something like:

Dim RowsInserted As Integer
RowsInserted = <scalar result from your stored procedure>
If RowsInserted = 0 Then
  MsgBox("This contract already exists")
End If

 

[darude]

Fantastic! Thank you.

 

[darude]

What does the SELECT 1 mean?

 

[RiverGuy]

Just a dummy value to tell you how many records were inserted. If this was an update stored procedure, you could use the @@RowCount statement to get the number of rows updated. However, since your stored procedure is probably going to be inserting just one record at a time, selecting the constant 1 will suffice.

 

[darude]

Great. Thank you.

 

[darude]

Okay. So I hate to bother you but. Now I want to get the staffID of the current user to add to the INSERT. So, here's my stored proc.



ALTER PROCEDURE procAppendToStudMR
@StudentID varchar(10)
AS
IF EXISTS
(SELECT StudMRStudentID FROM StudMR WHERE StudMRStudentID = @StudentID)
BEGIN
SELECT 0 as RowInserted
END
ELSE
BEGIN

INSERT INTO StudMR (StudMRStudentID, StudMRFirstname, StudMRLastname, StudMRStaffID)
SELECT StudentID, Firstname, Lastname, ArchiveSchoolID,
FROM vw_ArchiveStudents
WHERE StudentID = @StudentID
SELECT 1 as RowInserted
END

 

[RiverGuy]

Where is the StaffID stored? If it is in a table other than vw_ArchiveStudents, you could do something like

DECLARE @StaffID INT
SELECT @StaffID = StaffID FROM SomeTable WHERE User = @CurrentUser

Then, use it in your INSERT statement

     INSERT INTO StudMR (StudMRStudentID, StudMRFirstname,  StudMRLastname, StudMRStaffID)                                                                                                                                         
    SELECT StudentID, Firstname,     Lastname, ArchiveSchoolID,  @StaffID       
     FROM vw_ArchiveStudents
     WHERE StudentID = @StudentID

 

[darude]

Yes. It is stored in the Staff table. So I would
get the currentuser based on Staff.Username to get
back the Staff.Staffid.

 

[darude]

Perfect. Thank you.

 

[ESquared]

The accepted syntax for EXISTS() is to use SELECT *, not SELECT 1.

 

[RiverGuy]

The accepted syntax for EXISTS() is to use SELECT *, not SELECT 1

Accepted by who? I don't remember getting a memo on the subject.

 

[bborissov]

http://decipherinfosys.wordpress.co...-1-vs-a-select-in-the-existsnot-exists-query/

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[ESquared]

RiverGuy,

I didn't mean to step on your toes.

In any case, thanks to Borislav and some further research, I stand corrected!

It is better to use SELECT 1 than SELECT * because SELECT * makes the query compilation just a little more expensive by making it expand * into all the columns in the table, then it throws the column names away.

EXISTS Subqueries: SELECT 1 vs. SELECT *