April 15, 2010 at 4:17 am
Hi all,
Below is the query that I m using to insert values in variables to the columns of a table
CREATE TABLE #TEMP_ABSENT_TBL(CNO VARCHAR(20), EMPNAME VARCHAR(100), AB_DATE VARCHAR(15))
INSERT INTO #TEMP_ABSENT_TBL VALUES(@ZCNO, @ZEMPNAME, CONVERT(VARCHAR(15), @CURDATE, 101))
I m using the above query inside a Stored Procedure and hence it is an temporary table.
In while loop the control doesn't move from this INSERT INTO statement to the next statement.
Please tell me where I m wrong.
Sachin
April 15, 2010 at 4:43 am
Why are you using a While Loop?
that part of the code looks fine as long as the variables are declared, can you post the entire code to the stored proc?
April 15, 2010 at 4:52 am
ALTER PROCEDURE absent_report
@FROMDATE DATETIME,
@TODATE DATETIME
AS
BEGIN
CREATE TABLE #TEMP_ABSENT_TBL(CNO VARCHAR(20), EMPNAME VARCHAR(100), AB_DATE VARCHAR(15))
INSERT INTO #TEMP_ABSENT_TBL(CNO, EMPNAME, AB_DATE)
SELECT CardID, FirstName + ' ' + ISNULL(LastName, ' '), ' ' FROM Card
CREATE TABLE #TEMP_TRANS_TBL(CNO VARCHAR(20), LOGDATE VARCHAR(15))
INSERT INTO #TEMP_TRANS_TBL(CNO, LOGDATE)
SELECT CardNumber, CONVERT(VARCHAR(15), Time, 101)
FROM Transactionlog
WHERE (CONVERT(VARCHAR(15), Time, 101) >= @FROMDATE) AND (CONVERT(VARCHAR(15), Time, 101) <= @TODATE)
DECLARE CUR CURSOR
FOR
SELECT CNO, EMPNAME FROM #TEMP_ABSENT_TBL
DECLARE @ZCNO VARCHAR(20), @CURDATE DATETIME, @FLAG SMALLINT, @ZEMPNAME VARCHAR(100)
OPEN CUR
FETCH NEXT FROM CUR INTO @ZCNO, @ZEMPNAME
WHILE(@@FETCH_STATUS = 0)
BEGIN
IF((SELECT COUNT(*) FROM #TEMP_TRANS_TBL WHERE CNO = @ZCNO AND LOGDATE = @CURDATE) > 0)
BEGIN
DELETE FROM #TEMP_TRANS_TBL WHERE CNO = @ZCNO AND LOGDATE = @CURDATE
SET @FLAG = 1
END
IF(@FLAG = 0)
INSERT INTO #TEMP_ABSENT_TBL(CNO, EMPNAME, AB_DATE)
SELECT @ZCNO, @ZEMPNAME, CONVERT(VARCHAR(15), @CURDATE, 101)
SET @CURDATE = DATEADD(DAY, 1, @CURDATE)
SET @FLAG = 0
IF(@CURDATE <= @TODATE)
CONTINUE
SET @CURDATE = @FROMDATE
SET @FLAG = 0
FETCH NEXT FROM CUR INTO @ZCNO, @ZEMPNAME
END
CLOSE CUR
DEALLOCATE CUR
SELECT * FROM #TEMP_ABSENT_TBL ORDER BY EMPNAME, AB_DATE
DROP TABLE #TEMP_ABSENT_TBL
DROP TABLE #TEMP_TRANS_TBL
END
April 15, 2010 at 5:17 am
there are temp tables, cursors and a while loop. There must be a way to make this code a bit more efficent by using set based code.
From what i can tell you are trying to find the cards in Transactionlog that have transactions that are not equal to CurDate ?
could you explain what the code is trying to acheive?
also i cannot find where you give a value to @CurDate so this may be a problem
April 15, 2010 at 5:32 am
sachinrshetty (4/15/2010)
Hi all,Below is the query that I m using to insert values in variables to the columns of a table
CREATE TABLE #TEMP_ABSENT_TBL(CNO VARCHAR(20), EMPNAME VARCHAR(100), AB_DATE VARCHAR(15))
INSERT INTO #TEMP_ABSENT_TBL VALUES(@ZCNO, @ZEMPNAME, CONVERT(VARCHAR(15), @CURDATE, 101))
I m using the above query inside a Stored Procedure and hence it is an temporary table.
In while loop the control doesn't move from this INSERT INTO statement to the next statement.
Please tell me where I m wrong.
Sachin
ALTER PROCEDURE absent_report
@FROMDATE DATETIME,
@TODATE DATETIME
AS
BEGIN
CREATE TABLE #TEMP_ABSENT_TBL(CNO VARCHAR(20), EMPNAME VARCHAR(100), AB_DATE VARCHAR(15))
INSERT INTO #TEMP_ABSENT_TBL(CNO, EMPNAME, AB_DATE)
SELECT CardID, FirstName + ' ' + ISNULL(LastName, ' '), ' '
FROM Card
CREATE TABLE #TEMP_TRANS_TBL(CNO VARCHAR(20), LOGDATE VARCHAR(15))
INSERT INTO #TEMP_TRANS_TBL(CNO, LOGDATE)
SELECT CardNumber, CONVERT(VARCHAR(15), Time, 101)
FROM Transactionlog
WHERE (CONVERT(VARCHAR(15), Time, 101) >= @FROMDATE) AND (CONVERT(VARCHAR(15), Time, 101) <= @TODATE)
DECLARE CUR CURSOR FOR SELECT CNO, EMPNAME FROM #TEMP_ABSENT_TBL
DECLARE @ZCNO VARCHAR(20), @CURDATE DATETIME, @FLAG SMALLINT, @ZEMPNAME VARCHAR(100)
OPEN CUR
FETCH NEXT FROM CUR INTO @ZCNO, @ZEMPNAME
WHILE(@@FETCH_STATUS = 0)
BEGIN
IF((SELECT COUNT(*) FROM #TEMP_TRANS_TBL WHERE CNO = @ZCNO AND LOGDATE = @CURDATE) > 0)
BEGIN
DELETE FROM #TEMP_TRANS_TBL WHERE CNO = @ZCNO AND LOGDATE = @CURDATE
SET @FLAG = 1
END
IF(@FLAG = 0) -- missing BEGIN / END?
INSERT INTO #TEMP_ABSENT_TBL(CNO, EMPNAME, AB_DATE)
SELECT @ZCNO, @ZEMPNAME, CONVERT(VARCHAR(15), @CURDATE, 101)
SET @CURDATE = DATEADD(DAY, 1, @CURDATE)
SET @FLAG = 0
IF(@CURDATE <= @TODATE)
CONTINUE
SET @CURDATE = @FROMDATE
SET @FLAG = 0
FETCH NEXT FROM CUR INTO @ZCNO, @ZEMPNAME
END
CLOSE CUR
DEALLOCATE CUR
SELECT * FROM #TEMP_ABSENT_TBL ORDER BY EMPNAME, AB_DATE
DROP TABLE #TEMP_ABSENT_TBL
DROP TABLE #TEMP_TRANS_TBL
END
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
April 15, 2010 at 2:12 pm
@chris-2: When did you start promoting loops??? :crying:
Or do I miss something that prevent doing it set based?
April 16, 2010 at 2:39 am
lmu92 (4/15/2010)
@Chris: When did you start promoting loops??? :crying:Or do I miss something that prevent doing it set based?
This is what happens when you moonlight as a shower-curtain salesman, Lutz 😛
There's no reason why this shouldn't be set based, it would run a hell of a lot faster with less than half the code. It's tempting to tackle it based on the given query but how often have you spent a couple of hours writing a set-based equivalent of an OP's spaghetti mess only to find that the logic of the spaghetti mess is completely different to the actual requirements and you have to start all over again?
I'll wait for the fag-packet spec 😀
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
April 16, 2010 at 9:42 am
Chris Morris-439714 (4/16/2010)
...
This is what happens when you moonlight as a shower-curtain salesman, Lutz 😛
...
Are you not satisfied working with SQL anymore? And do you sell knifes as well wearing an Alfred Hitchcock costume (then the job would make sense, again :-D)?
April 19, 2010 at 2:08 am
lmu92 (4/16/2010)
Chris Morris-439714 (4/16/2010)
...
This is what happens when you moonlight as a shower-curtain salesman, Lutz 😛
...
Are you not satisfied working with SQL anymore? And do you sell knifes as well wearing an Alfred Hitchcock costume (then the job would make sense, again :-D)?
Lutz there's no way I'm gonna chase you round the hotel dressed like your mother!
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
April 20, 2010 at 7:28 am
Wouldn't you be dressed like your own mother? Lutz is just the innocent victim in this scenario. 😉
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply