If numeric query

I have a table with a memo field that sometimes contains an account
number at the same location. I need to find a record related to that
account in another table. I can write and run a query for a record
which works fine if the account number exists, but (obviously) get an
error if it does not. I wrote the following query which I don't
understand why it will not work.
IF EXISTS (SELECT fldMemo FROM tblRegister WHERE ISNUMERIC(SUBSTRING
(tblRegister.fldMemo,6,9)) = 1)
BEGIN
SELECT fldChkNo, fldDate, fldName, fldMemo, fldAmt, fldTransmitted,
fldStatus, fldLogDate, fldNote
FROM tblRegister INNER JOIN tblNotes ON SUBSTRING
(tblRegister.fldMemo,6,9) = tblNotes.fldMemberNo
END
ELSE
BEGIN
SELECT fldChkNo, fldDate, fldName, fldMemo, fldAmt, fldTransmitted,
fldStatus, fldLogDate
FROM tblRegister
END
Any help on this would be greatly appreciated.
Thanks
CharlesA couple of comments.
First, isnumeric isn't foolproof. You may wish to review this source:
isnumeric -What is wrong?
http://www.aspfaq.com/show.asp?id=2390
Second, it appears that you are attempting to compare a string to a number,
e.g.,
SUBSTRING(tblRegister.fldMemo,6,9) = tblNotes.fldMemberNo
(I'm assuming that tblNotes.fldMemberNo is a numeric datatype.)
You need to cast the first part as a numeric value in order make the compari
son. And this will not use indexing, so expect performance to be poor.
If you were to add another column to the Register table, set its value to be
the substring of the Memo field (or better yet, have the application provid
e the number), and then index that column, performance would be rather sprig
htly.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
<cbanks@.bjtsupport.com> wrote in message news:1162249400.557355.12560@.k70g2000cwa.googlegrou
ps.com...
>I have a table with a memo field that sometimes contains an account
> number at the same location. I need to find a record related to that
> account in another table. I can write and run a query for a record
> which works fine if the account number exists, but (obviously) get an
> error if it does not. I wrote the following query which I don't
> understand why it will not work.
>
> IF EXISTS (SELECT fldMemo FROM tblRegister WHERE ISNUMERIC(SUBSTRING
> (tblRegister.fldMemo,6,9)) = 1)
>
> BEGIN
> SELECT fldChkNo, fldDate, fldName, fldMemo, fldAmt, fldTransmitted,
> fldStatus, fldLogDate, fldNote
> FROM tblRegister INNER JOIN tblNotes ON SUBSTRING
> (tblRegister.fldMemo,6,9) = tblNotes.fldMemberNo
> END
>
> ELSE
>
> BEGIN
> SELECT fldChkNo, fldDate, fldName, fldMemo, fldAmt, fldTransmitted,
> fldStatus, fldLogDate
> FROM tblRegister
> END
>
> Any help on this would be greatly appreciated.
> Thanks
> Charles
>|||Arnie,
Thanks for the reply. My main issue is when I try to run this as an IF
statement I get an error "Incorrect syntax near the keyword 'BEGIN'."
I actually tried it without the 'Exists' after IF. If I run
SELECT fldChkNo, fldDate, fldName, fldMemo, fldAmt, fldTransmitted,
fldStatus, fldLogDate, fldNote
FROM tblRegister INNER JOIN tblNotes ON SUBSTRING
(tblRegister.fldMemo,6,9) = tblNotes.fldMemberNo with a where clause to
locate a record with a valid member number, it works fine. My issue is
running this when I don't have a valid member ID, which is what I am
trying to accomplish with the if statement.
It would have been better to include the member number in my register
table, but this was not in the original scope and I really don't want
to modify my table structure.
Charles
Arnie Rowland wrote:
> A couple of comments.
> First, isnumeric isn't foolproof. You may wish to review this source:
> isnumeric -What is wrong?
> http://www.aspfaq.com/show.asp?id=2390
>
> Second, it appears that you are attempting to compare a string to a number
, e.g.,
> SUBSTRING(tblRegister.fldMemo,6,9) = tblNotes.fldMemberNo
> (I'm assuming that tblNotes.fldMemberNo is a numeric datatype.)
>
> You need to cast the first part as a numeric value in order make the compa
rison. And this will not use indexing, so expect performance to be poor.
> If you were to add another column to the Register table, set its value to
be the substring of the Memo field (or better yet, have the application prov
ide the number), and then index that column, performance would be rather spr
ightly.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to th
e top yourself.
> - H. Norman Schwarzkopf
>
> <cbanks@.bjtsupport.com> wrote in message news:1162249400.557355.12560@.k70g
2000cwa.googlegroups.com...
> --=_NextPart_000_048F_01C6FC3D.3D5AC000
> Content-Type: text/html; charset=iso-8859-1
> Content-Transfer-Encoding: quoted-printable
> X-Google-AttachSize: 5062
> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
> <HTML><HEAD>
> <META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
> <META content="MSHTML 6.00.5296.0" name=GENERATOR>
> <STYLE></STYLE>
> </HEAD>
> <BODY>
> <DIV><FONT face=Arial size=2>A couple of comments.</FONT></DIV>
> <DIV><FONT face=Arial size=2></FONT> </DIV>
> <DIV><FONT face=Arial size=2>First, isnumeric isn't foolproof. You may wis
h to
> review this source:</FONT></DIV>
> <DIV><FONT face=Arial size=2></FONT> </DIV>
> <DIV>
> <P class=MsoNormal
> style="MARGIN: 0in 0in 6pt; mso-layout-grid-align: none"><SPAN
> style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial; mso-no-proof: yes
">isnumeric
> -What is wrong?<BR></SPAN><SPAN
> style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: Arial; mso-no-proof: ye
s"><A
> href="http://links.10026.com/?link=http://www.aspfaq.com/show.asp?id=2390">http://www.aspfaq.com/show.asp?id=
2390</A>
> </SPAN></P>
> <P class=MsoNormal
> style="MARGIN: 0in 0in 6pt; mso-layout-grid-align: none"><SPAN
> style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: Arial; mso-no-proof: ye
s"><FONT
> face=Arial size=2></FONT></SPAN> </P>
> <P class=MsoNormal
> style="MARGIN: 0in 0in 6pt; mso-layout-grid-align: none"><SPAN
> style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: Arial; mso-no-proof: ye
s">Second,
> it appears that you are attempting to compare a string to a number, e.g.,
> </SPAN></P>
> <P class=MsoNormal
> style="MARGIN: 0in 0in 6pt; mso-layout-grid-align: none"><SPAN
> style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: Arial; mso-no-proof: ye
s"><FONT
> face="Courier New">SUBSTRING(tblRegister.fldMemo,6,9) =
> tblNotes.fldMemberNo</FONT></SPAN></P>
> <P class=MsoNormal
> style="MARGIN: 0in 0in 6pt; mso-layout-grid-align: none"><SPAN
> style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: Arial; mso-no-proof: ye
s">(I'm assuming
> that tblNotes.fldMemberNo is a numeric datatype.)<BR></P></SPAN>
> <P class=MsoNormal
> style="MARGIN: 0in 0in 6pt; mso-layout-grid-align: none"><SPAN
> style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: Arial; mso-no-proof: ye
s">You
> need to cast the first part as a numeric value in order make the compariso
n.
> </SPAN><SPAN
> style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: Arial; mso-no-proof: ye
s">And
> this will not use indexing, so expect performance to be poor.</P></SPAN></
DIV>
> <DIV><FONT face=Arial size=2>If you were to add another column to the Regi
ster
> table, set its value to be the substring of the Memo field (or better yet,
have
> the application provide the number), and then index that column, performan
ce
> would be rather sprightly.</FONT><BR><FONT face=Arial size=2>-- <BR>Arnie
> Rowland, Ph.D.<BR>Westwood Consulting, Inc</FONT></DIV>
> <DIV><FONT face=Arial size=2></FONT> </DIV>
> <DIV><FONT face=Arial size=2>Most good judgment comes from experience. <BR
>Most
> experience comes from bad judgment. <BR>- Anonymous</FONT></DIV>
> <DIV><FONT face=Arial size=2></FONT> </DIV>
> <DIV><FONT face=Arial size=2>You can't help someone get up a hill without
> getting a little closer to the top yourself.<BR>- H. Norman
> Schwarzkopf</FONT></DIV>
> <DIV><FONT face=Arial size=2></FONT> </DIV>
> <DIV><FONT face=Arial size=2></FONT> </DIV>
> <DIV><FONT face=Arial size=2><</FONT><A
> href="http://links.10026.com/?link=mailto:cbanks@.bjtsupport.com"><FONT face=Arial
> size=2>cbanks@.bjtsupport.com</FONT></A><FONT face=Arial size=2>> wrote
in
> message </FONT><A
> href="http://links.10026.com/?link=news:1162249400.557355.12560@.k70g2000cwa.googlegroups.com"><FONT
> face=Arial
> size=2>news:1162249400.557355.12560@.k70g2000cwa.googlegroups.com</FONT></A
><FONT
> face=Arial size=2>...</FONT></DIV><FONT face=Arial size=2>>I have a tab
le
> with a memo field that sometimes contains an account<BR>> number at the
same
> location. I need to find a record related to that<BR>> accoun
t in
> another table. I can write and run a query for a record<BR>>
which
> works fine if the account number exists, but (obviously) get an<BR>> er
ror if
> it does not. I wrote the following query which I don't<BR>> u
nderstand
> why it will not work.<BR>> <BR>> IF EXISTS (SELECT fldMemo FROM
> tblRegister WHERE ISNUMERIC(SUBSTRING<BR>> (tblRegister.fldMemo,6,9)) =
> 1)<BR>> <BR>> BEGIN<BR>> SELECT fldChkNo, fldDate, fldName, fldMe
mo,
> fldAmt, fldTransmitted,<BR>> fldStatus, fldLogDate, fldNote<BR>> FRO
M
> tblRegister INNER JOIN tblNotes ON SUBSTRING<BR>> (tblRegister.fldMemo,
6,9) =
> tblNotes.fldMemberNo<BR>> END<BR>> <BR>> ELSE<BR>> <BR>>
> BEGIN<BR>> SELECT fldChkNo, fldDate, fldName, fldMemo, fldAmt,
> fldTransmitted,<BR>> fldStatus, fldLogDate<BR>> FROM tblRegister<BR>
> END<BR>> <BR>> Any help on this would be greatly appreciated.<BR>>
;
> Thanks<BR>> Charles<BR>></FONT></BODY></HTML>
> --=_NextPart_000_048F_01C6FC3D.3D5AC000--|||Charles,
exists is really for subqueries. However you could use:
IF (SELECT count(*) FROM tblRegister WHERE ISNUMERIC(SUBSTRING
(tblRegister.fldMemo,6,9)) = 1) > 0
....
or
SELECT fldMemo FROM tblRegister WHERE ISNUMERIC(SUBSTRING
(tblRegister.fldMemo,6,9)) = 1
if @.@.rowcount > 0
....
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||> exists is really for subqueries. However you could use:
> IF (SELECT count(*) FROM tblRegister WHERE ISNUMERIC(SUBSTRING
> (tblRegister.fldMemo,6,9)) = 1) > 0
I'm not sure why this is better than
IF EXISTS (SELECT 1 FROM tblRegister ...)
BEGIN
END
? The exists is potentially faster, and certainly not any slower, than a
select count.
The syntax problem he had was likely just a missing parenthesis...
A|||Thanks Aaron - my goof .
For some obscure reason I had assumed that it was returning a single row
(long night and needed more coffee!), but as I look more closely at what I
wrote I see issues with both solutions - Charles please disregard my earlier
post.
Regards,
Paul Ibison|||Actually, not a missing parens, but including an equality [ = 1 ) > 0 ]
which made the statement invalid. The statement 'should' work as:
IF EXISTS
( SELECT fldMemo
FROM tblRegister
WHERE isnumeric( substring( tblRegister.fldMemo, 6, 9 ))
)
BEGIN
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message news:uFkIfWP
$GHA.4708@.TK2MSFTNGP05.phx.gbl...
>
> I'm not sure why this is better than
>
> IF EXISTS (SELECT 1 FROM tblRegister ...)
> BEGIN
> END
>
> ? The exists is potentially faster, and certainly not any slower, than a
> select count.
>
> The syntax problem he had was likely just a missing parenthesis...
>
> A
>
>|||> Actually, not a missing parens, but including an equality [ = 1 ) > 0 ]">
> which made the statement invalid.
Where? I didn't see his code, I just saw fragments of it.
A

If numeric query

I have a table with a memo field that sometimes contains an account
number at the same location. I need to find a record related to that
account in another table. I can write and run a query for a record
which works fine if the account number exists, but (obviously) get an
error if it does not. I wrote the following query which I don't
understand why it will not work.
IF EXISTS (SELECT fldMemo FROM tblRegister WHERE ISNUMERIC(SUBSTRING
(tblRegister.fldMemo,6,9)) = 1)
BEGIN
SELECT fldChkNo, fldDate, fldName, fldMemo, fldAmt, fldTransmitted,
fldStatus, fldLogDate, fldNote
FROM tblRegister INNER JOIN tblNotes ON SUBSTRING
(tblRegister.fldMemo,6,9) = tblNotes.fldMemberNo
END
ELSE
BEGIN
SELECT fldChkNo, fldDate, fldName, fldMemo, fldAmt, fldTransmitted,
fldStatus, fldLogDate
FROM tblRegister
END
Any help on this would be greatly appreciated.
Thanks
CharlesThis is a multi-part message in MIME format.
--=_NextPart_000_048F_01C6FC3D.3D5AC000
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
A couple of comments.
First, isnumeric isn't foolproof. You may wish to review this source:
isnumeric -What is wrong?
http://www.aspfaq.com/show.asp?id=3D2390=20
Second, it appears that you are attempting to compare a string to a =number, e.g.,
SUBSTRING(tblRegister.fldMemo,6,9) =3D tblNotes.fldMemberNo
(I'm assuming that tblNotes.fldMemberNo is a numeric datatype.)
You need to cast the first part as a numeric value in order make the =comparison. And this will not use indexing, so expect performance to be =poor.
If you were to add another column to the Register table, set its value =to be the substring of the Memo field (or better yet, have the =application provide the number), and then index that column, performance =would be rather sprightly.
-- Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous
You can't help someone get up a hill without getting a little closer to =the top yourself.
- H. Norman Schwarzkopf
<cbanks@.bjtsupport.com> wrote in message =news:1162249400.557355.12560@.k70g2000cwa.googlegroups.com...
>I have a table with a memo field that sometimes contains an account
> number at the same location. I need to find a record related to that
> account in another table. I can write and run a query for a record
> which works fine if the account number exists, but (obviously) get an
> error if it does not. I wrote the following query which I don't
> understand why it will not work.
> > IF EXISTS (SELECT fldMemo FROM tblRegister WHERE ISNUMERIC(SUBSTRING
> (tblRegister.fldMemo,6,9)) =3D 1)
> > BEGIN
> SELECT fldChkNo, fldDate, fldName, fldMemo, fldAmt, fldTransmitted,
> fldStatus, fldLogDate, fldNote
> FROM tblRegister INNER JOIN tblNotes ON SUBSTRING
> (tblRegister.fldMemo,6,9) =3D tblNotes.fldMemberNo
> END
> > ELSE
> > BEGIN
> SELECT fldChkNo, fldDate, fldName, fldMemo, fldAmt, fldTransmitted,
> fldStatus, fldLogDate
> FROM tblRegister
> END
> > Any help on this would be greatly appreciated.
> Thanks
> Charles
>
--=_NextPart_000_048F_01C6FC3D.3D5AC000
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

A couple of comments.
First, isnumeric isn't foolproof. You =may wish to review this source:
isnumeric -What is wrong?http://www.aspfaq.com/show.asp?id=3D2390">http://www.aspfaq.com/s=how.asp?id=3D2390
Second, it appears that you are attempting to compare a string to a number, =e.g., SUBSTRING(tblRegister.fldMemo,6,9) =3D tblNotes.fldMemberNo
(I'm assuming that tblNotes.fldMemberNo is a numeric datatype.)
You need to cast the first part as a numeric value in order make the =comparison. And this will not use indexing, so expect performance to be =poor.
If you were to add another column to =the Register table, set its value to be the substring of the Memo field (or better =yet, have the application provide the number), and then index that column, =performance would be rather sprightly.-- =Arnie Rowland, Ph.D.Westwood Consulting, Inc
Most good judgment comes from =experience. Most experience comes from bad judgment. - Anonymous
You can't help someone get up a hill =without getting a little closer to the top yourself.- H. Norman Schwarzkopf
wrote in message news:1162249400.557355.12560@.k70g2000cwa.googlegroups.com=...>I =have a table with a memo field that sometimes contains an account> number at =the same location. I need to find a record related to that> account =in another table. I can write and run a query for a record> =which works fine if the account number exists, but (obviously) get an> =error if it does not. I wrote the following query which I don't> =understand why it will not work.> > IF EXISTS (SELECT fldMemo FROM tblRegister WHERE ISNUMERIC(SUBSTRING> (tblRegister.fldMemo,6,9)) ==3D 1)> > BEGIN> SELECT fldChkNo, fldDate, fldName, =fldMemo, fldAmt, fldTransmitted,> fldStatus, fldLogDate, fldNote> =FROM tblRegister INNER JOIN tblNotes ON SUBSTRING> =(tblRegister.fldMemo,6,9) =3D tblNotes.fldMemberNo> END> > ELSE> > BEGIN> SELECT fldChkNo, fldDate, fldName, fldMemo, fldAmt, fldTransmitted,> fldStatus, fldLogDate> FROM =tblRegister> END> > Any help on this would be greatly =appreciated.> Thanks> Charles>

---

--=_NextPart_000_048F_01C6FC3D.3D5AC000--|||Arnie,
Thanks for the reply. My main issue is when I try to run this as an IF
statement I get an error "Incorrect syntax near the keyword 'BEGIN'."
I actually tried it without the 'Exists' after IF. If I run
SELECT fldChkNo, fldDate, fldName, fldMemo, fldAmt, fldTransmitted,
fldStatus, fldLogDate, fldNote
FROM tblRegister INNER JOIN tblNotes ON SUBSTRING
(tblRegister.fldMemo,6,9) = tblNotes.fldMemberNo with a where clause to
locate a record with a valid member number, it works fine. My issue is
running this when I don't have a valid member ID, which is what I am
trying to accomplish with the if statement.
It would have been better to include the member number in my register
table, but this was not in the original scope and I really don't want
to modify my table structure.
Charles
Arnie Rowland wrote:
> A couple of comments.
> First, isnumeric isn't foolproof. You may wish to review this source:
> isnumeric -What is wrong?
> http://www.aspfaq.com/show.asp?id=2390
>
> Second, it appears that you are attempting to compare a string to a number, e.g.,
> SUBSTRING(tblRegister.fldMemo,6,9) = tblNotes.fldMemberNo
> (I'm assuming that tblNotes.fldMemberNo is a numeric datatype.)
>
> You need to cast the first part as a numeric value in order make the comparison. And this will not use indexing, so expect performance to be poor.
> If you were to add another column to the Register table, set its value to be the substring of the Memo field (or better yet, have the application provide the number), and then index that column, performance would be rather sprightly.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to the top yourself.
> - H. Norman Schwarzkopf
>
> <cbanks@.bjtsupport.com> wrote in message news:1162249400.557355.12560@.k70g2000cwa.googlegroups.com...
> >I have a table with a memo field that sometimes contains an account
> > number at the same location. I need to find a record related to that
> > account in another table. I can write and run a query for a record
> > which works fine if the account number exists, but (obviously) get an
> > error if it does not. I wrote the following query which I don't
> > understand why it will not work.
> >
> > IF EXISTS (SELECT fldMemo FROM tblRegister WHERE ISNUMERIC(SUBSTRING
> > (tblRegister.fldMemo,6,9)) = 1)
> >
> > BEGIN
> > SELECT fldChkNo, fldDate, fldName, fldMemo, fldAmt, fldTransmitted,
> > fldStatus, fldLogDate, fldNote
> > FROM tblRegister INNER JOIN tblNotes ON SUBSTRING
> > (tblRegister.fldMemo,6,9) = tblNotes.fldMemberNo
> > END
> >
> > ELSE
> >
> > BEGIN
> > SELECT fldChkNo, fldDate, fldName, fldMemo, fldAmt, fldTransmitted,
> > fldStatus, fldLogDate
> > FROM tblRegister
> > END
> >
> > Any help on this would be greatly appreciated.
> > Thanks
> > Charles
> >
> --=_NextPart_000_048F_01C6FC3D.3D5AC000
> Content-Type: text/html; charset=iso-8859-1
> Content-Transfer-Encoding: quoted-printable
> X-Google-AttachSize: 5062
> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
> &

---

>
>
>
>
>
> A couple of comments.
>
> First, isnumeric isn't foolproof. You may wish to
> review this source:
>
>
> style="MARGIN: 0in 0in 6pt; mso-layout-grid-align: none"> style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial; mso-no-proof: yes">isnumeric
> -What is wrong? style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: Arial; mso-no-proof: yes"> href="http://links.10026.com/?link=http://www.aspfaq.com/show.asp?id=2390">http://www.aspfaq.com/show.asp?id=2390">http://www.aspfaq.com/show.asp?id=2390
>
> style="MARGIN: 0in 0in 6pt; mso-layout-grid-align: none"> style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: Arial; mso-no-proof: yes"> face=Arial size=2>
> style="MARGIN: 0in 0in 6pt; mso-layout-grid-align: none"> style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: Arial; mso-no-proof: yes">Second,
> it appears that you are attempting to compare a string to a number, e.g.,
>
> style="MARGIN: 0in 0in 6pt; mso-layout-grid-align: none"> style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: Arial; mso-no-proof: yes"> face="Courier New">SUBSTRING(tblRegister.fldMemo,6,9) => tblNotes.fldMemberNo
> style="MARGIN: 0in 0in 6pt; mso-layout-grid-align: none"> style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: Arial; mso-no-proof: yes">(I'm assuming
> that tblNotes.fldMemberNo is a numeric datatype.)
> style="MARGIN: 0in 0in 6pt; mso-layout-grid-align: none"> style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: Arial; mso-no-proof: yes">You
> need to cast the first part as a numeric value in order make the comparison.
> style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: Arial; mso-no-proof: yes">And
> this will not use indexing, so expect performance to be poor.
> If you were to add another column to the Register
> table, set its value to be the substring of the Memo field (or better yet, have
> the application provide the number), and then index that column, performance
> would be rather sprightly.-- Arnie
> Rowland, Ph.D.Westwood Consulting, Inc
>
> Most good judgment comes from experience. Most
> experience comes from bad judgment. - Anonymous
>
> You can't help someone get up a hill without
> getting a little closer to the top yourself.- H. Norman
> Schwarzkopf
>
>
>< href="http://links.10026.com/?link=mailto:cbanks@.bjtsupport.com"> size=2>cbanks@.bjtsupport.com> wrote in
> message href="http://links.10026.com/?link=news:1162249400.557355.12560@.k70g2000cwa.googlegroups.com"> face=Arial
> size=2>news:1162249400.557355.12560@.k70g2000cwa.googlegroups.com face=Arial size=2>...>I have a table
> with a memo field that sometimes contains an account> number at the same
> location. I need to find a record related to that> account in
> another table. I can write and run a query for a record> which
> works fine if the account number exists, but (obviously) get an> error if
> it does not. I wrote the following query which I don't> understand
> why it will not work.> > IF EXISTS (SELECT fldMemo FROM
> tblRegister WHERE ISNUMERIC(SUBSTRING> (tblRegister.fldMemo,6,9)) => 1)> > BEGIN> SELECT fldChkNo, fldDate, fldName, fldMemo,
> fldAmt, fldTransmitted,> fldStatus, fldLogDate, fldNote> FROM
> tblRegister INNER JOIN tblNotes ON SUBSTRING> (tblRegister.fldMemo,6,9) => tblNotes.fldMemberNo> END> > ELSE> >
> BEGIN> SELECT fldChkNo, fldDate, fldName, fldMemo, fldAmt,
> fldTransmitted,> fldStatus, fldLogDate> FROM tblRegister>
> END> > Any help on this would be greatly appreciated.>
> Thanks> Charles>

---

> --=_NextPart_000_048F_01C6FC3D.3D5AC000--|||Charles,
exists is really for subqueries. However you could use:
IF (SELECT count(*) FROM tblRegister WHERE ISNUMERIC(SUBSTRING
(tblRegister.fldMemo,6,9)) = 1) > 0
....
or
SELECT fldMemo FROM tblRegister WHERE ISNUMERIC(SUBSTRING
(tblRegister.fldMemo,6,9)) = 1
if @.@.rowcount > 0
....
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||> exists is really for subqueries. However you could use:
> IF (SELECT count(*) FROM tblRegister WHERE ISNUMERIC(SUBSTRING
> (tblRegister.fldMemo,6,9)) = 1) > 0
I'm not sure why this is better than
IF EXISTS (SELECT 1 FROM tblRegister ...)
BEGIN
END
? The exists is potentially faster, and certainly not any slower, than a
select count.
The syntax problem he had was likely just a missing parenthesis...
A|||Thanks Aaron - my goof :(.
For some obscure reason I had assumed that it was returning a single row
(long night and needed more coffee!), but as I look more closely at what I
wrote I see issues with both solutions - Charles please disregard my earlier
post.
Regards,
Paul Ibison|||This is a multi-part message in MIME format.
--=_NextPart_000_057B_01C6FCC2.47296CF0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Actually, not a missing parens, but including an equality [ =3D 1 ) > 0 =] which made the statement invalid. The statement 'should' work as:
IF EXISTS ( SELECT fldMemo FROM tblRegister WHERE isnumeric( substring( tblRegister.fldMemo, 6, 9 ))
)
BEGIN
-- Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous
You can't help someone get up a hill without getting a little closer to =the top yourself.
- H. Norman Schwarzkopf
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in =message news:uFkIfWP$GHA.4708@.TK2MSFTNGP05.phx.gbl...
>> exists is really for subqueries. However you could use:
>> IF (SELECT count(*) FROM tblRegister WHERE ISNUMERIC(SUBSTRING >> (tblRegister.fldMemo,6,9)) =3D 1) > 0
> > I'm not sure why this is better than
> > IF EXISTS (SELECT 1 FROM tblRegister ...)
> BEGIN
> END
> > ? The exists is potentially faster, and certainly not any slower, =than a > select count.
> > The syntax problem he had was likely just a missing parenthesis...
> > A > >
--=_NextPart_000_057B_01C6FCC2.47296CF0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Actually, not a missing parens, but =including an equality [ =3D 1 ) > 0 ] which made the statement invalid. The =statement 'should' work as:
IF EXISTS
( SELECT =fldMemo =FROM tblRegister
=WHERE isnumeric( substring( tblRegister.fldMemo, 6, 9 ))
=)
=BEGIN-- Arnie =Rowland, Ph.D.Westwood Consulting, Inc
Most good judgment comes from =experience. Most experience comes from bad judgment. - Anonymous
You can't help someone get up a hill =without getting a little closer to the top yourself.- H. Norman Schwarzkopf
"Aaron Bertrand [SQL Server MVP]" = wrote in message news:uFkIfWP$GHA.4708@.TK2MSFTNGP05.phx.gbl...> exists is really for subqueries. However you could use:> IF (SELECT =count(*) FROM tblRegister WHERE ISNUMERIC(SUBSTRING > (tblRegister.fldMemo,6,9)) =3D 1) > 0> > I'm not sure =why this is better than> > IF EXISTS (SELECT 1 FROM tblRegister =...)> BEGIN> END> > ? The exists is potentially =faster, and certainly not any slower, than a > select count.> > =The syntax problem he had was likely just a missing parenthesis...> => A > >

---

--=_NextPart_000_057B_01C6FCC2.47296CF0--|||> Actually, not a missing parens, but including an equality [ = 1 ) > 0 ]
> which made the statement invalid.
Where? I didn't see his code, I just saw fragments of it.
A

IDs of updated records

I have to update a table, and after I update I need to insert a record
for each updated record in some other table.
I need to know the IDs of the records which were updated in the first
table, so that when I insert records in the second table then I can put
that ID in a field.
How would I acheive this?
Thanks in advance.With a trigger I suppose.
CREATE TRIGGER dbo.UpdateBaseTableName
ON dbo.BaseTableName
FOR UPDATE
AS
IF @.@.ROWCOUNT > 0
INSERT AuditTable(id_column) SELECT id_column FROM inserted;
GO
See the topic "CREATE TRIGGER" in Books Online for more details.
"Sehboo" <MasoodAdnan@.gmail.com> wrote in message
news:1138209391.561493.167780@.g43g2000cwa.googlegroups.com...
>I have to update a table, and after I update I need to insert a record
> for each updated record in some other table.
> I need to know the IDs of the records which were updated in the first
> table, so that when I insert records in the second table then I can put
> that ID in a field.
> How would I acheive this?
> Thanks in advance.
>|||On 2005, your the OUPUT option of the UPDATE command. If earlier version, do
a SELECT first based on
the WHERE condition to know the ID.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Sehboo" <MasoodAdnan@.gmail.com> wrote in message
news:1138209391.561493.167780@.g43g2000cwa.googlegroups.com...
>I have to update a table, and after I update I need to insert a record
> for each updated record in some other table.
> I need to know the IDs of the records which were updated in the first
> table, so that when I insert records in the second table then I can put
> that ID in a field.
> How would I acheive this?
> Thanks in advance.
>sql

Identy Number

I am using an Identy number to generate a Unique ID for records.
The process is
a. Save header record (identy ID created)
b. Retrieve Identy No using a select statement via odbc
c. Dave many data recors with Identy Number as reference to the Header
Record.
How can i retrieve the Identy No with a select statement.
Regards
Jeff
--
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.583 / Virus Database: 369 - Release Date: 10/02/2004Use the SCOPE_IDENTITY() function.
--
David Portas
SQL Server MVP
--|||To expand David's response... Use a select that returns the identity ie
select @.@.scope_identity
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jeff Williams" <jeff.williams@.hardsoft.com.au> wrote in message
news:OHc5aqz8DHA.2308@.TK2MSFTNGP11.phx.gbl...
> I am using an Identy number to generate a Unique ID for records.
> The process is
> a. Save header record (identy ID created)
> b. Retrieve Identy No using a select statement via odbc
> c. Dave many data recors with Identy Number as reference to the Header
> Record.
> How can i retrieve the Identy No with a select statement.
> Regards
> Jeff
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.583 / Virus Database: 369 - Release Date: 10/02/2004
>

Identy Number

I am using an Identy number to generate a Unique ID for records.
The process is
a. Save header record (identy ID created)
b. Retrieve Identy No using a select statement via odbc
c. Dave many data recors with Identy Number as reference to the Header
Record.
How can i retrieve the Identy No with a select statement.
Regards
Jeff
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.583 / Virus Database: 369 - Release Date: 10/02/2004Use the SCOPE_IDENTITY() function.
David Portas
SQL Server MVP
--|||To expand David's response... Use a select that returns the identity ie
select @.@.scope_identity
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jeff Williams" <jeff.williams@.hardsoft.com.au> wrote in message
news:OHc5aqz8DHA.2308@.TK2MSFTNGP11.phx.gbl...
> I am using an Identy number to generate a Unique ID for records.
> The process is
> a. Save header record (identy ID created)
> b. Retrieve Identy No using a select statement via odbc
> c. Dave many data recors with Identy Number as reference to the Header
> Record.
> How can i retrieve the Identy No with a select statement.
> Regards
> Jeff
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.583 / Virus Database: 369 - Release Date: 10/02/2004
>sql

Identity_Insert is set to OFF

Hi,
I'm using SQL Server MSDE RelA (BackEnd) Access Project (FrontEnd). I need
to Duplicate a record from a form and the linked records in its subform.
Parent form duplicate goes to table "Jobs" where JobID is the key, records
from subform with (Link JobID) go to table "Samples". I've created an update
query in order to do this and I'm getting the following error-message:
Cannot insert explicit value for identity column in table "Jobs" when
IDENTITY_INSERT is set to OFF
Do I need SP4 Service Pack SP4 or am I doing something wrong? Is there any
other way to duplicate these records?
Thanks in advance
gaba
Sorry,
I've meant "append query" not update
gaba
"gaba" wrote:

> Hi,
> I'm using SQL Server MSDE RelA (BackEnd) Access Project (FrontEnd). I need
> to Duplicate a record from a form and the linked records in its subform.
> Parent form duplicate goes to table "Jobs" where JobID is the key, records
> from subform with (Link JobID) go to table "Samples". I've created an update
> query in order to do this and I'm getting the following error-message:
> Cannot insert explicit value for identity column in table "Jobs" when
> IDENTITY_INSERT is set to OFF
> Do I need SP4 Service Pack SP4 or am I doing something wrong? Is there any
> other way to duplicate these records?
> Thanks in advance
>
> --
> gaba
|||gaba wrote:
> Hi,
> I'm using SQL Server MSDE RelA (BackEnd) Access Project (FrontEnd). I need
> to Duplicate a record from a form and the linked records in its subform.
> Parent form duplicate goes to table "Jobs" where JobID is the key, records
> from subform with (Link JobID) go to table "Samples". I've created an update
> query in order to do this and I'm getting the following error-message:
> Cannot insert explicit value for identity column in table "Jobs" when
> IDENTITY_INSERT is set to OFF
> Do I need SP4 Service Pack SP4 or am I doing something wrong? Is there any
> other way to duplicate these records?
>
Hi gaba,
The clue is in the question :-)
In order to insert data into a table which has an identity column, if
you want to insert a value instead of accepting the default, do the
following:
SET IDENTITY_INSERT <table name> ON
then doing your inserts, and then
SET IDENTITY_INSERT <table name> OFF
Rather irritatingly, you can only set this option against one table at
a time.
|||Thanks Damien. That was it. So simple...
gaba
"Damien" wrote:

> gaba wrote:
> Hi gaba,
> The clue is in the question :-)
> In order to insert data into a table which has an identity column, if
> you want to insert a value instead of accepting the default, do the
> following:
> SET IDENTITY_INSERT <table name> ON
> then doing your inserts, and then
> SET IDENTITY_INSERT <table name> OFF
> Rather irritatingly, you can only set this option against one table at
> a time.
>

IDENTITY_INSERT is set to OFF

I am trying to insert a new record to a table in my application created by VWD Express. I get beack the responce "Cannot insert explicit value for identity column in table 'Tradersa' when IDENTITY_INSERT is set to OFF" . I have a key record in the table which I would like to increment automatically as I add records so I have set the is identity value to true and both the identity seed and increment to 1.

I have done a fair bit or searching but do not know how to set the table value of IDENTITY_INSERT to ON. Is this as the table is set up or as the record is about to be added? I beleive I should set this when I add the record, but do not know how to in VWD.

Any help would be most welcome. Many thanks in advance

Looks like you are trying to insert a value into a column that has been defined as IDENTITY column? Is that right?|||You need to change the Identity Insert mode. Have a read ofthis article it should explain what is going on.|||

Yes the column is set as IDENTITY. After a bit more reading I think that the issue is with the explicit naming of the identity column. I don't believe that I am explicity defining the field just as @.Trader_ID.

|||Thanks for this, I would like to insert the field without speciying it so that it will increment automatically. The solution in the doc seems to specify the record to be added to the identity field.

Identity...I need to get the last (or highest number in Identity column)...

Ok,
I just need to know how to get the last record inserted by the highest
IDENTITY number. Even if the computer was rebooted and it was two
weeks ago. (Does not have to do with the session).
Any help is appreciated.
Thanks,
TrintSELECT IDENT_CURRENT('table_name') ;

--
David Portas
SQL Server MVP
--

"trint" <trinity.smith@.gmail.com> wrote in message
news:1127164340.720014.70160@.g14g2000cwa.googlegro ups.com...
> Ok,
> I just need to know how to get the last record inserted by the highest
> IDENTITY number. Even if the computer was rebooted and it was two
> weeks ago. (Does not have to do with the session).
> Any help is appreciated.
> Thanks,
> Trint|||Thank you for your quick response, David.
Trint

..Net programmer
trinity.smith@.gmail.com

*** Sent via Developersdex http://www.developersdex.com ***

Identity seed reset in SQL table

I have a test database that is being moved to the production server. Currently in one of the tables I have an identity seed for each record. Is there a way to reset it back to zero. I have deleted all my records but it still doesnt work, and I dont want to create a new table.

ThanksCheck out DBCC CHECKIDENT. This will do what you need.

FromSQL Server 2000 Books Online:

C. Force the current identity value to 30
This example forces the current identity value in the jobs table to a value of 30.

USE pubs
GO
DBCC CHECKIDENT (jobs, RESEED, 30)
GO

Terri|||I believe that if you issue a TRUNCATE TABLE command on the table, the identity seed will be reset to its original value.

Overall, a better strategy, since TRUNCATE TABLE is much faster than the corresponding delete statement...|||how do I do this?|||From within SQL Query Analyzer:

TRUNCATE TABLE tablename
GO

Be careful. This will delete all of the records in your table, swiftly and immediately, upon hitting that magic F5 key (or upon hitting the run arrow).|||mmmm truncate table... :) I've scared many of people with that.

Best thing to do is put that thing in a transaction just in case.

Identity or Sequence column in non-table SELECT ?

I'm looking for a way to specify a column in a SELECT statement
that is a sequence number related to the record number in the result set.
Ideally, just a number from 1 to N.
Example:
Select <?> as Sequence, column1, column2 from table1
The Identity function can do this, but only for SELECT INTO
a new table. I don't want to create a new table.
The NewID() function returns a new global ID, but those
are not sequential.
I was hoping there might be some special variable @.@.XXX
that represents the row number in the results, but could not find
anything like that in the SQL Server documentation.
Any ideas ?
Hi,
There is no concept of Rownum in sql server.
But you could write ur own query to get the serial number.
Use the below script as sample:-
create table item(item_code varchar(05))
go
insert into item values('a1')
insert into item values('a2')
insert into item values('a3')
insert into item values('a4')
go
SELECT (SELECT COUNT(i.item_code)
FROM item i
WHERE i.item_code >= o.item_code ) AS RowID,
item_code
FROM item o
ORDER BY RowID
Note:
This aproach is not recommended on a huge table. This query takes long time
and take more resource.
Thanks
Hari
SQL Server MVP
"GlennM" <GlennM@.discussions.microsoft.com> wrote in message
news:D4FDC49B-262B-49CB-9789-E9AE4570FEA9@.microsoft.com...
> I'm looking for a way to specify a column in a SELECT statement
> that is a sequence number related to the record number in the result set.
> Ideally, just a number from 1 to N.
> Example:
> Select <?> as Sequence, column1, column2 from table1
> The Identity function can do this, but only for SELECT INTO
> a new table. I don't want to create a new table.
> The NewID() function returns a new global ID, but those
> are not sequential.
> I was hoping there might be some special variable @.@.XXX
> that represents the row number in the results, but could not find
> anything like that in the SQL Server documentation.
> Any ideas ?
>
|||On Thu, 2 Jun 2005 12:44:02 -0700, GlennM wrote:

>I'm looking for a way to specify a column in a SELECT statement
>that is a sequence number related to the record number in the result set.
>Ideally, just a number from 1 to N.
>Example:
>Select <?> as Sequence, column1, column2 from table1
>The Identity function can do this, but only for SELECT INTO
>a new table. I don't want to create a new table.
>The NewID() function returns a new global ID, but those
>are not sequential.
>I was hoping there might be some special variable @.@.XXX
>that represents the row number in the results, but could not find
>anything like that in the SQL Server documentation.
>Any ideas ?
>
Hi Glenn,
http://www.aspfaq.com/show.asp?id=2427
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

identity in stored procedures

I have a stored procedure called sp_Insert_System_Header which inserts a record with an identity. I save this as id.
Then in the sp_Customer_Complaint_Entry stored procedure I want to use this field as a parameter for the case_id field.
When I run I get no errors But also no records get inserted into either table
Any Ideas.
CREATE PROCEDURE sp_Insert_System_Header
(
@.System_Type_IDint,
@.Priority_IDint,
@.PCAR_Manager_IDint,
@.Opportunity_IDint,
@.Status_IDint,
@.Initiated_By_IDint,
@.Location_IDint,
@.Other_Locationvarchar(100),
@.Initiated_OnDateTime,
@.Assigned_By_IDint,
@.Assigned_To_IDint,
@.Assigned_OnDateTime,
@.Resolved_OnDateTime,
@.Closed_BY_IDint,
@.Closed_OnDateTime,
@.Descriptionvarchar(2000),
@.Immediate_Actionsvarchar(2000),
@.Cause_Type_IDint,
@.Actual_Cause_Descriptionvarchar(2000),
@.Corrective_Descriptionvarchar(2000),
@.ID int OUTPUT
)
AS
INSERT INTO HEADER(
System_Type_ID,
Priority_ID,
PCAR_Manager_ID,
Opportunity_ID,
Status_ID,
Initiated_By_ID,
Location_ID,
OtherLocation,
Initiated_On,
Assigned_By_ID,
Assigned_To_ID,
Assigned_On,
Resolved_On,
Closed_BY_ID,
Closed_On,
Description,
Immediate_Actions,
Cause_Type_ID,
Actual_Cause_Description,
Corrective_Description
)
VALUES(
@.System_Type_ID,
@.Priority_ID,
@.PCAR_Manager_ID,
@.Opportunity_ID,
@.Status_ID,
@.Initiated_By_ID,
@.Location_ID,
@.Other_Location,
@.Initiated_On,
@.Assigned_By_ID,
@.Assigned_To_ID,
@.Assigned_On,
@.Resolved_On,
@.Closed_BY_ID,
@.Closed_On,
@.Description,
@.Immediate_Actions,
@.Cause_Type_ID,
@.Actual_Cause_Description,
@.Corrective_Description
)
Select @.ID = @.@.Identity
GO
CREATE PROCEDURE sp_Customer_Complaint_Entry
(
@.System_Type_IDint,
@.Priority_IDint,
@.Opportunity_IDint,
@.Status_IDint,
@.Initiated_By_IDint,
@.Location_IDint,
@.Other_Locationvarchar(100),
@.Initiated_OnDateTime,
@.Assigned_By_IDint,
@.Assigned_OnDateTime,
@.Resolved_OnDateTime,
@.Closed_BY_IDint,
@.Closed_OnDateTime,
@.Descriptionvarchar(2000),
@.Immediate_Actionsvarchar(2000),
@.Cause_Type_IDint,
@.Actual_Cause_Descriptionvarchar(2000),
@.Corrective_Descriptionvarchar(2000),
--THESE PARAMETERS ARE FOR CUSTOMER COMPLAINT
--SYSTEM ONLY
@.BusinessUnit_IDint,
@.RMANumbervarchar(50),
@.Product_Codevarchar(50),
@.Product_Namevarchar(100),
@.Customer_Numbervarchar(50),
@.Customer_Namevarchar(50),
@.Lot_Numbervarchar(50),
@.PO_Numbervarchar(50),
@.ID int OUTPUT
)
AS
Declare @.@.CASE_ID int,
@.@.PCAR_Manager_ID int
--FIND OUT WHO IS THE PCAR MANAGER FOR CUSTOMER COMPLAINT SYSTEM
EXEC
@.@.PCAR_Manager_ID = sp_getPCARID @.System_Type_ID, @.Initiated_By_ID
--ADD NEW RECORD TO SYSTEM HEADER
EXEC
@.@.CASE_ID = sp_Insert_System_Header
@.System_Type_ID,
@.Priority_ID,
@.@.PCAR_Manager_ID,
@.Opportunity_ID,
@.Status_ID,
@.Initiated_By_ID,
@.Location_ID,
@.Other_Location,
@.Initiated_On,
@.Assigned_By_ID,
@.@.PCAR_Manager_ID,
@.Assigned_On,
@.Resolved_On,
@.Closed_BY_ID,
@.Closed_On,
@.Description,
@.Immediate_Actions,
@.Cause_Type_ID,
@.Actual_Cause_Description,
@.Corrective_Description
--ADD NEW RECORD TO CUSTOMER COMPLAINT TABLE
INSERT INTO Customer_Complaint_System(
Case_ID,
BusinessUnit_ID,
RMANumber,
Product_Code,
Product_Name,
Customer_Number,
Customer_Name,
Lot_Number,
PO_Number)
VALUES(
@.@.CASE_ID,
@.BusinessUnit_ID,
@.RMANumber,
@.Product_Code,
@.Product_Name,
@.Customer_Number,
@.Customer_Name,
@.Lot_Number,
@.PO_Number
)
SELECT @.ID = @.@.CASE_ID
GO
I think the problem is incorrect usage of OUTPUT parameters.
The following is how you're doing it, which is actually appropriate syntax
for a RETURN value:
DECLARE @.myVariable INT
EXEC @.myVariable = my_Stored_Proc @.params, ...
For OUTPUT parameters, on the other hand, you do it this way:
DECLARE @.myVariable INT
EXEC my_Stored_Proc @.params, ..., @.myVariable OUTPUT
Does that make sense?
RETURN values, by the way, can only be of the INT datatype, and of course
you can only have one of them. OUTPUT params, on the other hand, can be of
any scaler datatype and you can have as many as you want. So they're quite
useful...
Also, two other comments: A) It's recommended that you not use sp_ to
prefix stored procedures as this is the prefix used for system stored
procedures and will cause a small performance penalty due to the server
looking for your stored procedure in the master database before looking
locally. B) You should probably not use @.@. to prefix variables, as that's
the prefix for system variables. Just a code readability issue.
"jat14" <anonymous@.discussions.microsoft.com> wrote in message
news:D140EA0D-E488-429E-9575-9942D9974D7E@.microsoft.com...
> I have a stored procedure called sp_Insert_System_Header which inserts a
record with an identity. I save this as id.
> Then in the sp_Customer_Complaint_Entry stored procedure I want to use
this field as a parameter for the case_id field.
> When I run I get no errors But also no records get inserted into either
table
> Any Ideas.
>
> CREATE PROCEDURE sp_Insert_System_Header
> (
> @.System_Type_ID int,
> @.Priority_ID int,
> @.PCAR_Manager_ID int,
> @.Opportunity_ID int,
> @.Status_ID int,
> @.Initiated_By_ID int,
> @.Location_ID int,
> @.Other_Location varchar(100),
> @.Initiated_On DateTime,
> @.Assigned_By_ID int,
> @.Assigned_To_ID int,
> @.Assigned_On DateTime,
> @.Resolved_On DateTime,
> @.Closed_BY_ID int,
> @.Closed_On DateTime,
> @.Description varchar(2000),
> @.Immediate_Actions varchar(2000),
> @.Cause_Type_ID int,
> @.Actual_Cause_Description varchar(2000),
> @.Corrective_Description varchar(2000),
> @.ID int OUTPUT
> )
> AS
> INSERT INTO HEADER(
> System_Type_ID,
> Priority_ID,
> PCAR_Manager_ID,
> Opportunity_ID,
> Status_ID,
> Initiated_By_ID,
> Location_ID,
> OtherLocation,
> Initiated_On,
> Assigned_By_ID,
> Assigned_To_ID,
> Assigned_On,
> Resolved_On,
> Closed_BY_ID,
> Closed_On,
> Description,
> Immediate_Actions,
> Cause_Type_ID,
> Actual_Cause_Description,
> Corrective_Description
> )
> VALUES(
> @.System_Type_ID,
> @.Priority_ID,
> @.PCAR_Manager_ID,
> @.Opportunity_ID,
> @.Status_ID,
> @.Initiated_By_ID,
> @.Location_ID,
> @.Other_Location,
> @.Initiated_On,
> @.Assigned_By_ID,
> @.Assigned_To_ID,
> @.Assigned_On,
> @.Resolved_On,
> @.Closed_BY_ID,
> @.Closed_On,
> @.Description,
> @.Immediate_Actions,
> @.Cause_Type_ID,
> @.Actual_Cause_Description,
> @.Corrective_Description
> )
> Select @.ID = @.@.Identity
> GO
>
> CREATE PROCEDURE sp_Customer_Complaint_Entry
> (
> @.System_Type_ID int,
> @.Priority_ID int,
> @.Opportunity_ID int,
> @.Status_ID int,
> @.Initiated_By_ID int,
> @.Location_ID int,
> @.Other_Location varchar(100),
> @.Initiated_On DateTime,
> @.Assigned_By_ID int,
> @.Assigned_On DateTime,
> @.Resolved_On DateTime,
> @.Closed_BY_ID int,
> @.Closed_On DateTime,
> @.Description varchar(2000),
> @.Immediate_Actions varchar(2000),
> @.Cause_Type_ID int,
> @.Actual_Cause_Description varchar(2000),
> @.Corrective_Description varchar(2000),
> --THESE PARAMETERS ARE FOR CUSTOMER COMPLAINT
> --SYSTEM ONLY
> @.BusinessUnit_ID int,
> @.RMANumber varchar(50),
> @.Product_Code varchar(50),
> @.Product_Name varchar(100),
> @.Customer_Number varchar(50),
> @.Customer_Name varchar(50),
> @.Lot_Number varchar(50),
> @.PO_Number varchar(50),
> @.ID int OUTPUT
> )
> AS
> Declare @.@.CASE_ID int,
> @.@.PCAR_Manager_ID int
> -- FIND OUT WHO IS THE PCAR MANAGER FOR CUSTOMER COMPLAINT SYSTEM
> EXEC
> @.@.PCAR_Manager_ID = sp_getPCARID @.System_Type_ID, @.Initiated_By_ID
> -- ADD NEW RECORD TO SYSTEM HEADER
> EXEC
> @.@.CASE_ID = sp_Insert_System_Header
> @.System_Type_ID,
> @.Priority_ID,
> @.@.PCAR_Manager_ID,
> @.Opportunity_ID,
> @.Status_ID,
> @.Initiated_By_ID,
> @.Location_ID,
> @.Other_Location ,
> @.Initiated_On,
> @.Assigned_By_ID,
> @.@.PCAR_Manager_ID,
> @.Assigned_On,
> @.Resolved_On,
> @.Closed_BY_ID,
> @.Closed_On,
> @.Description,
> @.Immediate_Actions,
> @.Cause_Type_ID,
> @.Actual_Cause_Description,
> @.Corrective_Description
> -- ADD NEW RECORD TO CUSTOMER COMPLAINT TABLE
> INSERT INTO Customer_Complaint_System(
> Case_ID,
> BusinessUnit_ID,
> RMANumber,
> Product_Code,
> Product_Name,
> Customer_Number,
> Customer_Name,
> Lot_Number,
> PO_Number)
> VALUES(
> @.@.CASE_ID,
> @.BusinessUnit_ID,
> @.RMANumber,
> @.Product_Code,
> @.Product_Name,
> @.Customer_Number,
> @.Customer_Name,
> @.Lot_Number,
> @.PO_Number
> )
> SELECT @.ID = @.@.CASE_ID
>
> GO
>

identity fields - losing values

Hi,
I've an application that consists of a of main table. Each record requires a
numeric reference and these references must be sequential with no gaps.
At the moment, this reference field is of an identity type. This works fine
most of the time, however every now and again the identity field 'loses' a
number (for example goes from 58 to 60) which is a right pain for me.
From my limited understanding of TSQL I see two alternatives
1. Have a manual identity field which I increment manually and use locking
and error trapping to ensure that no values are 'lost'. However, I'm not
really sure how to achieve this...
2. Even if when an identity value is 'lost' it is marked as such is fine, I
simply cannot have any gaps. Can I keep the identity value in place but use
error trapping to ensure that when an insert fails the identity is marked
appropriately rather than simply 'lost'.
Which of the above methods is the recommended one (or are there any other
alternatives) and can any kind soul point me in the right direction as to
how to achieve this (for example, links, etc).
Any and all advice is gratefully received.
Kind regards
Chris.You can't really avoid gaps with an IDENTITY column. If you care about
the value inserted then IDENTITY is the wrong solution.
You haven't explained how this value is to be used. If it is to be
purely based on insertion order then maybe you don't even need it in
the table. Just add a Creation Date column, order by that and display
the row number client-side or in a query.
You can increment a value yourself on the INSERT:
INSERT INTO YourTable (x, ...)
SELECT COALESCE(MAX(x),0)+1, ...
FROM YourTable
but this effectively serialises every INSERT, which may not be
acceptable in a multi-user environment. Logically there isn't a way out
of this: You can't allow concurrent updates if you need to maintain a
serial key in real-time because a rolled-back transaction will always
leave a gap.
David Portas
SQL Server MVP
--|||> 2. Even if when an identity value is 'lost' it is marked as such is
fine, I
> simply cannot have any gaps. Can I keep the identity value in place
but use
> error trapping to ensure that when an insert fails the identity is
marked
> appropriately rather than simply 'lost'.
If you mean that you just want to show the missing values in the data,
you can do so like this:
SELECT
N.num AS id, T.*
FROM Numbers AS N
LEFT JOIN YourTable AS T
ON N.num = T.id
WHERE N.num BETWEEN 1 AND 999999999
Where Numbers is a table containing the total range of IDs.
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1112956106.268466.113170@.f14g2000cwb.googlegroups.com...
> You can't really avoid gaps with an IDENTITY column. If you care about
> the value inserted then IDENTITY is the wrong solution.
> You haven't explained how this value is to be used. If it is to be
> purely based on insertion order then maybe you don't even need it in
> the table. Just add a Creation Date column, order by that and display
> the row number client-side or in a query.
> You can increment a value yourself on the INSERT:
> INSERT INTO YourTable (x, ...)
> SELECT COALESCE(MAX(x),0)+1, ...
> FROM YourTable
> but this effectively serialises every INSERT, which may not be
> acceptable in a multi-user environment. Logically there isn't a way out
> of this: You can't allow concurrent updates if you need to maintain a
> serial key in real-time because a rolled-back transaction will always
> leave a gap.
> --
> David Portas
> SQL Server MVP
> --
>
David,
Many thanks for your reply. For some background, the reference is simply a
way of identifying each record which as you suggested is based on insertion
order (although to be honest - this isn't terribly important - it wouldn't
be a problem for record X to have a lower ID than Y even if X was inserted
after Y).
I realise that ideally this wouldn't be a requirement as long as the ID is
unique but there is some inertia from another department who see gaps as
indicating missing records (and despite my best efforts can't be convinced
otherwise).
Based on what you've said, how does this sound:
Have a IDStore table. Two fields, "ID" and "committed". When an insert is
attempted, the first ID is found which is uncommitted and this is used. If
no uncommitted ID's are found then a row is inserted into the IDStore table,
with the next free ID and an uncommitted value. When the insert is
successful, the committed field is marked as committed. If it is
unsuccessful it is marked as uncommitted. And so on.
Assuming that I've managed to explain it clearly, does this sound like a
reasonable approach? I realise that I'll have to investigate locking and so
on but at least its something I can work towards..
Once again, your advice is gratefully received.
Chris.|||This is a risk whenever you expose IDENTITY to users. The IDENTITY
value acquires business meaning for the users and then you are lost
because there are too many scenarios in which you can't control the
IDENTITY value. The best policy is not to expose IDENTITY to users at
all.
Assuming the users don't have direct access to the tables (not usually
a good idea to allow this anyway), just don't display the IDENTITY,
which after all should only be used as an artificial key. I assume your
table has an alternative, business key as well. If not then you have a
more serious design problem. IDENTITY should never be the only key of a
table.
If the users need the "comfort factor" of seeing the numbers then see
my other reply on how to fill in the gaps. As you suggest, there are
alternative strategies but they all involve serializing the inserts, or
accepting that there will be gaps.
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1112958821.065794.173610@.o13g2000cwo.googlegroups.com...
> This is a risk whenever you expose IDENTITY to users. The IDENTITY
> value acquires business meaning for the users and then you are lost
> because there are too many scenarios in which you can't control the
> IDENTITY value. The best policy is not to expose IDENTITY to users at
> all.
> Assuming the users don't have direct access to the tables (not usually
> a good idea to allow this anyway), just don't display the IDENTITY,
> which after all should only be used as an artificial key. I assume your
> table has an alternative, business key as well. If not then you have a
> more serious design problem. IDENTITY should never be the only key of a
> table.
> If the users need the "comfort factor" of seeing the numbers then see
> my other reply on how to fill in the gaps. As you suggest, there are
> alternative strategies but they all involve serializing the inserts, or
> accepting that there will be gaps.
> --
> David Portas
> SQL Server MVP
> --
>
David,
Thanks again for your reply. Based on your advice, I have redesigned the
table in question so that the IDENTITY field is internal to the system and
not visible by the user.
I have created an additional, INT field in this table which will be used for
the reference and will be gapless and sequential - although I've yet to
figure out exactly how to achieve this (but I see triggers in use)...
Thanks once again,
Chris.|||CHris,
Does this new field (we say "column" in SQL btw) have to be immutable?
Will they throw a fit if the record 227 becomes record 226 after someone
deletes record 200?
Will it be impossible to delete records from the table?
If any of these answers are NO, And if the performance hit is acceptable,
then I might suggest that you not persist a value in this new column at all,
Just calculate it on display, as the Count of all other records in the table
with Identity Value <= IDentity value of the record you're displaying
Select <Other COls>,
(Select Count(*) From Table
Where ID <= T.ID) As RowID
From Table T
"Chris Strug" wrote:

> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1112958821.065794.173610@.o13g2000cwo.googlegroups.com...
> David,
> Thanks again for your reply. Based on your advice, I have redesigned the
> table in question so that the IDENTITY field is internal to the system and
> not visible by the user.
> I have created an additional, INT field in this table which will be used f
or
> the reference and will be gapless and sequential - although I've yet to
> figure out exactly how to achieve this (but I see triggers in use)...
> Thanks once again,
> Chris.
>
>|||"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:992173F7-F444-4C7E-821A-A18B4F385944@.microsoft.com...
> CHris,
> Does this new field (we say "column" in SQL btw) have to be immutable?
> Will they throw a fit if the record 227 becomes record 226 after someone
> deletes record 200?
> Will it be impossible to delete records from the table?
> If any of these answers are NO, And if the performance hit is acceptable,
> then I might suggest that you not persist a value in this new column at
all,
> Just calculate it on display, as the Count of all other records in the
table
> with Identity Value <= IDentity value of the record you're displaying
> Select <Other COls>,
> (Select Count(*) From Table
> Where ID <= T.ID) As RowID
> From Table T
Hi!
Unfortunately the answer to all your questions is a resounding "yes!".
However, the example you mention is something that I've crossed before and I
feel will be very useful sooner or later.
Thanks for your advice though!
Chris.

identity and identity increment

Hello,
whenever I add a record to a table with identity column it increments by one
which is what I want. But when I input a record that was reject for any
reason (I.e. primary key violation) and then input another record it will
not be increment by one...it skips the number that would of been assigned
to the incorrect record. is there an easy to ensure that it always
increments by one?> whenever I add a record to a table with identity column it increments by
> one which is what I want. But when I input a record that was reject for
> any reason (I.e. primary key violation) and then input another record it
> will not be increment by one...it skips the number that would of been
> assigned to the incorrect record. is there an easy to ensure that it
> always increments by one?
No, if you are going to use IDENTITY, you should learn to live with gaps, or
else use something more like a sequence generator (essentially you lock the
entire table and use MAX(id_col)+1). If you delete any row other than the
most recent, how do you account for that gap? Do you intend to re-insert a
row with ID = 40 when in fact your next ID should have been 722? Are you
associatng some tangible value to the actual ID value generated? If so,
why? In reality, the consumers of the data should not really care if thir
next row gets an ID of 36 or 37 or 38.
http://www.aspfaq.com/2523
A|||That is the nature of using IDENTITY; transactions that are rolled back caus
e
gaps in the sequence. It's one of the reasons some DBA's won't use IDENTITY.
The only way around it is to use a custom id sequence function. Itzik
Ben-Gan wrote an article for SQL Server Magazine a few months back on how to
create custom identity functions.
HTH,
-Mark Williams
"John Smith" wrote:

> Hello,
> whenever I add a record to a table with identity column it increments by o
ne
> which is what I want. But when I input a record that was reject for any
> reason (I.e. primary key violation) and then input another record it will
> not be increment by one...it skips the number that would of been assigned
> to the incorrect record. is there an easy to ensure that it always
> increments by one?
>
>|||thanks... that helps
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uCh7ueQeGHA.4276@.TK2MSFTNGP03.phx.gbl...
> No, if you are going to use IDENTITY, you should learn to live with gaps,
> or else use something more like a sequence generator (essentially you lock
> the entire table and use MAX(id_col)+1). If you delete any row other than
> the most recent, how do you account for that gap? Do you intend to
> re-insert a row with ID = 40 when in fact your next ID should have been
> 722? Are you associatng some tangible value to the actual ID value
> generated? If so, why? In reality, the consumers of the data should not
> really care if thir next row gets an ID of 36 or 37 or 38.
> http://www.aspfaq.com/2523
> A
>

Identity

I have a numerid field with an identity set on it so that every record get's
an incremented number in this field automatically.
If I delete a record, the numbering is no longer sequential as one record
has gone.
Is there any way of forcing a re-number of the records to make them
sequential again?
Thanks
You can use DBCC CHECKIDENT to reset. But there's no feature to compress "holes" in the sequence. The question
is why does it matter if they are sequential or not? They are only used to identify something anyhow! Also, be
aware that rollbacks and things like that can mean that you consume a value which isn't inserted to the table,
leading to a "gap".
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Keith" <@..> wrote in message news:%23OAeaT%23FEHA.3908@.TK2MSFTNGP12.phx.gbl...
> I have a numerid field with an identity set on it so that every record get's
> an incremented number in this field automatically.
> If I delete a record, the numbering is no longer sequential as one record
> has gone.
> Is there any way of forcing a re-number of the records to make them
> sequential again?
> Thanks
>
|||In addition to Tibor's response, if you wrote a trigger or something which
did compress the values it would very likely be quite expensive... These
PK values should have no meaning...
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Keith" <@..> wrote in message
news:%23OAeaT%23FEHA.3908@.TK2MSFTNGP12.phx.gbl...
> I have a numerid field with an identity set on it so that every record
get's
> an incremented number in this field automatically.
> If I delete a record, the numbering is no longer sequential as one record
> has gone.
> Is there any way of forcing a re-number of the records to make them
> sequential again?
> Thanks
>

Identity

I have a numerid field with an identity set on it so that every record get's
an incremented number in this field automatically.
If I delete a record, the numbering is no longer sequential as one record
has gone.
Is there any way of forcing a re-number of the records to make them
sequential again?
ThanksYou can use DBCC CHECKIDENT to reset. But there's no feature to compress "ho
les" in the sequence. The question
is why does it matter if they are sequential or not? They are only used to i
dentify something anyhow! Also, be
aware that rollbacks and things like that can mean that you consume a value
which isn't inserted to the table,
leading to a "gap".
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Keith" <@..> wrote in message news:%23OAeaT%23FEHA.3908@.TK2MSFTNGP12.phx.gbl...ed">
> I have a numerid field with an identity set on it so that every record get
's
> an incremented number in this field automatically.
> If I delete a record, the numbering is no longer sequential as one record
> has gone.
> Is there any way of forcing a re-number of the records to make them
> sequential again?
> Thanks
>|||In addition to Tibor's response, if you wrote a trigger or something which
did compress the values it would very likely be quite expensive... These
PK values should have no meaning...
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Keith" <@..> wrote in message
news:%23OAeaT%23FEHA.3908@.TK2MSFTNGP12.phx.gbl...
> I have a numerid field with an identity set on it so that every record
get's
> an incremented number in this field automatically.
> If I delete a record, the numbering is no longer sequential as one record
> has gone.
> Is there any way of forcing a re-number of the records to make them
> sequential again?
> Thanks
>