comments inline...

>Hi everybody,
>
>I'm trying to write an SP. Here is what I've wrote so far:
>

>-- =============================================
>-- Create procedure with OUTPUT Parameters
>-- =============================================
>-- creating the store procedure
>IF EXISTS (SELECT name
>	   FROM   sysobjects
>	   WHERE  name = N'sp_InsertShoppingCartInfo'
>	   AND 	  type = 'P')
>    DROP PROCEDURE sp_InsertShoppingCartInfo
>GO
>

--> do NOT prefix a stored procedure with sp_. sp_ has special meaning to sql server. it tell sql server that this stored procedure is a system stored procedure. when you execute it, sql server will look in the master database first then it will look in the current database

>CREATE PROCEDURE sp_InsertShoppingCartInfo(
>	@tcUserID  char(20),
>        @tnNumOfRecords int,
>        @tcDescription 	text,
>        @tcCreditCategory char(1),
>	@tcSelectionCriteria text,
>        @tcListOfCodes varchar(500))
>AS
>
>--	<@param2, sysname, @p2> <datatype_for_param2, , int>  OUTPUT
>-- SELECT @p2 = @p2 + @p1
>
>/*
>CREATED BY:    Nadya Nosonovsky
>CREATED ON:    28/2/2003
>PURPOSE:    This routine inserts records into Cart, CartContent and CartDetail tables
>            It passes UserID, Description, SelectionCriteria, NumberOfRecords,
>            and list of CredID separated by comma to insert into CartDetail table
>*/
>-- First do basic validation
>BEGIN
>
>    IF @tcUserID IS NULL
>      BEGIN

--> while PRINTs come out nicely in QA, most data access technologies do not return them as errors but as informational messages. Use RAISERROR

>        PRINT 'ERROR: The UserID value must contain a value.'
>        RETURN 1
>      END
>
>    IF NOT EXISTS
>         (SELECT * FROM INFORMATION_SCHEMA.TABLES
>          WHERE TABLE_NAME = 'Cart')
>        BEGIN
>          PRINT 'ERROR: The Cart table does not exist.'
>          RETURN 2
>        END
>
>    DECLARE @CartID  int
>    BEGIN TRANSACTION
>/*
>   If exists, grab CartID and update LastModified field
>   otherwise insert a record and grab generated CartID
>*/
>   UPDATE cart
>   SET LastModTs = CURRENT_TIMESTAMP,
>   @CartID = CartID
>   FROM cart
>   WHERE UserID = @tcUserID
>
>   IF @@ERROR <> 0
>        BEGIN
>            ROLLBACK TRAN
>            PRINT 'ERROR: An error occurred while attempting to update the Cart Table.'
>            RETURN 3
>        END

--> @@rowcount and @@error are reset by each statement. by the time execution gets here, the number of rows affected by your update query will be lost. to capture both values, use the following:

SELECT @cnt = @@rowcount, @err = @@error 

you'll have to declare @cnt and @err 

>
>    IF @@RowCount = 0 --Record doesn't exist
>        BEGIN
>	   INSERT INTO cart UserID values @tcUserID
>	   @CartID = @@SCOPE_IDENTITY
>        END
>
>     IF @@ERROR <> 0
>        BEGIN
>            ROLLBACK TRAN
>            PRINT 'ERROR: An error occurred while attempting to insert record to the Cart Table.'
>            RETURN 4
>        END
>

--> this line is not correct. the column list and values list must be wrapped by parens

>     INSERT INTO CartContent
>            CartID, NumOfRecords, CreditCategory, DescriptionText, SelectionCriteria
>            values @CartID, @tnNumOfRecords, @tcCreditCategory, @tcDescription, @tcSelectionCriteria
>
>     IF @@ERROR <> 0
>        BEGIN
>            ROLLBACK TRAN
>            PRINT 'ERROR: An error occurred while attempting to insert record to the CartContent Table.'
>            RETURN 4
>        END
>
>     commit transaction
>END
>-- =============================================
>-- example to execute the store procedure
>-- =============================================
>DECLARE <@variable_for_output_parameter, sysname, @p2_output> <datatype_for_output_parameter, , int>
>EXECUTE <procedure_name, sysname, proc_test> <value_for_param1, , 1>, <@variable_for_output_parameter, sysname, @p2_output> OUTPUT
>SELECT <@variable_for_output_parameter, sysname, @p2_output>
>GO

>
>I when tried to execute it and here is what I got:
>=======================================================
>Server: Msg 170, Level 15, State 1, Procedure sp_InsertShoppingCartInfo, Line 58
>Line 58: Incorrect syntax near 'UserID'.
>Server: Msg 170, Level 15, State 1, Procedure sp_InsertShoppingCartInfo, Line 70
>Line 70: Incorrect syntax near 'CartID'.
>
>What is incorrect here?