October 29, 2015 at 3:33 pm
This store procedure will get some executable queries from the select statement, the cursor will fetch each rows to execute the query and insert the queries into table_3 to mark as 'E'. Until 17:00, this store procedure will stop execute the queries and just get the queries from select statement insert into table_3 to mark as 'C'.
I don't know why the outputs in table_3 are quiet different than I think. This store procedure comes out with two exactly same queries and one marked as C and another marked as E.
CREATE PROCEDURE procedure1
AS
DECLARE cursor_1 CURSOR FOR
SELECT
'This is a executable query'
FROM table_1
DECLARE @table_2
DECLARE @stoptime DATETIME = NULL;
SET @stoptime = CONVERT( CHAR(8), GetDate(), 14);
OPEN cursor_1
FETCH NEXT FROM cursor_1 INTO @table_2;
WHILE (@@FETCH_STATUS = 0)
IF (@stoptime < '17:00:00')
BEGIN
EXEC sp_executesql @table_2
INSERT INTO table_3 VALUES (@table_2,'C')
FETCH NEXT FROM cursor_1 INTO @table_2
END
ELSE
BEGIN
INSERT INTO table_3 VALUES (@table_2,'E');
END
FETCH NEXT FROM cursor_1 INTO @table_2
CLOSE cursor_1;
DEALLOCATE cursor_1;
October 30, 2015 at 2:26 am
Sure there will be a better way to do it than a cursor.
Could you please provide definitions of the tables in question, with some sample data and an expected outcome.
Please see the link in my signature for posting code & data for help with this.
October 30, 2015 at 2:59 am
Your last FETCH is beyond of WHILE loop. Is it what you really mean? Try
CREATE PROCEDURE procedure1
AS
DECLARE cursor_1 CURSOR FOR
SELECT
'This is a executable query'
FROM table_1
DECLARE @table_2
DECLARE @stoptime DATETIME = NULL;
SET @stoptime = CONVERT( CHAR(8), GetDate(), 14);
OPEN cursor_1
FETCH NEXT FROM cursor_1 INTO @table_2;
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF (@stoptime < '17:00:00')
BEGIN
EXEC sp_executesql @table_2
INSERT INTO table_3 VALUES (@table_2,'C')
END
ELSE
BEGIN
INSERT INTO table_3 VALUES (@table_2,'E');
END
FETCH NEXT FROM cursor_1 INTO @table_2;
END
CLOSE cursor_1;
DEALLOCATE cursor_1;
October 30, 2015 at 7:18 am
I changed the query like this, but then the query just keeping execute the outcomes and marked as all 'C', even it passed the specific time.
October 30, 2015 at 7:24 am
The table should have two columns, one is for the executable query and the status. Should looks like this
Query Status
'Execute statement1' C
'Execute statement2' E
This cursor will fetch each rows and see if the time is over 17:00:00. If it is not over 5 PM, execute the statement and mark as 'C'. If it is over 5 PM, then just insert into the table and mark as 'E'.
October 30, 2015 at 7:32 am
If you need the procedure to behave differently within the same run you need to place
SET @stoptime = CONVERT( CHAR(8), GetDate(), 14);
within the WHILE loop too .
October 30, 2015 at 8:19 am
So I changed the store procedure like this, but it didn't bring me any outputs in the table at all.
CREATE PROCEDURE procedure1
AS
DECLARE cursor_1 CURSOR FOR
SELECT
'This is a executable query'
FROM table_1
DECLARE @table_2
DECLARE @stoptime DATETIME = NULL;
OPEN cursor_1
FETCH NEXT FROM cursor_1 INTO @table_2;
WHILE (@@FETCH_STATUS = 0)
SET @stoptime = CONVERT( CHAR(8), GetDate(), 14);
BEGIN
IF (@stoptime < '17:00:00')
BEGIN
EXEC sp_executesql @table_2
INSERT INTO table_3 VALUES (@table_2,'C')
END
ELSE
BEGIN
INSERT INTO table_3 VALUES (@table_2,'E');
END
FETCH NEXT FROM cursor_1 INTO @table_2;
END
CLOSE cursor_1;
DEALLOCATE cursor_1;
October 30, 2015 at 8:50 am
Too many errors. Avoid writing too much at once without testing.
DROP TABLE #table_1;CREATE TABLE #table_1 (StatementName VARCHAR(500))
INSERT INTO #table_1 (StatementName) VALUES ('Nothing')
DROP TABLE #table_3;CREATE TABLE #table_3 (StatementName VARCHAR(500), Flag CHAR(1))
DECLARE @table_2 VARCHAR(500) -- ERROR, NOT DEFINED
DECLARE @stoptime DATETIME = NULL;
DECLARE cursor_1 CURSOR FOR
SELECT N'SELECT GETDATE()' FROM #table_1
OPEN cursor_1
FETCH NEXT FROM cursor_1 INTO @table_2;
SET @stoptime = CONVERT( CHAR(8), GetDate(), 14);
-- WHILE must be followed immediately by BEGIN if
-- the content of the loop is more than one statement.
-- If WHILE is followed by any statement other than BEGIN,
-- then that statement is run and the loop ends.
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF @stoptime < '17:00:00'
BEGIN
EXEC (@table_2)
INSERT INTO #table_3 VALUES (@table_2,'C')
END
ELSE
BEGIN
INSERT INTO #table_3 VALUES (@table_2,'E');
END
FETCH NEXT FROM cursor_1 INTO @table_2;
SET @stoptime = CONVERT( CHAR(8), GetDate(), 14);
END
CLOSE cursor_1;
DEALLOCATE cursor_1;
SELECT * FROM #table_3
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
October 30, 2015 at 1:41 pm
Thanks a lot and it works very good.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply