An Oracle programmer looking for T-SQL syntax related help!!
-
PLEASE HELP. MY DEADLINE IS COB TODAY
I have been an Oracle programmer for about 5 years now, this is my first time doing T-SQL programming. ANd it just seems like simple stuff that i can do with Oracle is taking me a while to figure out here. So SQL SERVER Gurus, please help me!
My stored procedure does something basic like, I am trying to look for a missing sequence number is my table using a group by function. And if there is a missing sequence number, then i want to insert that sequence into a table.
I don't understand how looping works in sql server. I mean how do i loop my cursor data between a minimum and a maximum value. In oracle i used to do something like, FOR i IN minval..maxval LOOP.. How do i translate something like this in T-SQL.
My stored procedure looks as follows. And where i am having syntax problem is highlighted in red and information blue.
dbsequenceid is the name of the column where i need to find the missing sequence.
My while loop is where i cannot seem to find the correct syntax.. What i want to do is i want to loop the data in the cursor between the v_minseq and v_maxseq. So i basically want something like, while ...>=v_minseq and ...<= v_maxseq. How do i do this? It has to be something simple that i am missing..
CREATE PROCEDURE missing_iconnt_data(@p_sitecode INTEGER,@p_fromsalesdate DATETIME,@p_tosalesdate DATETIME)
AS
BEGIN
DECLARE
@v_sitename varchar(80),
@v_siteglobalcode integer,
@v_salesdate datetime,
@v_minseq integer,
@v_maxseq integer,
@v_currentseq integer,
@v_validseq integer,
DECLARE cur_missing_data CURSOR for
SELECT sitename,siteglobalcode,salesdate,min(dbsequenceid) minseq,max(dbsequenceid) maxseq
FROM crm_stg_icon
WHERE salesdate BETWEEN CONVERT(DATETIME, @p_fromsalesdate) and CONVERT(DATETIME, @p_tosalesdate)
AND siteglobalcode = nullif(@p_sitecode,'')
GROUP BY sitename,siteglobalcode,salesdate
OPEN cur_missing_data
FETCH cur_missing_data INTO @v_sitename,@v_siteglobalcode,@v_salesdate,@v_minseq,@v_maxseq
SET @v_currentseq = @v_minseq
SET @v_validseq = @v_minseq
SET @v_loopseq1 = @v_minseq
SET @v_loopseq2 = @v_maxseq
WHILE ( ? >= @v_minseq AND ? <= @v_maxseq)
BEGIN
SET @v_validseq = @v_validseq + 1
SELECT @v_currentseq = crm_stg_icon.dbsequenceid
FROM crm_stg_icon
WHERE dbsequenceid = @v_validseq
AND siteglobalcode = @v_siteglobalcode
IF @v_currentseq = @v_validseq
BEGIN
set @v_currentseq = @v_currentseq + 1
END
ELSE
BEGIN
INSERT INTO tbl_missing_iconnt_date
VALUES (@v_sitename,@v_siteglobalcode,@v_salesdate,@v_validseq)
SET @v_currentseq = @v_currentseq + 2
END
FETCH cur_missing_data INTO @v_sitename,@v_siteglobalcode,@v_salesdate,@v_minseq,@v_maxseq
END
END
CLOSE cur_missing_data
DEALLOCATE cur_missing_data
-
Not sure if I understand completely, but if all you're asking for is to find gaps in a sequence, this is an easy task having a numbers table. I'll use SQL Server's own master..spt_values, but I think you'll be better off creating your own helper table. See if this helps:
CREATE TABLE T
(
n INT NOT NULL PRIMARY KEY
)
INSERT INTO T (n)
SELECT '1'
UNION ALL
SELECT '2'
UNION ALL
SELECT '3'
UNION ALL
SELECT '4'
UNION ALL
SELECT '5'
UNION ALL
SELECT '7'
UNION ALL
SELECT '9'
UNION ALL
SELECT '10'
SELECT
number
FROM
master..spt_values Numbers
WHERE
type='P'
AND NOT EXISTS
(SELECT *
FROM T
WHERE n = Numbers.Number)
AND Numbers.Number BETWEEN 1 AND 10
DROP TABLE t
number
-----------
6
8
(2 row(s) affected)
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
If your looking for another way to teminate the while loop you can use BREAK.
example:
While 1 = 1
... your code
if <condition to terminate loop>
BREAK
FETCH cur_missing_data INTO ...
End
From Books Online:
BREAK
Causes an exit from the innermost WHILE loop. Any statements appearing after the END keyword, marking the end of the loop, are executed.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply