Isolation Level

Question

Post reply

Isolation Level

Viewing 15 posts - 16 through 30 (of 50 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Debugging a script like this is quite tricky without any data to test against. Try running this:

--ALTER Procedure [dbo].[BS_Voucher_CreateVoucher]

DECLARE

@vouchertypeID int = 1,

@denomination int = 100,

@quantity int = 10,

@amountvalue int = 100,

@userid varchar(50) = 1,

@validateDays int = 40

--AS

--BEGIN

SET NOCOUNT ON;

DECLARE

--@vouchertype varchar(20),

@transactionID varchar(20),

--@count int =1,

@VoucherNumber int, -- was @max-2

@vouchertypenew varchar(20),

@retval varchar(20),

@voucherstatus int

-- table variables receive OUTPUT

DECLARE @VoucherNumberTable TABLE (vouchervalue INT);

DECLARE @transactionIDTable TABLE (transactionID INT);

--------------------------------------------------------------------------------------

-- Load some variables. Exit immediately if vouchertype is invalid

--------------------------------------------------------------------------------------

SELECT

@vouchertypenew = SUBSTRING(TrimmedVoucherType,1+CHARINDEX(' ',TrimmedVoucherType,1),20),

@retval = LEFT(TrimmedVoucherType,1)

+ ISNULL(SUBSTRING(TrimmedVoucherType,1+NULLIF(CHARINDEX(' ',TrimmedVoucherType,1),0),1),'')

FROM GV_VoucherType v

CROSS APPLY (SELECT TrimmedVoucherType = LTRIM(RTRIM(v.vouchertype))) x

WHERE v.VoucherTypeID = @vouchertypeID;

IF @vouchertypenew IS NULL

RETURN -1

SELECT @voucherstatus = VoucherStatusId FROM GV_VoucherStatus WHERE VoucherStatus = 'New';

--------------------------------------------------------------------------------------

-- Read existing @VoucherNumber, set next @VoucherNumber

--------------------------------------------------------------------------------------

-- save off the current value to @VoucherNumber

-- and update to the next available value

UPDATE gv_vouchervalue

SET vouchervalue = vouchervalue + @quantity

OUTPUT deleted.vouchervalue INTO @VoucherNumberTable

WHERE voucherabbreviation = 'V' + @retval;

SELECT @VoucherNumber = ISNULL(vouchervalue,0) FROM @VoucherNumberTable;

SELECT VoucherNumber = @VoucherNumber, retval = @retval

What values are returned?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

kapil_kk SSC-Insane Points: 21316 More actions · Answer 2

ChrisM@Work (5/8/2013)
Debugging a script like this is quite tricky without any data to test against. Try running this:
--ALTER Procedure [dbo].[BS_Voucher_CreateVoucher]
DECLARE
@vouchertypeID int = 1,
@denomination int = 100,
@quantity int = 10,
@amountvalue int = 100,
@userid varchar(50) = 1,
@validateDays int = 40
--AS
--BEGIN
SET NOCOUNT ON;
DECLARE
--@vouchertype varchar(20),
@transactionID varchar(20),
--@count int =1,
@VoucherNumber int, -- was @max-2
@vouchertypenew varchar(20),
@retval varchar(20),
@voucherstatus int
-- table variables receive OUTPUT
DECLARE @VoucherNumberTable TABLE (vouchervalue INT);
DECLARE @transactionIDTable TABLE (transactionID INT);
--------------------------------------------------------------------------------------
-- Load some variables. Exit immediately if vouchertype is invalid
--------------------------------------------------------------------------------------
SELECT
@vouchertypenew = SUBSTRING(TrimmedVoucherType,1+CHARINDEX(' ',TrimmedVoucherType,1),20),
@retval = LEFT(TrimmedVoucherType,1)
+ ISNULL(SUBSTRING(TrimmedVoucherType,1+NULLIF(CHARINDEX(' ',TrimmedVoucherType,1),0),1),'')
FROM GV_VoucherType v
CROSS APPLY (SELECT TrimmedVoucherType = LTRIM(RTRIM(v.vouchertype))) x
WHERE v.VoucherTypeID = @vouchertypeID;
IF @vouchertypenew IS NULL
RETURN -1
SELECT @voucherstatus = VoucherStatusId FROM GV_VoucherStatus WHERE VoucherStatus = 'New';
--------------------------------------------------------------------------------------
-- Read existing @VoucherNumber, set next @VoucherNumber
--------------------------------------------------------------------------------------
-- save off the current value to @VoucherNumber
-- and update to the next available value
UPDATE gv_vouchervalue
SET vouchervalue = vouchervalue + @quantity
OUTPUT deleted.vouchervalue INTO @VoucherNumberTable
WHERE voucherabbreviation = 'V' + @retval;
SELECT @VoucherNumber = ISNULL(vouchervalue,0) FROM @VoucherNumberTable;
SELECT VoucherNumber = @VoucherNumber, retval = @retval
What values are returned?

These values were returned:

VoucherIdVoucherTypeIdVoucherNoDenominationExpiryDateCreatedDateModifyDateVoucherStatusIdTransactionIDQuantityAmountValueCreatedByModifiedByValidatedaysIsDeleted

541VVB00000011002013-11-08 14:07:27.2572013-05-082013-05-08 14:07:27.2571TRN000003210011400

551VVB00000021002013-11-08 14:07:27.2572013-05-082013-05-08 14:07:27.2571TRN000003210011400

561VVB00000031002013-11-08 14:07:27.2572013-05-082013-05-08 14:07:27.2571TRN000003210011400

571VVB00000041002013-11-08 14:07:27.2572013-05-082013-05-08 14:07:27.2571TRN000003210011400

581VVB00000051002013-11-08 14:07:27.2572013-05-082013-05-08 14:07:27.2571TRN000003210011400

591VVB00000061002013-11-08 14:07:27.2572013-05-082013-05-08 14:07:27.2571TRN000003210011400

601VVB00000071002013-11-08 14:07:27.2572013-05-082013-05-08 14:07:27.2571TRN000003210011400

611VVB00000081002013-11-08 14:07:27.2572013-05-082013-05-08 14:07:27.2571TRN000003210011400

621VVB00000091002013-11-08 14:07:27.2572013-05-082013-05-08 14:07:27.2571TRN000003210011400

631VVB00000101002013-11-08 14:07:27.2572013-05-082013-05-08 14:07:27.2571TRN000003210011400

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

kapil_kk SSC-Insane Points: 21316 More actions · Answer 3

Also, I am not clear what this will do please explain.. I also want to learn error handling and transaction for this script:

WITH E1(N) AS (

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1),

E2(N) AS (SELECT 1 FROM E1 a, E1 b),

iTally(N) AS (SELECT n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM E2 a, E2 b)

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

The last SELECT in that script is

SELECT VoucherNumber = @VoucherNumber, retval = @retval

What values are returned?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

kapil_kk SSC-Insane Points: 21316 More actions · Answer 5

ChrisM@Work (5/8/2013)
The last SELECT in that script is
SELECT VoucherNumber = @VoucherNumber, retval = @retval
What values are returned?

VoucherNumberretval

0VB

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

kapil_kk (5/8/2013)
ChrisM@Work (5/8/2013)
The last SELECT in that script is
SELECT VoucherNumber = @VoucherNumber, retval = @retval
What values are returned?
VoucherNumberretval
0VB

Thanks.

Either the table gv_vouchervalue doesn't have a row where voucherabbreviation = 'VVB', or it does, and the value of vouchervalue is null.

I'd recommend that since @retval (used to select a row in gv_vouchervalue) is derived from column vouchertype in table GV_VoucherType, the two tables should be maintained together rather than cobbling up a fix in this stored procedure. Can you fix the data in these two tables?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

kapil_kk SSC-Insane Points: 21316 More actions · Answer 7

ChrisM@Work (5/8/2013)
kapil_kk (5/8/2013)
ChrisM@Work (5/8/2013)
The last SELECT in that script is
SELECT VoucherNumber = @VoucherNumber, retval = @retval
What values are returned?
VoucherNumberretval
0VB
Thanks.
Either the table gv_vouchervalue doesn't have a row where voucherabbreviation = 'VVB', or it does, and the value of vouchervalue is null.
I'd recommend that since @retval (used to select a row in gv_vouchervalue) is derived from column vouchertype in table GV_VoucherType, the two tables should be maintained together rather than cobbling up a fix in this stored procedure. Can you fix the data in these two tables?

This is the data in the gv_vouchervalue:

VoucherAbbreviationVoucherValue

VVBNULL

VVD27

VPD20

BVB8489776

BVD2766

BPD711459

TRNID32

here VVB is not updated and its null... I think due tot his next time if I run the script for the same values then it start generating same voucher number which is already exists and throw the error

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

Yes that's correct.

Here's an updated script which is adjusted to account for NULL values of VoucherValue:

--ALTER Procedure [dbo].[BS_Voucher_CreateVoucher]

DECLARE

@vouchertypeID int = 1,

@denomination int = 100,

@quantity int = 10,

@amountvalue int = 100,

@userid varchar(50) = 1,

@validateDays int = 40

--AS

--BEGIN

SET NOCOUNT ON;

DECLARE

--@vouchertype varchar(20),

@transactionID varchar(20),

--@count int =1,

@VoucherNumber int, -- was @max-2

@vouchertypenew varchar(20),

@retval varchar(20),

@voucherstatus int

-- table variables receive OUTPUT

DECLARE @VoucherNumberTable TABLE (vouchervalue INT);

DECLARE @transactionIDTable TABLE (transactionID INT);

--------------------------------------------------------------------------------------

-- Load some variables. Exit immediately if vouchertype is invalid

--------------------------------------------------------------------------------------

SELECT

@vouchertypenew = SUBSTRING(TrimmedVoucherType,1+CHARINDEX(' ',TrimmedVoucherType,1),20),

@retval = LEFT(TrimmedVoucherType,1)

+ ISNULL(SUBSTRING(TrimmedVoucherType,1+NULLIF(CHARINDEX(' ',TrimmedVoucherType,1),0),1),'')

FROM GV_VoucherType v

CROSS APPLY (SELECT TrimmedVoucherType = LTRIM(RTRIM(v.vouchertype))) x

WHERE v.VoucherTypeID = @vouchertypeID;

IF @vouchertypenew IS NULL

RETURN -1

SELECT @voucherstatus = VoucherStatusId FROM GV_VoucherStatus WHERE VoucherStatus = 'New';

--------------------------------------------------------------------------------------

-- Read existing @VoucherNumber, set next @VoucherNumber

--------------------------------------------------------------------------------------

-- save off the current value to @VoucherNumber

-- and update to the next available value

UPDATE gv_vouchervalue

SET vouchervalue = ISNULL(vouchervalue,0) + @quantity

OUTPUT deleted.vouchervalue INTO @VoucherNumberTable

WHERE voucherabbreviation = 'V' + @retval;

SELECT @VoucherNumber = ISNULL(vouchervalue,0) FROM @VoucherNumberTable;

--------------------------------------------------------------------------------------

-- Read existing @transactionID, set next @transactionID

--------------------------------------------------------------------------------------

-- save off the current value to @transactionID

-- and update to the next available value

UPDATE gv_vouchervalue

SET VoucherValue = VoucherValue + 1

OUTPUT deleted.vouchervalue INTO @transactionIDTable

WHERE VoucherAbbreviation = 'TRNID'

SELECT @transactionID = ISNULL(vouchervalue,0) FROM @transactionIDTable;

--------------------------------------------------------------------------------------

-- Insert required number of rows into GV_Voucher

--------------------------------------------------------------------------------------

-- Inline tally table generates (@quantity) rows

WITH E1(N) AS (

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1),

E2(N) AS (SELECT 1 FROM E1 a, E1 b),

iTally(N) AS (SELECT n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM E2 a, E2 b)

-- USE A COLUMN LIST!!

-- If the column order is changed or a column is added or removed,

-- an insert without a column list will fail.

-- A column list is free documentation. What does

-- 'TRN' + Replace( Str( @transactionID + 1, 7), ' ', '0') go into?

INSERT INTO GV_Voucher

SELECT TOP(@quantity)

@vouchertypeID,

'V'+@retval + Replace( Str( @VoucherNumber + t.n, 7), ' ', '0'),

@denomination,

DATEADD(MM,6,GETDATE()),

GETDATE(),

@voucherstatus,

'TRN' + REPLACE( Str( @transactionID + 1, 7), ' ', '0'),

@quantity,

CASE @vouchertypenew

WHEN 'Discount' THEN @amountvalue

ELSE 0

END,

@userid,

@validateDays,

0

FROM iTally t;

--UPDATE gv_vouchervalue

--SET vouchervalue = @VoucherNumber + @count

--WHERE voucherabbreviation = 'V'+@retval

--UPDATE gv_vouchervalue

--SET VoucherValue = @transactionID + 1

--WHERE VoucherAbbreviation = 'TRNID'

--END

RETURN 0

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

kapil_kk SSC-Insane Points: 21316 More actions · Answer 9

ChrisM@Work (5/8/2013)
Yes that's correct.
Here's an updated script which is adjusted to account for NULL values of VoucherValue:
--ALTER Procedure [dbo].[BS_Voucher_CreateVoucher]
DECLARE
@vouchertypeID int = 1,
@denomination int = 100,
@quantity int = 10,
@amountvalue int = 100,
@userid varchar(50) = 1,
@validateDays int = 40
--AS
--BEGIN
SET NOCOUNT ON;
DECLARE
--@vouchertype varchar(20),
@transactionID varchar(20),
--@count int =1,
@VoucherNumber int, -- was @max-2
@vouchertypenew varchar(20),
@retval varchar(20),
@voucherstatus int
-- table variables receive OUTPUT
DECLARE @VoucherNumberTable TABLE (vouchervalue INT);
DECLARE @transactionIDTable TABLE (transactionID INT);
--------------------------------------------------------------------------------------
-- Load some variables. Exit immediately if vouchertype is invalid
--------------------------------------------------------------------------------------
SELECT
@vouchertypenew = SUBSTRING(TrimmedVoucherType,1+CHARINDEX(' ',TrimmedVoucherType,1),20),
@retval = LEFT(TrimmedVoucherType,1)
+ ISNULL(SUBSTRING(TrimmedVoucherType,1+NULLIF(CHARINDEX(' ',TrimmedVoucherType,1),0),1),'')
FROM GV_VoucherType v
CROSS APPLY (SELECT TrimmedVoucherType = LTRIM(RTRIM(v.vouchertype))) x
WHERE v.VoucherTypeID = @vouchertypeID;
IF @vouchertypenew IS NULL
RETURN -1
SELECT @voucherstatus = VoucherStatusId FROM GV_VoucherStatus WHERE VoucherStatus = 'New';
--------------------------------------------------------------------------------------
-- Read existing @VoucherNumber, set next @VoucherNumber
--------------------------------------------------------------------------------------
-- save off the current value to @VoucherNumber
-- and update to the next available value
UPDATE gv_vouchervalue
SET vouchervalue = ISNULL(vouchervalue,0) + @quantity
OUTPUT deleted.vouchervalue INTO @VoucherNumberTable
WHERE voucherabbreviation = 'V' + @retval;
SELECT @VoucherNumber = ISNULL(vouchervalue,0) FROM @VoucherNumberTable;
--------------------------------------------------------------------------------------
-- Read existing @transactionID, set next @transactionID
--------------------------------------------------------------------------------------
-- save off the current value to @transactionID
-- and update to the next available value
UPDATE gv_vouchervalue
SET VoucherValue = VoucherValue + 1
OUTPUT deleted.vouchervalue INTO @transactionIDTable
WHERE VoucherAbbreviation = 'TRNID'
SELECT @transactionID = ISNULL(vouchervalue,0) FROM @transactionIDTable;
--------------------------------------------------------------------------------------
-- Insert required number of rows into GV_Voucher
--------------------------------------------------------------------------------------
-- Inline tally table generates (@quantity) rows
WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1),
E2(N) AS (SELECT 1 FROM E1 a, E1 b),
iTally(N) AS (SELECT n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM E2 a, E2 b)
-- USE A COLUMN LIST!!
-- If the column order is changed or a column is added or removed,
-- an insert without a column list will fail.
-- A column list is free documentation. What does
-- 'TRN' + Replace( Str( @transactionID + 1, 7), ' ', '0') go into?
INSERT INTO GV_Voucher
SELECT TOP(@quantity)
@vouchertypeID,
'V'+@retval + Replace( Str( @VoucherNumber + t.n, 7), ' ', '0'),
@denomination,
DATEADD(MM,6,GETDATE()),
GETDATE(),
GETDATE(),
@voucherstatus,
'TRN' + REPLACE( Str( @transactionID + 1, 7), ' ', '0'),
@quantity,
CASE @vouchertypenew
WHEN 'Discount' THEN @amountvalue
ELSE 0
END,
@userid,
@userid,
@validateDays,
0
FROM iTally t;
--UPDATE gv_vouchervalue
--SET vouchervalue = @VoucherNumber + @count
--WHERE voucherabbreviation = 'V'+@retval
--UPDATE gv_vouchervalue
--SET VoucherValue = @transactionID + 1
--WHERE VoucherAbbreviation = 'TRNID'
--END
RETURN 0

Yess it worked now 🙂

Thanks a lot..

can we move fwd now towards error handling and transaction

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

kapil_kk SSC-Insane Points: 21316 More actions · Answer 10

kapil_kk (5/8/2013)
Also, I am not clear what this will do please explain.. I also want to learn error handling and transaction for this script:
WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1),
E2(N) AS (SELECT 1 FROM E1 a, E1 b),
iTally(N) AS (SELECT n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM E2 a, E2 b)

Chris, please explain me why you used this and what is the purpose of this...

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 11

kapil_kk (5/8/2013)
...
Yess it worked now 🙂
Thanks a lot..
can we move fwd now towards error handling and transaction

Sure. This bit first?

kapil_kk (5/8/2013)
Also, I am not clear what this will do please explain.. I also want to learn error handling and transaction for this script:
WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1),
E2(N) AS (SELECT 1 FROM E1 a, E1 b),
iTally(N) AS (SELECT n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM E2 a, E2 b)

This is an inline tally table. It's widely used on ssc to generate a numbers table on the fly, and also as a row generator. Run it in an SSMS window to see what it does:

WITH E1(N) AS (

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1),

E2(N) AS (SELECT 1 FROM E1 a, E1 b),

iTally(N) AS (SELECT n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM E2 a, E2 b)

SELECT n

FROM iTally; -- 10,000 rows, n = 1 to 10000

WITH E1(N) AS (

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1),

E2(N) AS (SELECT 1 FROM E1 a, E1 b),

iTally(N) AS (SELECT TOP (10) n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM E2 a, E2 b)

SELECT n

FROM iTally; -- 10 rows, n = 1 to 10

WITH E1(N) AS (

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1),

E2(N) AS (SELECT 1 FROM E1 a, E1 b),

iTally(N) AS (SELECT TOP (10) n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM E2 a, E2 b)

SELECT DATEADD(day,1-n,CAST(GETDATE() AS DATE))

FROM iTally; -- 10 rows, last 10 days including today

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

kapil_kk SSC-Insane Points: 21316 More actions · Answer 12

ChrisM@Work (5/8/2013)
kapil_kk (5/8/2013)
...
Yess it worked now 🙂
Thanks a lot..
can we move fwd now towards error handling and transaction
Sure. This bit first?
kapil_kk (5/8/2013)
Also, I am not clear what this will do please explain.. I also want to learn error handling and transaction for this script:
WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1),
E2(N) AS (SELECT 1 FROM E1 a, E1 b),
iTally(N) AS (SELECT n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM E2 a, E2 b)
This is an inline tally table. It's widely used on ssc to generate a numbers table on the fly, and also as a row generator. Run it in an SSMS window to see what it does:
WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1),
E2(N) AS (SELECT 1 FROM E1 a, E1 b),
iTally(N) AS (SELECT n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM E2 a, E2 b)
SELECT n
FROM iTally; -- 10,000 rows, n = 1 to 10000
WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1),
E2(N) AS (SELECT 1 FROM E1 a, E1 b),
iTally(N) AS (SELECT TOP (10) n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM E2 a, E2 b)
SELECT n
FROM iTally; -- 10 rows, n = 1 to 10
WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1),
E2(N) AS (SELECT 1 FROM E1 a, E1 b),
iTally(N) AS (SELECT TOP (10) n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM E2 a, E2 b)
SELECT DATEADD(day,1-n,CAST(GETDATE() AS DATE))
FROM iTally; -- 10 rows, last 10 days including today

OK thnks new thing to learn..

Will play with it....

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 13

Here's an excellent article to help you out:

http://www.sqlservercentral.com/articles/T-SQL/62867/[/url]

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

kapil_kk SSC-Insane Points: 21316 More actions · Answer 14

ChrisM@Work (5/8/2013)
Here's an excellent article to help you out:
http://www.sqlservercentral.com/articles/T-SQL/62867/[/url]

Ok thanks a lot.. 🙂

will go through this article after completing transaction work with this script...

plz help me on this

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 15

kapil_kk (5/8/2013)
ChrisM@Work (5/8/2013)
Here's an excellent article to help you out:
http://www.sqlservercentral.com/articles/T-SQL/62867/[/url]
Ok thanks a lot.. 🙂
will go through this article after completing transaction work with this script...
plz help me on this

Here's a popular and well-written artcile covering error handling in SQL Server 2005 / 2008:

http://www.sommarskog.se/error_handling_2005.html

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden