Monday, May 6, 2013

[T-SQL] Isolation Level

[T-SQL] Isolation Level

Isolation Level

Posted: 06 May 2013 12:27 AM PDT

Hi, I have a scenario in which I am performing insertion on a table using a stored procedure. For every insertion there is a ID generation for the rows like if a quantity is 100 then for 100 rows there will be a single transaction ID as there will be 100 unique vouchers. Suppose if I inserted 1000 rows who should get ID 001 but on another screen I am again performing inserting then it should get another unique Id say 002 but I am getting same ID for both windows.How to achieve this plz tell?

conversion of the varchar value overflowed an int column

Posted: 05 May 2013 06:34 PM PDT

I have a stored procedure :In this prcedure I am returning @pSTN value and used in another sp as output. @pSTn value is generating on basis of From Store and suppose if my from store is 1001 thn @pstn value like '1001000111' is will retun no error but when my @fromstore variable is > 2 i.e. 3001 or 400 it will start giving me error 'The conversion of the varchar value '4001000012' overflowed an int column'.To prevent that i used CAST in the RETURN statment of the stored procedure but then it giving me error:'Arithmetic overflow error converting expression to data type int.'How to solve this?ALTER PROCEDURE [dbo].[BS_GetSTNValue] --DECLARE @pFromStore int = 1001 ,@pToStore int = 1004 ,@pSTN VARCHAR(20) = 0 output ASBEGINIF NOT EXISTS ( SELECT 1 FROM GV_STNDetails gs WHERE gs.From_StoreCode = @pFromStore AND gs.TO_StoreCode = @pToStore AND ISNULL(gs.AWBNo,0) = 0 )BEGININSERT INTO GV_STNDetails (From_StoreCode, TO_StoreCode) VALUES (@pFromStore,@pToStore)ENDDECLARE @count INT = 0 ,@AWBNo VARCHAR(10) = 0, @checkSTN varchar(10) SET @checkSTN = ( SELECT TOP 1 ISNULL(gs.STNNO, 0) FROM GV_STNDetails gs WHERE gs.From_StoreCode = @pFromStore AND gs.TO_StoreCode = @pToStore ) PRINT @checkSTN SET @AWBNo = ( SELECT TOP 1 ISNULL(gs.AWBNo, 0) FROM GV_STNDetails gs WHERE gs.From_StoreCode = @pFromStore AND gs.TO_StoreCode = @pToStore ) PRINT @AWBNo SET @count = ( SELECT gsv.CountValue FROM GV_STNCountValues gsv WHERE gsv.StoreCode = @pFromStore ) IF @AWBNo = 0 BEGIN IF CAST(@checkSTN as BIGINT) = 0 BEGIN SET @pSTN = CONVERT(VARCHAR, REPLACE(STR(@pFromStore, 4), ' ', 0)) + CONVERT(VARCHAR, REPLACE(STR(@count + 1, 6), ' ', '0')) PRINT 'WHEn STN & AWB is 0 ' + @pSTN UPDATE GV_STNDetails SET STNNO = @pSTN WHERE From_StoreCode = @pFromStore AND TO_StoreCode = @pToStore AND ISNULL(AWBNo,0) = 0 UPDATE GV_STNCountValues SET CountValue = CountValue + 1 WHERE StoreCode = @pFromStore END ELSE BEGIN SET @pSTN = (SELECT TOP 1 gs.STNNO FROM GV_STNDetails gs WHERE gs.From_StoreCode = @pFromStore AND gs.TO_StoreCode = @pToStore AND ISNULL(AWBNo,0) = 0) UPDATE GV_STNDetails SET STNNO = @pSTN WHERE From_StoreCode = @pFromStore AND TO_StoreCode = @pToStore AND ISNULL(AWBNo,0) = 0 PRINT 'same ' + @pstn END END ELSE BEGIN DECLARE @oldSTN VARCHAR(10) SET @oldSTN = ( SELECT TOP 1 gs.STNNO FROM GV_STNDetails gs WHERE gs.From_StoreCode = @pFromStore AND gs.TO_StoreCode = @pToStore AND ISNULL(gs.AWBNo,0) <> 0 ) SET @pSTN = CONVERT(VARCHAR, REPLACE(STR(@pFromStore, 4), ' ', 0)) + CONVERT(VARCHAR, REPLACE(STR(RIGHT(@oldSTN, 6) + 1,6),' ',0)) PRINT @pSTN UPDATE GV_STNDetails SET STNNO = @pSTN WHERE From_StoreCode = @pFromStore AND TO_StoreCode = @pToStore AND ISNULL(AWBNo,0) = 0 PRINT 'awb ' + @pSTN END RETURN @pSTN END

Understanding CASE processing

Posted: 05 May 2013 11:14 PM PDT

Hi,I found an SP that generates multiple index seeks from 1 case statement.By the looks of it each WHEN THEN is run regardless if the WHEN is true or not.Do CASE statements always behave like this or can they be forced to only execute when true thus skipping the rest.? [code="sql"]----------------------------------------------------- Test 1 CASE Multiple COLUMNS---------------------------------------------------CREATE TABLE #Products ( ProductNr Int PRIMARY KEY, Price1 Money, Price2 Money, Price3 Money, Price4 Money, Price5 Money )-- ParametersDECLARE @InputVal Char(1), @ProdNr IntSELECT @InputVal = '1', @ProdNr = 10-- VariablesDECLARE @Price Money-- Execution plan returns multiple index seeksSET @Price = ( CASE WHEN @InputVal = '1' THEN (SELECT Price1 FROM #Products WHERE ProductNr = @ProdNr) WHEN @InputVal = '2' THEN (SELECT Price2 FROM #Products WHERE ProductNr = @ProdNr) WHEN @InputVal = '3' THEN (SELECT Price3 FROM #Products WHERE ProductNr = @ProdNr) WHEN @InputVal = '4' THEN (SELECT Price4 FROM #Products WHERE ProductNr = @ProdNr) ELSE (SELECT Price5 FROM #Products WHERE ProductNr = @ProdNr) END )-- Execution plan returns 1 index seekSET @Price = ( SELECT CASE @InputVal WHEN '1' THEN Price1 WHEN '2' THEN Price2 WHEN '3' THEN Price3 WHEN '4' THEN Price4 ELSE Price5 END FROM #Products WHERE ProductNr = @ProdNr )DROP TABLE #Products[/code]Thanks

Indexed view CI chosen of table CI. Why?

Posted: 05 May 2013 11:50 PM PDT

Hi,This is related to the table and query in question: [url]http://www.sqlservercentral.com/Forums/Topic1449684-392-1.aspx[/url](Hope this is clear, otherwise I have to double post them).A view on the above mentioned table aliases and shifts the column names.[code="sql"]CREATE VIEW vwProductsWITH SCHEMABINDINGAS SELECT ProductNr, Price1 [Price2], Price2 [Price3], Price3 [Price4], Price4 [Price5], Price5 [Price0] FROM dbo.ProductsGOCREATE UNIQUE CLUSTERED INDEX UCI_Products_ProdNr ON vwProducts (ProductNr)[/code]The clustered index on the Products table is the same, except for the name (PK_ProdNr).Why does the optimizer find the views clustered index sexier than the tables?Especially since the view is not even used in the query.

Extract string between two special characters upto 4 sublevels

Posted: 05 May 2013 03:25 PM PDT

Hi Everyone,Can anyone of you please help me to get the T-SQL to extract a string between two special characters upto three subfolder levels.Example: /IT/Management, /PRODUCTION/DESIGN/Service/Billing/Cash//Service/Billing/Cash/FullMy Result should be in the Below format MainFolderName SubFolderLevel1 SubFolderLevel2 SubFolderLevel3IT MANAGEMENT NULL NULLPRODUCTION DESIGN NULL NULL Service Billing Cash NULLService Billing Cash FULLThanks in advanceSrikanth Reddy Kundur
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog