stored procedure - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

stored procedure

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-08-05 : 08:23:25

Cheryl writes "I have looked through the site and haven't quite found what I needed. I have a created a stored procedure that checks to see if a record exists. If it doesn't then it inserts it as a new record, if it does then it updates the record.

My project is a document tracking system for loans and is created to be dynamic as much as possible. There is a page that displays all the available documents for that loan type, etc. and uses 4 tables. That SP looks like this:

CREATE PROCEDURE LoanDocList
@l numeric
AS

SELECT loans.loan_id AS loan_id, loans.entity_id AS entity_id, SubQ1.*

FROM SELECT DocForms.entitydoc_id, DocForms.entitydoc, ISNULL(entities.status,'Need')AS status, entities.id,entities.date_assigned

FROM loans INNER JOIN entitydocs DocForms INNER JOIN
entity CompanyType ON DocForms.entity_id = CompanyType.entity_id ON loans.entity_id = CompanyType.entity_id LEFT OUTER JOIN
entities ON loans.loan_id = entities.loan_id
WHERE (loans.loan_id = @l)) SubQ1 CROSS JOIN loans
WHERE(loans.loan_id = @l)
GO

Now the SP that I am having problems with when called from an ASP page. If I run it using the SQL tools it works great. I feel that there may be a problem with the logic and I can't see it. I have tried writing this a few different ways but nothing has worked. I know that the SP works, but within ASP it does not. Thanks in advance.

CREATE PROCEDURE entities_up

@id int,
@entitydoc_id int,
@entity_id int,
@loan_id int,
@status varchar(9),
@date_assigned char(8)

AS

IF NOT EXISTS (SELECT * FROM entities WHERE id=@id)
INSERT INTO entities (entitydoc_id,entity_id,loan_id,status,date_assigned)
VALUES(@entitydoc_id,@entity_id,@loan_id,@status,@date_assigned)

ELSE

UPDATE entities
SET status=@status,
date_assigned=@date_assigned
WHERE id =@id
SELECT @id =@@Identity
GO"

JustinBigelow
SQL Gigolo

1157 Posts

Posted - 2002-08-05 : 09:19:20

If the stored procedure works fine in QA but errors in an ASP then the usual culprit is the lack of "set nocount on". Leaving it out of your procedure decleration tends to play hell with the resultset.

CREATE PROCEDURE LoanDocList
@l numeric
AS
set nocount on
SELECT loans.loan_id AS loan_id, loans.entity_id AS entity_id, SubQ1.*
...

If that doesnt solve your problem then let us know what the exact error is.

hth,
Justin

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources