@@identity

I'm running the following within a For Next loop but the
@.@.identity value doesn't always show up. It only shows
for every 3rd record. Any ideas on why this might be?
Thanks,
Ben Bleser
sql = "INSERT INTO table . . . . "
conn.execute(sql)
sql = "Select @.@.Identity as Id"
set rsTmp = conn.execute(sql)
RequestID = rsTmp("Id")
rsTmp.close
set rsTmp = nothing
Response.write RequestID & "<br><br>"
Try putting them in the same sql command string; i.e.,
sql = "INSERT INTO tbl ...; SELECT @.@.identity AS ID;"
set rsTmp = conn.execute(sql)
RequestID = rsTmp("Id")
Also, just a performance enhancement, but if you're looping over the INSERT
statements you might consider putting your FOR...NEXT inside of the OPEN and
CLOSE statements. Opening and closing connections can add up to some
serious overhead.
"Ben Bleser" <bbleser@.airway.com> wrote in message
news:00bf01c54449$a62c9c50$a601280a@.phx.gbl...
> I'm running the following within a For Next loop but the
> @.@.identity value doesn't always show up. It only shows
> for every 3rd record. Any ideas on why this might be?
> Thanks,
> Ben Bleser
> sql = "INSERT INTO table . . . . "
> conn.execute(sql)
> sql = "Select @.@.Identity as Id"
> set rsTmp = conn.execute(sql)
> RequestID = rsTmp("Id")
> rsTmp.close
> set rsTmp = nothing
> Response.write RequestID & "<br><br>"
|||Another way could be to passed the values to insert to a stored procedure
and to get a OUTPUT as a return code back which would represent the
identity.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Michael C#" <howsa@.boutdat.com> schrieb im Newsbeitrag
news:OOD0dCFRFHA.204@.TK2MSFTNGP15.phx.gbl...
> Try putting them in the same sql command string; i.e.,
> sql = "INSERT INTO tbl ...; SELECT @.@.identity AS ID;"
> set rsTmp = conn.execute(sql)
> RequestID = rsTmp("Id")
> Also, just a performance enhancement, but if you're looping over the
> INSERT statements you might consider putting your FOR...NEXT inside of the
> OPEN and CLOSE statements. Opening and closing connections can add up to
> some serious overhead.
> "Ben Bleser" <bbleser@.airway.com> wrote in message
> news:00bf01c54449$a62c9c50$a601280a@.phx.gbl...
>

@@identity

I'm running the following within a For Next loop but the
@.@.identity value doesn't always show up. It only shows
for every 3rd record. Any ideas on why this might be?
Thanks,
Ben Bleser
sql = "INSERT INTO table . . . . "
conn.execute(sql)
sql = "Select @.@.Identity as Id"
set rsTmp = conn.execute(sql)
RequestID = rsTmp("Id")
rsTmp.close
set rsTmp = nothing
Response.write RequestID & "<br><br>"Try putting them in the same sql command string; i.e.,
sql = "INSERT INTO tbl ...; SELECT @.@.identity AS ID;"
set rsTmp = conn.execute(sql)
RequestID = rsTmp("Id")
Also, just a performance enhancement, but if you're looping over the INSERT
statements you might consider putting your FOR...NEXT inside of the OPEN and
CLOSE statements. Opening and closing connections can add up to some
serious overhead.
"Ben Bleser" <bbleser@.airway.com> wrote in message
news:00bf01c54449$a62c9c50$a601280a@.phx.gbl...
> I'm running the following within a For Next loop but the
> @.@.identity value doesn't always show up. It only shows
> for every 3rd record. Any ideas on why this might be?
> Thanks,
> Ben Bleser
> sql = "INSERT INTO table . . . . "
> conn.execute(sql)
> sql = "Select @.@.Identity as Id"
> set rsTmp = conn.execute(sql)
> RequestID = rsTmp("Id")
> rsTmp.close
> set rsTmp = nothing
> Response.write RequestID & "<br><br>"|||Another way could be to passed the values to insert to a stored procedure
and to get a OUTPUT as a return code back which would represent the
identity.
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Michael C#" <howsa@.boutdat.com> schrieb im Newsbeitrag
news:OOD0dCFRFHA.204@.TK2MSFTNGP15.phx.gbl...
> Try putting them in the same sql command string; i.e.,
> sql = "INSERT INTO tbl ...; SELECT @.@.identity AS ID;"
> set rsTmp = conn.execute(sql)
> RequestID = rsTmp("Id")
> Also, just a performance enhancement, but if you're looping over the
> INSERT statements you might consider putting your FOR...NEXT inside of the
> OPEN and CLOSE statements. Opening and closing connections can add up to
> some serious overhead.
> "Ben Bleser" <bbleser@.airway.com> wrote in message
> news:00bf01c54449$a62c9c50$a601280a@.phx.gbl...
>> I'm running the following within a For Next loop but the
>> @.@.identity value doesn't always show up. It only shows
>> for every 3rd record. Any ideas on why this might be?
>> Thanks,
>> Ben Bleser
>> sql = "INSERT INTO table . . . . "
>> conn.execute(sql)
>> sql = "Select @.@.Identity as Id"
>> set rsTmp = conn.execute(sql)
>> RequestID = rsTmp("Id")
>> rsTmp.close
>> set rsTmp = nothing
>> Response.write RequestID & "<br><br>"
>|||Hello Ben,
Take a look at SCOPE_IDNTITY() too - in books online.
Cheers
SQLCatZ

@@identity

I'm running the following within a For Next loop but the
@.@.identity value doesn't always show up. It only shows
for every 3rd record. Any ideas on why this might be?
Thanks,
Ben Bleser
sql = "INSERT INTO table . . . . "
conn.execute(sql)
sql = "Select @.@.Identity as Id"
set rsTmp = conn.execute(sql)
RequestID = rsTmp("Id")
rsTmp.close
set rsTmp = nothing
Response.write RequestID & "<br><br>"Try putting them in the same sql command string; i.e.,
sql = "INSERT INTO tbl ...; SELECT @.@.identity AS ID;"
set rsTmp = conn.execute(sql)
RequestID = rsTmp("Id")
Also, just a performance enhancement, but if you're looping over the INSERT
statements you might consider putting your FOR...NEXT inside of the OPEN and
CLOSE statements. Opening and closing connections can add up to some
serious overhead.
"Ben Bleser" <bbleser@.airway.com> wrote in message
news:00bf01c54449$a62c9c50$a601280a@.phx.gbl...
> I'm running the following within a For Next loop but the
> @.@.identity value doesn't always show up. It only shows
> for every 3rd record. Any ideas on why this might be?
> Thanks,
> Ben Bleser
> sql = "INSERT INTO table . . . . "
> conn.execute(sql)
> sql = "Select @.@.Identity as Id"
> set rsTmp = conn.execute(sql)
> RequestID = rsTmp("Id")
> rsTmp.close
> set rsTmp = nothing
> Response.write RequestID & "<br><br>"|||Another way could be to passed the values to insert to a stored procedure
and to get a OUTPUT as a return code back which would represent the
identity.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Michael C#" <howsa@.boutdat.com> schrieb im Newsbeitrag
news:OOD0dCFRFHA.204@.TK2MSFTNGP15.phx.gbl...
> Try putting them in the same sql command string; i.e.,
> sql = "INSERT INTO tbl ...; SELECT @.@.identity AS ID;"
> set rsTmp = conn.execute(sql)
> RequestID = rsTmp("Id")
> Also, just a performance enhancement, but if you're looping over the
> INSERT statements you might consider putting your FOR...NEXT inside of the
> OPEN and CLOSE statements. Opening and closing connections can add up to
> some serious overhead.
> "Ben Bleser" <bbleser@.airway.com> wrote in message
> news:00bf01c54449$a62c9c50$a601280a@.phx.gbl...
>

@@FETCH_STATUS

hi.
i have a question about @.@.FETCH_STATUS when doing a cursor loop.
i found 2 examples and i want to know what the diffrence is between
them,
the first one i found in the help:
DECLARE Employee_Cursor CURSOR FOR
SELECT LastName, FirstName FROM Northwind.dbo.Employees
OPEN Employee_Cursor
FETCH NEXT FROM Employee_Cursor
WHILE @.@.FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM Employee_Cursor
END
CLOSE Employee_Cursor
DEALLOCATE Employee_Cursor
here it uses @.@.FETCH_STATUS = 0, wich would be while it is successfull.
the next exaple was an example in the QA:
DECLARE <cursor_name, sysname, test_cursor> CURSOR
READ_ONLY
FOR <select_statement, , SELECT au_fname FROM pubs.dbo.authors>
DECLARE @.name varchar(40)
OPEN <cursor_name, sysname, test_cursor>
FETCH NEXT FROM <cursor_name, sysname, test_cursor> INTO @.name
WHILE (@.@.fetch_status <> -1)
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN
-- PRINT 'add user defined code here'
-- eg.
DECLARE @.message varchar(100)
SELECT @.message = 'my name is: ' + @.name
PRINT @.message
END
FETCH NEXT FROM <cursor_name, sysname, test_cursor> INTO @.name
END
CLOSE <cursor_name, sysname, test_cursor>
DEALLOCATE <cursor_name, sysname, test_cursor>
here it first says, while @.@.FETCH_STATUS <> -2 and then says
only if @.@.FETCH_STATUS <> -1
in my world this would result in the same thing, since according to the
help the 3 diffrent statuses is 0, -1 and -2.
what is the diffrence between the two? and wich one is the "right" one
to use?
/Anders0
The FETCH statement was successful.
-1
The FETCH statement failed or the row was beyond the result set.
-2
The row fetched is missing.
-oj
<aebohlin@.gmail.com> wrote in message
news:1141228790.445782.79910@.v46g2000cwv.googlegroups.com...
> hi.
> i have a question about @.@.FETCH_STATUS when doing a cursor loop.
> i found 2 examples and i want to know what the diffrence is between
> them,
> the first one i found in the help:
> DECLARE Employee_Cursor CURSOR FOR
> SELECT LastName, FirstName FROM Northwind.dbo.Employees
> OPEN Employee_Cursor
> FETCH NEXT FROM Employee_Cursor
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> FETCH NEXT FROM Employee_Cursor
> END
> CLOSE Employee_Cursor
> DEALLOCATE Employee_Cursor
> here it uses @.@.FETCH_STATUS = 0, wich would be while it is successfull.
> the next exaple was an example in the QA:
> DECLARE <cursor_name, sysname, test_cursor> CURSOR
> READ_ONLY
> FOR <select_statement, , SELECT au_fname FROM pubs.dbo.authors>
> DECLARE @.name varchar(40)
> OPEN <cursor_name, sysname, test_cursor>
> FETCH NEXT FROM <cursor_name, sysname, test_cursor> INTO @.name
> WHILE (@.@.fetch_status <> -1)
> BEGIN
> IF (@.@.fetch_status <> -2)
> BEGIN
> -- PRINT 'add user defined code here'
> -- eg.
> DECLARE @.message varchar(100)
> SELECT @.message = 'my name is: ' + @.name
> PRINT @.message
> END
> FETCH NEXT FROM <cursor_name, sysname, test_cursor> INTO @.name
> END
> CLOSE <cursor_name, sysname, test_cursor>
> DEALLOCATE <cursor_name, sysname, test_cursor>
> here it first says, while @.@.FETCH_STATUS <> -2 and then says
> only if @.@.FETCH_STATUS <> -1
> in my world this would result in the same thing, since according to the
> help the 3 diffrent statuses is 0, -1 and -2.
> what is the diffrence between the two? and wich one is the "right" one
> to use?
> /Anders
>|||You don't need <>-2 checking for READONLY CURSOR. If the cursor is Ket-Set
driven cursor, you may read non-existing rows. Then you need to cehck for <>
-2
Refer "Cursor Types" in BOL
--
Thanks & Rate the Postings.
-Ravi-
"aebohlin@.gmail.com" wrote:

> hi.
> i have a question about @.@.FETCH_STATUS when doing a cursor loop.
> i found 2 examples and i want to know what the diffrence is between
> them,
> the first one i found in the help:
> DECLARE Employee_Cursor CURSOR FOR
> SELECT LastName, FirstName FROM Northwind.dbo.Employees
> OPEN Employee_Cursor
> FETCH NEXT FROM Employee_Cursor
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> FETCH NEXT FROM Employee_Cursor
> END
> CLOSE Employee_Cursor
> DEALLOCATE Employee_Cursor
> here it uses @.@.FETCH_STATUS = 0, wich would be while it is successfull.
> the next exaple was an example in the QA:
> DECLARE <cursor_name, sysname, test_cursor> CURSOR
> READ_ONLY
> FOR <select_statement, , SELECT au_fname FROM pubs.dbo.authors>
> DECLARE @.name varchar(40)
> OPEN <cursor_name, sysname, test_cursor>
> FETCH NEXT FROM <cursor_name, sysname, test_cursor> INTO @.name
> WHILE (@.@.fetch_status <> -1)
> BEGIN
> IF (@.@.fetch_status <> -2)
> BEGIN
> -- PRINT 'add user defined code here'
> -- eg.
> DECLARE @.message varchar(100)
> SELECT @.message = 'my name is: ' + @.name
> PRINT @.message
> END
> FETCH NEXT FROM <cursor_name, sysname, test_cursor> INTO @.name
> END
> CLOSE <cursor_name, sysname, test_cursor>
> DEALLOCATE <cursor_name, sysname, test_cursor>
> here it first says, while @.@.FETCH_STATUS <> -2 and then says
> only if @.@.FETCH_STATUS <> -1
> in my world this would result in the same thing, since according to the
> help the 3 diffrent statuses is 0, -1 and -2.
> what is the diffrence between the two? and wich one is the "right" one
> to use?
> /Anders
>|||thanks alot.sql

Queries in While Condition

When using a query in the condition of a while loop, is that query being performed each time?
EX:
While (@.X <= (Select count(*) from SOME_TABLE))I was going to say no...but...

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(Col1 int IDENTITY(1,1), Col2 char(1))
GO

INSERT INTO myTable99(Col2)
SELECT 'A' UNION ALL
SELECT 'B' UNION ALL
SELECT 'C' UNION ALL
SELECT 'D' UNION ALL
SELECT 'E'
GO

DECLARE @.x int
SELECT @.x = 1
WHILE @.x < (SELECT COUNT(*) FROM myTable99)
BEGIN
SELECT @.x = @.x + 1
END
SELECT @.x

SELECT @.x = 1
WHILE @.x < (SELECT COUNT(*) FROM myTable99)
BEGIN
IF @.x IN (1,3,5) INSERT INTO myTable99(Col2) SELECT 'x'
SELECT @.x = @.x + 1
END
SELECT @.x
GO

SET NOCOUNT OFF
DROP TABLE myTable99
GO