Should i use a loop? - 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

General SQL Server Forums

New to SQL Server Programming

Should i use a loop?

Author

Topic

dannyboy86
Starting Member

8 Posts

Posted - 2007-09-18 : 05:14:22

I have the following stored procedure which enters items bought by the usr in the database, inserting the user's ID, the item and the price. now every user has a unique id and every user can only buy three items; thus only three inputs must be inserted in the table, how can i do that? This is the current SP i have...

ALTER PROCEDURE [dbo].[spA_ALW_InsertIntoMLAGoods]

@MLAFormIDF INT,
@Description NVARCHAR(50),
@Amount INT

AS

BEGIN
BEGIN TRY
--UPDATE MLAGoods
--SET
-- MLAFormIDF = @MLAFormIDF,
-- Description = @Description,
-- Amount = @Amount
--WHERE MLAFormIDF = @MLAFormIDF

INSERT INTO MLAGoods
(
MLAFormIDF,
Description,
Amount
)
VALUES
(
@MLAFormIDF,
@Description,
@Amount
)
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE() AS [ERRORMSG], ERROR_SEVERITY() AS [ERRORSEV]
RETURN @@ERROR
END CATCH

END

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-09-18 : 05:36:19

well since your stored procedure only inserts onyl one item per call, you must call the stored procedure 3 times.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Kristen
Test

22859 Posts

Posted - 2007-09-18 : 05:43:48

"every user has a unique id and every user can only buy three items"


IF (
        SELECT COUNT(*)
        FROM MLAGoods
        WHERE MLAFormIDF = @MLAFormIDF -- I assume this is the userID?
   ) >= 3
BEGIN
    -- Already has 3, or more, items
    ... Error handling Routine Here ...
END
ELSE
BEGIN
    INSERT INTO MLAGoods
    ...
END

Kristen

dannyboy86
Starting Member

8 Posts

Posted - 2007-09-18 : 05:54:00

thanks Kristen...where you placed the "... Error handling Routine Here ..." i placed an update feature since the users can update the data...sucks that all three items are updating now :/ ah got to work more on it. thanks a lot :)

Kristen
Test

22859 Posts

Posted - 2007-09-18 : 05:59:26

"sucks that all three items are updating now"

You'll either need to add an IDENTITY column so that you have a unique ID for each row, or pass the "old" Description, Amount values so you can use those to match the original record.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources