?
Solved

stored procedure issues

Posted on 2011-10-10
5
Medium Priority
?
233 Views
Last Modified: 2012-05-12
having problems with this stored proc.


I can get it to populate the fields that it needs to but its not auto incrementing the table as designed.

Basically it's supposed to go like this

Select the top record with the lowest int in IRBTurnCounter then it needs to add a 1 to the value of the selected record so instead of IRBTurnCounter for the selected record being 1 it becomes a 2


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[P_sp_EmailerProgramV2]
	-- Add the parameters for the stored procedure here

AS
DECLARE @Email nvarchar(50),
@IRBTurnCounter int,
@FirstName nvarchar(50)

	/* SET NOCOUNT ON */
select top 1 Email, IRBTurnCounter, FirstName from tbl_IRBMembors Order by  IRBTurnCounter Asc

UPDATE tbl_IRBMembors
SET 
IRBTurnCounter = @IRBTurnCounter + 1,
Email = @Email
WHERE Email = @Email

Open in new window

0
Comment
Question by:HawaiiDragon
  • 3
  • 2
5 Comments
 
LVL 61

Expert Comment

by:HainKurt
ID: 36945008
try

select top 1 Email, IRBTurnCounter, FirstName from tbl_IRBMembors Order by  IRBTurnCounter Asc
-->
select top 1 @IRBTurnCounter = IRBTurnCounter + 1 from tbl_IRBMembors Order by  IRBTurnCounter Asc
0
 

Author Comment

by:HawaiiDragon
ID: 36945089
Nope no records added
0
 
LVL 61

Expert Comment

by:HainKurt
ID: 36945827
there is no insert statement in your code, only update...
sure it wont insert any new record...

try this code below...


...
/* SET NOCOUNT ON */
select top 1 @Email = Email, @IRBTurnCounter = IRBTurnCounter + 1 from tbl_IRBMembors Order by IRBTurnCounter Asc

UPDATE tbl_IRBMembors
SET 
IRBTurnCounter = @IRBTurnCounter,
Email = @Email
WHERE Email = @Email

Open in new window

0
 
LVL 61

Accepted Solution

by:
HainKurt earned 2000 total points
ID: 36945835
but I don't understand what you are trying to do here...

for example why you get Email? why do you update it?

the code below increments the max number to max + 1

please give example of what you are trying to do... code does not make any sense to me...
ALTER PROCEDURE [dbo].[P_sp_EmailerProgramV2]
	-- Add the parameters for the stored procedure here
AS
@IRBTurnCounter int

/* SET NOCOUNT ON */
select @IRBTurnCounter = max(IRBTurnCounter) tbl_IRBMembors

UPDATE tbl_IRBMembors
   SET IRBTurnCounter = @IRBTurnCounter +1,
 WHERE IRBTurnCounter = @IRBTurnCounter

Open in new window

0
 

Author Closing Comment

by:HawaiiDragon
ID: 36969210
Sorry HainKurt,  I forgot to assing points!

Explanation:

When a new document is assigned to a user (admin of department) the filed is changed by 1 so that they have to wait in line to get the next document. Its a strage process but when all student documents are simmliar its so that their turns in grading the documents is fair.

Matt
0

Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

601 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question