Hi Bob,

I want to have this SP in Shopping Cart database. But the info is in either MA or CT database...

So, what should I do, if the above would not work?

>>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
>
>But, won't it mean executing the WHERE twice? Won't there only be a lock duing the update if the WHERE is matched?
>
>>
>>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.
>
>Oh, didn't ralize that. So she will have to EXEC the whole update to the other database. Or, she could have an SP in each database that does the work and call it using EXEC passing any stuff needed...
>
>EXEC(@tcState+'Credit.dbo.updatewhatever '+@thing+','+@thing)
>
>This should work, yes.
>
>BOb
>
>
>
>
>>
>>>>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