Writing a probably simple stored Procedure

[AndyFutureRoute]

Hello

I don't know really where to startw ith writing a stored procedure.

I need it to return the characterID (an automayically icrementing number) of the record i am going to insert.

So, insert a record and return the ID of it...simple i know, i can do it wth an insert and then a select, but how do i put one of these into a stored procedure?!

exmaples woud be great!!

Cheers


Andy

 

[SQLBill]

Check out the Books OnLine (BOL), go to the Index tab and then type in the text CREATE PROCEDURE.

Very basic example:

CREATE PROCEDURE my_procedure AS
SELECT characterID
FROM mytable
GO

Then to run the stored procedure....

EXEC my_procedure

NOTE: SQL Server has it's own built-in stored procedures. It names them with the prefix sp_. For example...sp_help. It is considered poor programming/naming conventions to begin the name of your own stored procedures with the prefix sp_. I enforce the use of the programmers initials followed by sp (for example: bf_sp_myprocedure).

-SQLBill

-SQLBill

 

[mwolf00]

CREATE PROC dbo.usp_templateComplete(
@tempID INT = null,
@template varchar(30) = null,
@personID INT = null,
@requestID INT = null
)
AS
SET NOCOUNT ON

DECLARE @recordNumber INT

IF @tempID IS NULL OR @template IS NULL OR @personID IS NULL RETURN -100 -- bad data

INSERT INTO myTable (field1, field2, field3, field4)
VALUES (@tempID, @template, @personID, @requestID)

SELECT @recordNumber = @@IDENTITY

RETURN @recordNumber
GO



<%
dim objCmd
set objCmd = server.createobject(&quot;adodb.command&quot
Set objCmd.ActiveConnection = objConn
with objCmd
.commandText = &quot;usp_templateComplete&quot;
.CommandType = adCmdStoredProc
.parameters(0).direction = adParamReturnValue
.parameters(1) = tempID
.parameters(2) = &quot;tbsTmpltProjNew&quot;
.parameters(3) = personID
.parameters(4) = null
.Execute
End With
%>

if objCmd.parameters(0) = -100 then response.redirect &quot;badData.asp&quot; -----------------------------------------------------------------
Be nice. It's only doing what you tell it to do.
mikewolf@tst-us.com