I ran through in one issue while working in one of the stored procedure and using SELECT clause while variable assignment. And I suffered this issue in a live production environment and due to this it was killing the Server CPU.
Finally, I got the issue and resolved it. The issue was It did not take care of variable assignment with SELECT clause. In my earlier post I have written about SET v's SELECT for variable assignment and I discussed the same issue with overview but not given details here. I am going to complete the details and share the issue with you with examples now.
Now toward the solution I am going to use SET for variable assignment in below revised script.
This is just what I want to share. What you want to comment here?
Finally, I got the issue and resolved it. The issue was It did not take care of variable assignment with SELECT clause. In my earlier post I have written about SET v's SELECT for variable assignment and I discussed the same issue with overview but not given details here. I am going to complete the details and share the issue with you with examples now.
CREATE TABLE FromTab ( Id INT ,Name VARCHAR(50) ) GO CREATE TABLE ToTab ( Id INT ,Name VARCHAR(50) ) GO -- Inserting demo records INSERT FromTab SELECT 1 ,'First' UNION ALL SELECT 2 ,'Second' UNION ALL SELECT 3 ,'Third' UNION ALL SELECT 4 ,'Fourth' UNION ALL SELECT 5 ,'Fifth' GOAs discussed we are using SELECT clause for variable assignment here in below script.
DECLARE @Var INT DECLARE @Startdate DATETIME SET @Startdate = Getdate() SELECT TOP 1 @Var = Id FROM FromTab WHILE EXISTS(SELECT 1 FROM FromTab WHERE Id <= @Var) BEGIN PRINT CONVERT(VARCHAR,DATEDIFF(SECOND,@Startdate,GETDATE())) + ' Seconds' INSERT INTO ToTab SELECT Id ,Name FROM FromTab WHERE Id = @Var SELECT TOP 1 @Var = Id FROM FromTab WHERE Id > @Var END GO
Now toward the solution I am going to use SET for variable assignment in below revised script.
-- First truncate table before go ahead with next TRUNCATE TABLE ToTab DECLARE @Var INT DECLARE @Startdate DATETIME SET @Startdate = Getdate() SELECT TOP 1 @Var = Id FROM FromTab WHILE EXISTS(SELECT 1 FROM FromTab WHERE Id <= @Var) BEGIN PRINT CONVERT(VARCHAR,DATEDIFF(SECOND,@Startdate,GETDATE())) + ' Seconds' INSERT INTO ToTab SELECT Id ,Name FROM FromTab WHERE Id = @Var -- Comment start --SELECT TOP 1 -- @Var = Id --FROM FromTab --WHERE Id > @Var -- Comment end -- Added code and used SET SET @Var = (SELECT TOP 1 Id FROM FromTab WHERE Id > @Var) -- End Adding END GO
This is just what I want to share. What you want to comment here?
Comments
Post a Comment