Nesting SQL Transactions

Hopefully someone can point me in the right direction, I've been searching on the net for the answer to this question and can't seem to come up with anything.

First, I'm using ASP.NET 2.0 and Visual Studio 2005 with a SQL Server 2000 backend.

My SQL database is relational and is used to store names, addresses, etc of other companies.

What I need to be able to do is have 2 transactions, one nested within the other. In pseudo-code:

BEGIN TRANSACTION1

BEGIN TRANSACTION2

INSERT INTO COMPANY TABLE

COMMIT TRANSACTION2 -OR- ROLLBACK TRANSACTION2

GET COMPANYID JUST ADDED

PERFORM REMAINING INSERTS

COMMIT TRANSACTION1 -OR- ROLLBACK TRANSACTION 1 & 2

Right now I have everything grouped into one VB.NET transaction, which doesn't work because the company is not actually added until the transaction reaches commit. Therefore, I can't retrieve the companyID halfway through.

Is what I'm trying to do even possible? Thanks in advance for the help!

BEGIN TRANSACTION

INSERT INTO COMPANY TABLE

GET COMPANYID JUST ADDED

PERFORM REMAINING INSERTS

COMMIT TRANSACTION -OR- ROLLBACK TRANSACTION

dim conn as new sqlconnection("{ConnectString}")
conn.open
dim cmd as new sqlcommand("INSERT INTO Table1(column1) VALUES (@.col1) SELECT SCOPE_IDENTITY()",conn)
cmd.parameters.add("@.col1",sqldbtype.{Whatever}).value={Whatever}
dim MyID as integer=cmd.executescalar
dim cmd2 as new sqlcommand("INSERT INTO Table2(Table1ID,col2) VALUES (@.MyID,@.MyVal)",conn)
cmd2.parameters.add("@.MyID",sqldbtype.integer).value=MyID
cmd2.parameters.add("@.MyVal",sqldbtype.{whatever})
for loop here...
cmd2.parameter("@.MyVal").value={whatever}
cmd2.executenonquery
next
conn.close

Now wrap that in try/catch with a transaction and you are all set.|||

Motley-

Thanks for pointing me in the right direction! At first I wasn't sure exactly how to implement what you gave me with what I already have. After some trial and error and info fromthis site I was able to get my transaction to work exactly as I needed it to.

Thanks again for the help!

Need to Strip non-numerics from phone field

I tried searching forums for the last hour and could not find much.

Rather then doing...

REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(RE PLACE(ISNULL(PHONE, ''), ' ', ''), '(', ''), ')', ''), '-', ''), '.', ''), ':', ''), '+', '')

I was hoping there was an equally efficient alternative that utilizes PATINDEX('%[^0-9]%', PHONE)

to some capacity (i.e. uses regular expressions to strip all non-numeric characters from the phone field)

I am certain this has been addressed before, just not sure if the "REPLACE to the nth degree" is the only solution.

THANKS!Any way to make this recursive based on the actual length in the field? Or would I take a performance hit?

SELECT
CASE WHEN SUBSTRING( PHONE , 1 , 1 ) LIKE '[0-9]' THEN SUBSTRING( PHONE , 1 , 1 ) ELSE '' END +
CASE WHEN SUBSTRING( PHONE , 2 , 1 ) LIKE '[0-9]' THEN SUBSTRING( PHONE , 2 , 1 ) ELSE '' END +
CASE WHEN SUBSTRING( PHONE , 3 , 1 ) LIKE '[0-9]' THEN SUBSTRING( PHONE , 3 , 1 ) ELSE '' END +
CASE WHEN SUBSTRING( PHONE , 4 , 1 ) LIKE '[0-9]' THEN SUBSTRING( PHONE , 4 , 1 ) ELSE '' END +
CASE WHEN SUBSTRING( PHONE , 5 , 1 ) LIKE '[0-9]' THEN SUBSTRING( PHONE , 5 , 1 ) ELSE '' END +
CASE WHEN SUBSTRING( PHONE , 6 , 1 ) LIKE '[0-9]' THEN SUBSTRING( PHONE , 6 , 1 ) ELSE '' END +
CASE WHEN SUBSTRING( PHONE , 7 , 1 ) LIKE '[0-9]' THEN SUBSTRING( PHONE , 7 , 1 ) ELSE '' END +
CASE WHEN SUBSTRING( PHONE , 8 , 1 ) LIKE '[0-9]' THEN SUBSTRING( PHONE , 8 , 1 ) ELSE '' END +
CASE WHEN SUBSTRING( PHONE , 9 , 1 ) LIKE '[0-9]' THEN SUBSTRING( PHONE , 9 , 1 ) ELSE '' END +
CASE WHEN SUBSTRING( PHONE ,10 , 1 ) LIKE '[0-9]' THEN SUBSTRING( PHONE ,10 , 1 ) ELSE '' END +
CASE WHEN SUBSTRING( PHONE ,11 , 1 ) LIKE '[0-9]' THEN SUBSTRING( PHONE ,11 , 1 ) ELSE '' END +
CASE WHEN SUBSTRING( PHONE ,12 , 1 ) LIKE '[0-9]' THEN SUBSTRING( PHONE ,12 , 1 ) ELSE '' END +
CASE WHEN SUBSTRING( PHONE ,13 , 1 ) LIKE '[0-9]' THEN SUBSTRING( PHONE ,13 , 1 ) ELSE '' END +
CASE WHEN SUBSTRING( PHONE ,14 , 1 ) LIKE '[0-9]' THEN SUBSTRING( PHONE ,14 , 1 ) ELSE '' END +
CASE WHEN SUBSTRING( PHONE ,15 , 1 ) LIKE '[0-9]' THEN SUBSTRING( PHONE ,15 , 1 ) ELSE '' END +
CASE WHEN SUBSTRING( PHONE ,16 , 1 ) LIKE '[0-9]' THEN SUBSTRING( PHONE ,16 , 1 ) ELSE '' END +
CASE WHEN SUBSTRING( PHONE ,17 , 1 ) LIKE '[0-9]' THEN SUBSTRING( PHONE ,17 , 1 ) ELSE '' END +
CASE WHEN SUBSTRING( PHONE ,18 , 1 ) LIKE '[0-9]' THEN SUBSTRING( PHONE ,18 , 1 ) ELSE '' END +
CASE WHEN SUBSTRING( PHONE ,19 , 1 ) LIKE '[0-9]' THEN SUBSTRING( PHONE ,19 , 1 ) ELSE '' END +
CASE WHEN SUBSTRING( PHONE ,20 , 1 ) LIKE '[0-9]' THEN SUBSTRING( PHONE ,20 , 1 ) ELSE '' END +
CASE WHEN SUBSTRING( PHONE ,21 , 1 ) LIKE '[0-9]' THEN SUBSTRING( PHONE ,21 , 1 ) ELSE '' END +
CASE WHEN SUBSTRING( PHONE ,22 , 1 ) LIKE '[0-9]' THEN SUBSTRING( PHONE ,22 , 1 ) ELSE '' END +
CASE WHEN SUBSTRING( PHONE ,23 , 1 ) LIKE '[0-9]' THEN SUBSTRING( PHONE ,23 , 1 ) ELSE '' END +
CASE WHEN SUBSTRING( PHONE ,24 , 1 ) LIKE '[0-9]' THEN SUBSTRING( PHONE ,24 , 1 ) ELSE '' END +
CASE WHEN SUBSTRING( PHONE ,25 , 1 ) LIKE '[0-9]' THEN SUBSTRING( PHONE ,25 , 1 ) ELSE '' END +
CASE WHEN SUBSTRING( PHONE ,26 , 1 ) LIKE '[0-9]' THEN SUBSTRING( PHONE ,26 , 1 ) ELSE '' END +
CASE WHEN SUBSTRING( PHONE ,27 , 1 ) LIKE '[0-9]' THEN SUBSTRING( PHONE ,27 , 1 ) ELSE '' END +
CASE WHEN SUBSTRING( PHONE ,28 , 1 ) LIKE '[0-9]' THEN SUBSTRING( PHONE ,28 , 1 ) ELSE '' END +
CASE WHEN SUBSTRING( PHONE ,29 , 1 ) LIKE '[0-9]' THEN SUBSTRING( PHONE ,29 , 1 ) ELSE '' END +
CASE WHEN SUBSTRING( PHONE ,30 , 1 ) LIKE '[0-9]' THEN SUBSTRING( PHONE ,30 , 1 ) ELSE '' END
AS NUMERICVALUEONLY
FROM COUNCIL_MEMBER|||I'd use one of my old handy-dandys that I wrote for just this kind of job!CREATE FUNCTION dbo.fNumeric(@.pcIn VARCHAR(100)) RETURNS VARCHAR(100) AS
BEGIN
DECLARE @.i INT

SET @.i = PatIndex('%[^0-9]%', @.pcIn)

WHILE 0 < @.i
BEGIN
SET @.pcIn = Stuff(@.pcIn, @.i, 1, '')
SET @.i = PatIndex('%[^0-9]%', @.pcIn)
END

RETURN @.pcIn
END
GO-PatP|||I'd use one of my old handy-dandys that I wrote for just this kind of job!CREATE FUNCTION dbo.fNumeric(@.pcIn VARCHAR(100)) RETURNS VARCHAR(100) AS
BEGIN
DECLARE @.i INT

SET @.i = PatIndex('%[^0-9]%', @.pcIn)

WHILE 0 < @.i
BEGIN
SET @.pcIn = Stuff(@.pcIn, @.i, 1, '')
SET @.i = PatIndex('%[^0-9]%', @.pcIn)
END

RETURN @.pcIn
END
GO-PatP

While it looks pretty, it doesn't look very efficient - it does give me an idea though, I'll post back if I have a more elegant solution then my last post.|||Something like this might work if I packaged it into a UDF, not sure how much of a performance hit I would take with the While loop (I know very similar to what Pat posted - ok nearly identical) :)

DECLARE @.phone VARCHAR(100)
SELECT @.phone = '123ab 34 902x:a123ab 34 902x:a'

WHILE (PATINDEX('%[^0-9]%', @.phone) > 0)
SELECT @.phone = REPLACE(@.phone, SUBSTRING(@.phone, PATINDEX('%[^0-9]%', @.phone), 1), '')

SELECT @.phone|||The only difference is he gets Estimated Row Size of 61 while yours holds at 4 regardless of the string length and contents.|||The only difference is he gets Estimated Row Size of 61 while yours holds at 4 regardless of the string length and contents.I'm not sure that I understand the significance of that comment, but Ok. The PatIndex() call is (or at least was when I wrote this) expensive in terms of both time and resources consumed, so I was trying to minimize those calls.

-PatP|||Nothing significant, Pat, I was just trying to identify the differences, that's all ;)