Two comments:

1) instead of

>UPDATE cart
>SET LastModified = CURRENT_TIMESTAMP
>,@CartID = cartid
>FROM cart
>WHERE userid = @tnUserID
>
>IF @@RowCount = 0
>BEGIN
> INSERT INTO cart blah blah blah
> @cartid = @@SCOPE_IDENTITY
>END

you'd be better off with

IF EXISTS (SELECT * FROM cart WHERE cartid = @cartID)
>UPDATE cart
>SET LastModified = CURRENT_TIMESTAMP
>,@CartID = cartid
>FROM cart
>WHERE userid = @tnUserID
ELSE
>INSERT INTO cart blah blah blah
>@cartid = @@SCOPE_IDENTITY

It'll be much better on your locks and concurrency

2)

EXEC ('USE '+@tcState+'Credit')

won't change the database. The dynamic SQL is executed in a seperate batch. the database will change in the other batch but it won't affect the context that the stored procedure is running under.

>>Hi everybody,
>>
>>May be you can help me with writing complicated stored procedure, which involves several tables and one from another database.
>
>This should get you started.
>
>

>CREATE PROC @dothisthing
>(@tnUserID          int
>,@tcListOfCodes     varchar(500)
>,@tcState           char(2)
>)
>
>AS
>
>--Look for tnUserID in Cart table.
>DECLARE @cartid     int
>
>begin transaction
>
>/*
>If exists, grab CartID and update LastModified field
>otherwise insert a record and grab generated CartID
>*/
>UPDATE cart
>SET LastModified = CURRENT_TIMESTAMP
>,@CartID = cartid
>FROM cart
>WHERE userid = @tnUserID
>
>IF @@RowCount = 0
>BEGIN
>	INSERT INTO cart blah blah blah
>	@cartid = @@SCOPE_IDENTITY
>END
>
>-- From another database (either CTCredit or MACredit - depending on passed 3rd parameter)
>EXEC ('USE '+@tcState+'Credit')
>
>/*
>for every CredID in the list insert into CreditContent records and in the CreditDetail (child of CreditContent) based on the info from CreditInfo table from either CTCredit or MACredit database.
>
>DO THIS INSERT HERE USING
>INSERT INTO ...
>SELECT FROM ...
>
>Here is a sample of retriving info from CreditInfo corresponding to passed list of codes:
>
>strSQL = "select CredID, CreditCat, ccode, town, Address from " & _
>         "CreditInfo where ccode+town+address in (select ccode+town+address from CreditInfo where CredID in (" & request.form("Selection") &_
>		  ")) order by CreditCat, ccode, town, Address"
>*/
>
>-- RETURN BACK TO YOU PRIMARY DATABASE
>USE mydatabasename
>
>commit transaction
>
>

>
>BOb