Stored Procedure

Question

Stored Procedure

Mahathi

Old Hand

Points: 320
More actions
June 23, 2006 at 5:22 am

#169308

Hi
I wrote a stored procedure which inserts multiple rows into a database. After writing the stored procedure the application which is using this has been damaged. I am giving the stored procedure. Anyone please tell me the modification for that stored procedure.

CREATE PROCEDURE ReserveMultipleSelectedComputers(@ServiceTagIDs varchar(1000),@UserID int,@Duration int,@DueDate DateTime,@Comments varchar(100),@Count int=0 output)
AS
DECLARE @ServID varchar(10),@Pos int;
DECLARE @ReserveStatus int;
DECLARE @AvailableComputerCount int;
DECLARE @Service_Tag_Id varchar(50);
SET @ServiceTagIDs = LTRIM(RTRIM(@ServiceTagIDs))+','
SET @Pos=CHARINDEX(',',@ServiceTagIDs,1)
IF REPLACE(@ServiceTagIDs, ',', '') <>''
BEGIN
  BEGIN TRANSACTION
   WHILE @Pos > 0
  BEGIN
   PRINT @pos
   SET @ServID = LTRIM(RTRIM(LEFT(@ServiceTagIDs,@Pos-1)))
   If @ServID <> ''
   BEGIN
    SELECT @AvailableComputerCount = Count(*) FROM Mst_ProjwiseComputers PC, Status S WHERE PC.Service_Tag_id = @ServID AND PC.Status_Id = S.Status_Id AND S.Status_Name = 'Available';
    IF @AvailableComputerCount=1
     BEGIN
      SELECT @ReserveStatus=(SELECT Top 1 Status_Id FROM Status WHERE Status_Name='Reserved')
      UPDATE Mst_ProjwiseComputers SET Status_Id=@ReserveStatus WHERE Service_Tag_id=@ServID
      IF @Duration > 0
       INSERT INTO Transactions(Service_tag_Id,User_Id,Start_Time,End_Time,Comments,Status_Id)VALUES(@ServID,@UserID,getDate(),DATEADD(hour,@Duration,getDate()),@Comments,@ReserveStatus)
      ELSE
       INSERT INTO Transactions(Service_tag_Id,User_Id,Start_Time,End_Time,Comments,Status_Id)VALUES(@ServID,@UserID,getDate(),@DueDate,@Comments,@ReserveStatus)
     END
    ELSE
     BEGIN
      Goto ERR_HANDLER
     END
    SET @ServiceTagIDs = RIGHT(@ServiceTagIDs,LEN(@ServiceTagIDs)-@Pos)
       SET @Pos = CHARINDEX(',',@ServiceTagIDs,1)
   END
  END
  SET @Count = 1
  COMMIT TRANSACTION
END
ERR_HANDLER:
IF(@@ERROR <> 0)
BEGIN
  SET @Count=0
  RollBack Transaction
END
SELECT @Count
GO

This is the stored procedure. This is executing in the sql query analyzer. But when I call this procedure from the application it is giving an error called Server Application Unavailable. Only the page using this procedure is not working. The rest of the application is working. I will be thankful for the solution provided.
Regards
Mahathi.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

RyanRandall SSChampion Points: 13623 More actions · Answer 1

If I understand you correctly (and I can't believe I do), aren't you telling us that the procedure is working fine, but also asking for a modification to it?

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

Mahathi Old Hand Points: 320 More actions · Answer 2

Hi Ryan

The error cam when I execute the procedure is

Event ID:

1001

Source:

ASP.NET 1.1.4322.0

aspnet_wp.exe (PID:3152) was recycled because memory consumption exceeded the 608MB(60 percent of available RAM).

So to make that procedure work within the constraint I want to make the stored procedure in a more efficient way. So if it is possible give me more efficient stored procedure.

Thanks in advance.

RyanRandall SSChampion Points: 13623 More actions · Answer 3

If calling this stored procedure from query analyser is working fine for you, but not from your asp.net - you probably need to post your problem to an asp.net forum.

We can improve your stored procedure for you, although I'm not sure that's the cause of your error. If you'd like it improving regardless, post your table ddl, some sample data and the result you would like.

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

daniel Mr or Mrs. 500 Points: 581 More actions · Answer 4

Mahathi,

just a few links that may shed some light on your asp/iis issue

problem - http://support.microsoft.com/default.aspx?scid=kb;en-us;324772

idleTimeout solution - http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpgenref/html/gngrfprocessmodelsection.asp

hope this helps!

Daniel

MISfIT