How to increment a number in a SP? 1

[Chopstik]

Continuing on with my same SQL project, I am trying to determine if it is possible to increment a count in a stored procedure. For each row in this particular SELECT statement, I want it to be able to count and then reflect the new number for each row. I am wondering if it is possible and, if so, what would be the best way to approach it. Thanks! (My code, thus far, is below)

CREATE PROCEDURE APInterface
@CurrentRun integer
AS
SET NOCOUNT ON

DECLARE @PayNumber integer
SET @PayNumber = 0

CREATE Table #tmp(Outline char(175), RecID int identity)

INSERT #tmp(Outline)
SELECT --Outline =
CONVERT(char(2),'10')+
CONVERT(char(10),'')+
SUBSTRING(CONVERT(char(10),GETDATE(),102),6,2)+
SUBSTRING(CONVERT(char(10),GETDATE(),102),9,2)+
CONVERT(char(4),YEAR(GETDATE()))+
CONVERT(char(2),'XR')+
CONVERT(char(158),'')

--UNION ALL

INSERT #tmp(Outline)
SELECT --Outline =
CONVERT(char(2),'20')+
'0' + crPayerCompany +
-- This is where I am trying to put this, but not sure how best to do it if it's possible.
-- WHILE @@FETCH_STATUS = 0
-- BEGIN
-- --RIGHT(CONVERT(char(5),'00000') +
-- CONVERT(char(6),@PayNumber + 1) +
-- END
CONVERT(char(6),'') + --This needs to be changed to reflect the actual Pay Number that we will be using. Probably needs a temp table.
SUBSTRING(CONVERT(char(10),GETDATE(),102),6,2)+
SUBSTRING(CONVERT(char(10),GETDATE(),102),9,2)+
CONVERT(char(4),YEAR(GETDATE()))+
CONVERT(char(2),'XR')+
CONVERT(char(2),'') +
CASE
WHEN crCheckAmount >= 0 then CONVERT(char(1),1)
ELSE
CONVERT(char(1),2)
END +
CASE
WHEN Vendor_ID is null then CONVERT(char(9),'000000000')
ELSE
CAST(REPLICATE('0',9) + Vendor_ID as varchar(15))
END
FROM L_CheckRequestChecks JOIN L_CheckRequest on L_CheckRequestChecks.crCheckRunSerial = L_CheckRequest.crCheckRunSerial
JOIN L_Address on Lease_ID = crPropertyID
WHERE L_CheckRequest.crCheckRunSerial = @CurrentRun and Address_Type = 'Payment' and Vendor_ID = crVendorID
-- left(Company_Name,10) = left(crCompany,10) and left(City,7) = left(crCity,7)
ORDER BY L_CheckRequestChecks.crCheckSerial

SELECT Outline FROM #tmp
Order By RecID Insanity is merely a state of mind while crazy people have a mind of their own.

 

[gcole]

Declare a @Cnt variable as an int
Set @Cnt = 0
As you loop
Set @Cnt = @Cnt + 1

 

[Chopstik]

Ok, if I make that change, the relevant part of my code now looks like this:

INSERT #tmp(Outline)
SELECT --Outline =
CONVERT(char(2),'20')+
'0' + crPayerCompany
SET @PayNum = @PayNum + 1 +
CONVERT(char(6), @PayNum)+
-- CONVERT(char(6),'') + --This needs to be changed to reflect the actual Pay Number that we will be using. Probably needs a temp table.
SUBSTRING(CONVERT(char(10),GETDATE(),102),6,2)+
SUBSTRING(CONVERT(char(10),GETDATE(),102),9,2)+
CONVERT(char(4),YEAR(GETDATE()))+
CONVERT(char(2),'XR')+
CONVERT(char(2),'') +
CASE
WHEN crCheckAmount >= 0 then CONVERT(char(1),1)
ELSE
CONVERT(char(1),2)
END +
CASE
WHEN Vendor_ID is null then CONVERT(char(9),'000000000')
ELSE
RIGHT('000000000' + CAST(Vendor_ID as varchar(6)), 9)
END --+
FROM L_CheckRequestChecks JOIN L_CheckRequest on L_CheckRequestChecks.crCheckRunSerial = L_CheckRequest.crCheckRunSerial
JOIN L_Address on Lease_ID = WHERE L_CheckRequest.crCheckRunSerial = @CurrentRun and Address_Type = 'Payment' and Vendor_ID = crVendorID
-- left(Company_Name,10) = left(crCompany,10) and left(City,7) = left(crCity,7)
ORDER BY L_CheckRequestChecks.crCheckSerial

But now, I get an error message stating that there is an error near the keyword FROM. Not sure why now there would be an error near FROM (since my changes were done earlier in the code)... Thoughts (since I am obviously am not having any)?... Thanks! Insanity is merely a state of mind while crazy people have a mind of their own.

 

[Chopstik]

Sorry, I should clarify that the error message is that there is a syntax error near the keyword FROM. Insanity is merely a state of mind while crazy people have a mind of their own.

 

[sunila7]

Hi,

u cannot put a set statement in Select statement and increment a counter. what exactly r u tring to do. r u trying to put a no field which starts from 1 and goes till the last record. if yes does this table L_CheckRequestChecks have an id field which is unique?

u can open a cursor and iterate through it and update data into the temp table u have created.

Sunil

 

[Chopstik]

Sunil,

Yes, I am trying to put a number field which goes from 1 until the end of the recordset, and yes it does have a unique id field (L_CheckRequestChecks.crCheckSerial). Haven't worked with cursors before, so think I am going to have to do some research on how best to figure this out. So would I have to use cursors and the FETCH command in order to cycle through the records?

Thanks! Insanity is merely a state of mind while crazy people have a mind of their own.

 

[sunila7]

Hi,

try this query... u seems to have missed some SQL in the join part .... check wether it returns a running number aftersrPAyerCompnay in the SQL.

SELECT
CONVERT(char(2),'20')+
'0' + crPayerCompany +
(SELECT COUNT(crCheckSerial) FROM L_CheckRequestChecks L1 Where L1.crCheckSerial
<=L_CheckRequestChecks.crCheckSerial)
+
-- CONVERT(char(6),'') + --This needs to be changed to reflect the actual Pay Number that we will be using. Probably needs a temp table.
SUBSTRING(CONVERT(char(10),GETDATE(),102),6,2)+
SUBSTRING(CONVERT(char(10),GETDATE(),102),9,2)+
CONVERT(char(4),YEAR(GETDATE()))+
CONVERT(char(2),'XR')+
CONVERT(char(2),'') +
CASE
WHEN crCheckAmount >= 0 then CONVERT(char(1),1)
ELSE
CONVERT(char(1),2)
END +
CASE
WHEN Vendor_ID is null then CONVERT(char(9),'000000000')
ELSE
RIGHT('000000000' + CAST(Vendor_ID as varchar(6)), 9)
END --+
FROM L_CheckRequestChecks
JOIN L_CheckRequest on L_CheckRequestChecks.crCheckRunSerial = L_CheckRequest.crCheckRunSerial
JOIN L_Address on Lease_ID = --- You seems to have missed out on Some SQL here
WHERE L_CheckRequest.crCheckRunSerial = @CurrentRun and Address_Type = 'Payment' and Vendor_ID = crVendorID
ORDER BY L_CheckRequestChecks.crCheckSerial

Sunil

 

[sunila7]

Hi,

try this query... u seems to have missed some SQL in the join part .... check wether it returns a running number after PAyerCompnay in the SQL.

SELECT
CONVERT(char(2),'20')+
'0' + crPayerCompany +
(SELECT COUNT(crCheckSerial) FROM L_CheckRequestChecks L1 Where L1.crCheckSerial
<=L_CheckRequestChecks.crCheckSerial)
+
-- CONVERT(char(6),'') + --This needs to be changed to reflect the actual Pay Number that we will be using. Probably needs a temp table.
SUBSTRING(CONVERT(char(10),GETDATE(),102),6,2)+
SUBSTRING(CONVERT(char(10),GETDATE(),102),9,2)+
CONVERT(char(4),YEAR(GETDATE()))+
CONVERT(char(2),'XR')+
CONVERT(char(2),'') +
CASE
WHEN crCheckAmount >= 0 then CONVERT(char(1),1)
ELSE
CONVERT(char(1),2)
END +
CASE
WHEN Vendor_ID is null then CONVERT(char(9),'000000000')
ELSE
RIGHT('000000000' + CAST(Vendor_ID as varchar(6)), 9)
END --+
FROM L_CheckRequestChecks
JOIN L_CheckRequest on L_CheckRequestChecks.crCheckRunSerial = L_CheckRequest.crCheckRunSerial
JOIN L_Address on Lease_ID = --- You seems to have missed out on Some SQL here
WHERE L_CheckRequest.crCheckRunSerial = @CurrentRun and Address_Type = 'Payment' and Vendor_ID = crVendorID
ORDER BY L_CheckRequestChecks.crCheckSerial

Sunil

 

[Chopstik]

Sunil,

That worked great! Thank you very much. However, what is the difference between this and using a cursor? I understand that a cursor can be more time-consuming to run (and my run-time for this increased from about 2 seconds to almost a minute), but this code also seems much easier (for me, anyway) to follow.

Also, if I have to break down this count further, or use it in a similar fashion, is this possible? Sorry to ask so many questions, but this is still such a new area for me (as far as SQL goes, anyway). Thanks! Insanity is merely a state of mind while crazy people have a mind of their own.

 

[sunila7]

Hi,

Basically difference between using a cursor and this is that in a Cursor u would basically be iterating through each record one at a time. incrementing a counter then update it to the table using an update statement.

u can read more about cursors in this article

http://www.swynk.com/friends/achigrik/usecursor.asp

i didnt understand ur question about breaking the count.

Sunil

 

[Chopstik]

Ok, thanks for the help with the cursor. Not sure if I know of another way to phrase my question b/c I'm not really sure what I want/need just yet. I haven't worked my way to that point in the project yet, so guess I'm trying to anticipate (rather badly at this point) what will be needed. When I have a better idea, I'll ask then.

Thanks again for all of your help. At this rate, I'm going to owe a great deal of thanks to you and several others. ;-)
Insanity is merely a state of mind while crazy people have a mind of their own.