Guys,

Would this be a ideal procedure? Somehow my program is ffailing but i cant
find out if it is in procedure or my program.

CREATE PROCEDURE dbo.sp_approveDocument
@uniqueid int,
@approvedby char(15),
@documentid int output
AS
INSERT INTO ext_DocumentCenter (CategoryID, DocumentTitle, Description,
FileName, DocumentType, UploadBy,approvedby) SELECT CategoryID, Title,
Description, FileName, Type, RequestBy,@approvedby FROM
ext_DocumentUploadRequest where uniqueid = @uniqueid;
delete from ext_DocumentUploadRequest where uniqueid=@uniqueid;
select @documentid=(select max(documentid) from ext_DocumentCenter)
GO


Thanks

Manny

Ideal for what? Ideal in what regard? You're going to have to provide a lot
more info - what is failing, what is the error message, etc.

For query questions provide DDL etc -- http://aspfaq.com/5006


Show quoteHide quote

Ideal for what?  What does "failing" mean?  But what I can tell you a
few things from what you did post.

Your data element names make no sense.  Why did you make this an
"SP_" procedure?  How many non-unique ids do you have?  There is no
such thing as a "category_id"!  A data element is either an
identifier of some kind like "user_id" or it is a non-key attribute
that is a category "user_category", not a weird mix of both.  This
is like adjectives without a noun.

Since we have no spec, data model or DDL, I am trying to figure out why
there is a vague "unique_id" floating around here.  Shouldn't you
seek documents using a document number or id?

What is the purpose of Ext_DocumentUploadRequest?  Since the name is
singular, can I assume that it has one row at a time?  It looks like
you built a list of documents in it via this vague "unique_id" and
then insert them one at a time into Ext_DocumentCenter as they are
approved.

That would be a horrible way to do this.  It would mimic a punch
card/magnetic tapes file system in which you PHYSICALLY move data from
one card deck/tape file to another.

The relational approach would use an approval status code that gets set
by an UPDATE statement (pending, first edit, approved, rejected, etc.).
That would lead to something more like this:

CREATE PROCEDURE ApproveDocument
(@my_approving_user CHAR(15),
@my_document_id INTEGER)
AS
BEGIN
UPDATE Ext_DocumentCenter
  SET approval_status = 'A',
      approving_user = @approving_user,
      approval_date = CURRENT_TIMESTAMP
WHERE document_id = @my_document_id;

-- error handling here
END ;

I would probably want to find an industry standard document numbering
system instead of just using an integer and increment it.  That is just
asking for bad data.

Query Speed
Stored procedures in an n-tiered environment.
a really bad ordering problem
Calculated fields with Days/Dates
Dangers of update statement