Identity column: March 2012

Friday, March 30, 2012

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

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

If Nothing Selected Into Variable What Is The Value Of Variable?

DECLARE @.PayTypeValue numeric (18, 5)
SET @.PayTypeValue = (SELECT [LoadPayValue] FROM [CSITSS].[dbo].[LoadPayType] WHERE [CompanyDiv] = @.CompDiv AND [Deleted] = 0 AND [LoadPayType] = @.LoadPay)
IF THIS QUERY RETURNS NOTHING WHAT DOES IT SET @.PayTypeValue to?
NULL OR 0DECLARE @.PayTypeValue numeric (18, 5)

SET @.PayTypeValue = (SELECT [LoadPayValue] FROM [CSITSS].[dbo].[LoadPayType] WHERE [CompanyDiv] = @.CompDiv AND [Deleted] = 0 AND [LoadPayType] = @.LoadPay)

IF THIS QUERY RETURNS NOTHING WHAT DOES IT SET @.PayTypeValue to?

NULL OR 0

why don't you try it and see for yourself:

SELECT @.PayTypeValue= [LoadPayValue] FROM [CSITSS].[dbo].[LoadPayType] WHERE 1=0
select @.PayTypeValue|||Also, try
SET @.PayTypeValue = 42
SELECT @.PayTypeValue= [LoadPayValue] FROM [CSITSS].[dbo].[LoadPayType] WHERE 1=0
select @.PayTypeValue

and see what happens.|||i thought it would return NULL.....|||But it returns...

The answer would be useful for people with similar questions ;)|||The answer is 42.
If the query returns no rows, the variable keeps its old value.|||Ahh that's clever - kinda like having an "else" value.
Cheers ivon|||hmmm...

That could be dangerous if you were using that variable in a loop.
I would wrap the SQL in ISNULL()

set @.somevariable=ISNULL((select somevalue from sometable),-9999999)
If @.somevariable=-9999999
'no rows were returned
Else|||That could be dangerous if you were using that variable in a loop.

How so? It retained it's initially declared value...
If you looped round, @.PayTypeValue would equal 42 each time..?|||Yes, but if you were expecting the value to change with each iteration...
What is returned from the select statement is also dependant on
@.compdiv and @.loadpay, so if those values change, it's quite possilbe that @.paytypevalue would change, too.|||Yes, but if you were expecting the value to change with each iteration...
That's how I found out; I got some very peculiar results.

BTW using IsNull() won't help: the query returns no rows, so there is no null value to replace with something else.

Solutions I'm using are
- Setting the variable to a default value before filling it with the query,
- Checking @.@.ROWCOUNT to see if any rows were returned from the query.|||I think it all depends on how you put it together.

declare @.tmp integer
set @.tmp=42
set @.tmp=isnull((select 18 where 1=0),0)
select @.tmp

Returns 0, which is what I'd expect, since
SELECT 18 WHERE 1=0
returns no rows|||How do I use @.@.ROWCOUNT to do this?

I haven't set this variable to anything before this and at least this instance doesn't loop. This SP is called once per order and checks 14 different things. Then feeds the results into a verification table. Those results and then selected by Crystal Reports and displayed to the user for an entire batch of orders one at a time by order number.|||RedNeckGeek: I now see that you put the isnull around the entire query.

tdecker81:
Something like
SELECT @.l_var = value
FROM table
WHERE <conditions>

SELECT @.l_records = @.@.ROWCOUNT

IF @.l_records > 0
BEGIN
' Do stuff with @.l_var
END|||Interesting. Since I rarely use the first syntax, I was unaware of this behavior:
set nocount on

declare @.MyValue int
set @.MyValue = 42

--This method retains @.MyValue
select @.MyValue = id from sysobjects where 1 = 0
select @.MyValue as 'Unchanged!'

--This method sets @.MyValue to NULL
set @.MyValue = (select id from sysobjects where 1 = 0)
select @.MyValue as 'Set to NULL!'

Learn something new every day...|||Learn something new every day...

my problem is I forget 2 somethings every day, so I think I'm losing the battle...

(most of it is stuff my wife tells me so I guess it's ok)

:)|||I'm sure you're wife will be there to constantly remind you too.sql

If Not Exists?

Curious about the above:
If there is a query that says
If not exists(select * from table)
versus one that says
If not exists(select * from table with(NoLOCK))
Is the second statement more correct than the first (considering that there
may be hundreds of hits to that table in a very short space of time -
possible 100's per second)?
Or is the second statement truly no different than the first as the
statement would discover that the record had a count > 0 and thus validate
the "if not exists" statment as false?
Regards,
Jamie
On Mon, 30 Jul 2007 11:02:05 -0700, thejamie wrote:

>Curious about the above:
>If there is a query that says
>If not exists(select * from table)
>versus one that says
>If not exists(select * from table with(NoLOCK))
>Is the second statement more correct than the first (considering that there
>may be hundreds of hits to that table in a very short space of time -
>possible 100's per second)?
Hi thejamie,
The seconds statement is faster, but LESS correct. If the table has no
rows, one user proceeds to insert a row that violates a constraint (so
that the insert will be rolled back) and another user runs the second
version of the query, it will "see" the row inserted by the first user,
even though that row logically never existed. And the revere goes to: if
one user runs a DELETE without WHERE clause that gets rolled back later,
the query with nolock hint will temporarily see no rows, even though
they logically were never gone.
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
|||Yes. LESS correct, unfortunately it is the transaction table - roughly 2 or
three million of these a week - it is hard to touch this table without
conflicts - we sort of gave up and now use the with(nolock) when touching the
table. The issue came up as this particular call to the transactions table
brought up the question of what does the "EXISTS" keyword actually do when it
accesses a table.
For example, does it open the PK index to see if it has a count and shut it
back down again?
or possibly:
Opens the table like a select top 1 call might do,
or worst possibly scenario - "EXISTS" opens the entire index and scans
through from beginning to end to determine the condition as true or false.
What is actually written is " IF NOT EXISTS " which flowed more logically -
from the context - if the table does not exist - which at about 2:30 AM in
the morning when it gets archived to the datawarehouse - it essentially does
not exist from that point until about 5 am when the warehouse starts up -
question remains:
Is the table accessing all the records with the "Exists" keyword, or does it
try to scan the entire index... etc?
Regards,
Jamie
"Hugo Kornelis" wrote:

> On Mon, 30 Jul 2007 11:02:05 -0700, thejamie wrote:
>
> Hi thejamie,
> The seconds statement is faster, but LESS correct. If the table has no
> rows, one user proceeds to insert a row that violates a constraint (so
> that the insert will be rolled back) and another user runs the second
> version of the query, it will "see" the row inserted by the first user,
> even though that row logically never existed. And the revere goes to: if
> one user runs a DELETE without WHERE clause that gets rolled back later,
> the query with nolock hint will temporarily see no rows, even though
> they logically were never gone.
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
>
|||On Tue, 31 Jul 2007 06:02:05 -0700, thejamie wrote:

>Yes. LESS correct, unfortunately it is the transaction table - roughly 2 or
>three million of these a week - it is hard to touch this table without
>conflicts - we sort of gave up and now use the with(nolock) when touching the
>table. The issue came up as this particular call to the transactions table
>brought up the question of what does the "EXISTS" keyword actually do when it
>accesses a table.
>For example, does it open the PK index to see if it has a count and shut it
>back down again?
>or possibly:
>Opens the table like a select top 1 call might do,
>or worst possibly scenario - "EXISTS" opens the entire index and scans
>through from beginning to end to determine the condition as true or false.
>What is actually written is " IF NOT EXISTS " which flowed more logically -
>from the context - if the table does not exist - which at about 2:30 AM in
>the morning when it gets archived to the datawarehouse - it essentially does
>not exist from that point until about 5 am when the warehouse starts up -
>question remains:
>Is the table accessing all the records with the "Exists" keyword, or does it
>try to scan the entire index... etc?
Hi thejamie,
Both EXISTS and NOT EXISTS will stop processing as soon as the first row
matching the WHERE clause (if any) is found.
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
|||On Jul 30, 1:02 pm, thejamie <theja...@.discussions.microsoft.com>
wrote:
> Curious about the above:
> If there is a query that says
> If not exists(select * from table)
> versus one that says
> If not exists(select * from table with(NoLOCK))
> Is the second statement more correct than the first (considering that there
> may be hundreds of hits to that table in a very short space of time -
> possible 100's per second)?
> Or is the second statement truly no different than the first as the
> statement would discover that the record had a count > 0 and thus validate
> the "if not exists" statment as false?
> --
> Regards,
> Jamie
Suggestion:
Try using Select 1 instead of Select * in this scenario. That is an
old db tuning trick. Let's it use whatever index fits the where
clause and doesn't have to go to the table, retrieve and subsequently
discard all of the data in the found rows.
|||On Wed, 01 Aug 2007 17:12:50 -0700, karlag92 wrote:
[vbcol=seagreen]
>On Jul 30, 1:02 pm, thejamie <theja...@.discussions.microsoft.com>
>wrote:
(snip)
>Suggestion:
>Try using Select 1 instead of Select * in this scenario. That is an
>old db tuning trick.
Hi karlag92,
The trick is, in fact, so old that it doesn't apply anymore. And it
actually never has applied for SQL Server. It was, IIRC, either Oracle
or DB2 that erroneously decided to expand the * to a column list even if
it was part of a [NOT] EXISTS subquery - and that has of course long be
fixed.
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

If Not Exists?

Curious about the above:
If there is a query that says
If not exists(select * from table)
versus one that says
If not exists(select * from table with(NoLOCK))
Is the second statement more correct than the first (considering that there
may be hundreds of hits to that table in a very short space of time -
possible 100's per second)?
Or is the second statement truly no different than the first as the
statement would discover that the record had a count > 0 and thus validate
the "if not exists" statment as false?
--
Regards,
JamieOn Mon, 30 Jul 2007 11:02:05 -0700, thejamie wrote:

>Curious about the above:
>If there is a query that says
>If not exists(select * from table)
>versus one that says
>If not exists(select * from table with(NoLOCK))
>Is the second statement more correct than the first (considering that there
>may be hundreds of hits to that table in a very short space of time -
>possible 100's per second)?
Hi thejamie,
The seconds statement is faster, but LESS correct. If the table has no
rows, one user proceeds to insert a row that violates a constraint (so
that the insert will be rolled back) and another user runs the second
version of the query, it will "see" the row inserted by the first user,
even though that row logically never existed. And the revere goes to: if
one user runs a DELETE without WHERE clause that gets rolled back later,
the query with nolock hint will temporarily see no rows, even though
they logically were never gone.
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||Yes. LESS correct, unfortunately it is the transaction table - roughly 2 or
three million of these a week - it is hard to touch this table without
conflicts - we sort of gave up and now use the with(nolock) when touching th
e
table. The issue came up as this particular call to the transactions table
brought up the question of what does the "EXISTS" keyword actually do when i
t
accesses a table.
For example, does it open the PK index to see if it has a count and shut it
back down again?
or possibly:
Opens the table like a select top 1 call might do,
or worst possibly scenario - "EXISTS" opens the entire index and scans
through from beginning to end to determine the condition as true or false.
What is actually written is " IF NOT EXISTS " which flowed more logically -
from the context - if the table does not exist - which at about 2:30 AM in
the morning when it gets archived to the datawarehouse - it essentially does
not exist from that point until about 5 am when the warehouse starts up -
question remains:
Is the table accessing all the records with the "Exists" keyword, or does it
try to scan the entire index... etc?
--
Regards,
Jamie
"Hugo Kornelis" wrote:

> On Mon, 30 Jul 2007 11:02:05 -0700, thejamie wrote:
>
> Hi thejamie,
> The seconds statement is faster, but LESS correct. If the table has no
> rows, one user proceeds to insert a row that violates a constraint (so
> that the insert will be rolled back) and another user runs the second
> version of the query, it will "see" the row inserted by the first user,
> even though that row logically never existed. And the revere goes to: if
> one user runs a DELETE without WHERE clause that gets rolled back later,
> the query with nolock hint will temporarily see no rows, even though
> they logically were never gone.
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
>|||On Tue, 31 Jul 2007 06:02:05 -0700, thejamie wrote:

>Yes. LESS correct, unfortunately it is the transaction table - roughly 2 o
r
>three million of these a week - it is hard to touch this table without
>conflicts - we sort of gave up and now use the with(nolock) when touching t
he
>table. The issue came up as this particular call to the transactions table
>brought up the question of what does the "EXISTS" keyword actually do when
it
>accesses a table.
>For example, does it open the PK index to see if it has a count and shut it
>back down again?
>or possibly:
>Opens the table like a select top 1 call might do,
>or worst possibly scenario - "EXISTS" opens the entire index and scans
>through from beginning to end to determine the condition as true or false.
>What is actually written is " IF NOT EXISTS " which flowed more logically -
>from the context - if the table does not exist - which at about 2:30 AM in
>the morning when it gets archived to the datawarehouse - it essentially doe
s
>not exist from that point until about 5 am when the warehouse starts up -
>question remains:
>Is the table accessing all the records with the "Exists" keyword, or does i
t
>try to scan the entire index... etc?
Hi thejamie,
Both EXISTS and NOT EXISTS will stop processing as soon as the first row
matching the WHERE clause (if any) is found.
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||On Jul 30, 1:02 pm, thejamie <theja...@.discussions.microsoft.com>
wrote:
> Curious about the above:
> If there is a query that says
> If not exists(select * from table)
> versus one that says
> If not exists(select * from table with(NoLOCK))
> Is the second statement more correct than the first (considering that ther
e
> may be hundreds of hits to that table in a very short space of time -
> possible 100's per second)?
> Or is the second statement truly no different than the first as the
> statement would discover that the record had a count > 0 and thus validate
> the "if not exists" statment as false?
> --
> Regards,
> Jamie
Suggestion:
Try using Select 1 instead of Select * in this scenario. That is an
old db tuning trick. Let's it use whatever index fits the where
clause and doesn't have to go to the table, retrieve and subsequently
discard all of the data in the found rows.|||On Wed, 01 Aug 2007 17:12:50 -0700, karlag92 wrote:

>On Jul 30, 1:02 pm, thejamie <theja...@.discussions.microsoft.com>
>wrote:
(snip)[vbcol=seagreen]
>Suggestion:
>Try using Select 1 instead of Select * in this scenario. That is an
>old db tuning trick.
Hi karlag92,
The trick is, in fact, so old that it doesn't apply anymore. And it
actually never has applied for SQL Server. It was, IIRC, either Oracle
or DB2 that erroneously decided to expand the * to a column list even if
it was part of a [NOT] EXISTS subquery - and that has of course long be
fixed.
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

If Not Exists?

Curious about the above:
If there is a query that says
If not exists(select * from table)
versus one that says
If not exists(select * from table with(NoLOCK))
Is the second statement more correct than the first (considering that there
may be hundreds of hits to that table in a very short space of time -
possible 100's per second)?
Or is the second statement truly no different than the first as the
statement would discover that the record had a count > 0 and thus validate
the "if not exists" statment as false?
--
Regards,
JamieOn Mon, 30 Jul 2007 11:02:05 -0700, thejamie wrote:
>Curious about the above:
>If there is a query that says
>If not exists(select * from table)
>versus one that says
>If not exists(select * from table with(NoLOCK))
>Is the second statement more correct than the first (considering that there
>may be hundreds of hits to that table in a very short space of time -
>possible 100's per second)?
Hi thejamie,
The seconds statement is faster, but LESS correct. If the table has no
rows, one user proceeds to insert a row that violates a constraint (so
that the insert will be rolled back) and another user runs the second
version of the query, it will "see" the row inserted by the first user,
even though that row logically never existed. And the revere goes to: if
one user runs a DELETE without WHERE clause that gets rolled back later,
the query with nolock hint will temporarily see no rows, even though
they logically were never gone.
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||Yes. LESS correct, unfortunately it is the transaction table - roughly 2 or
three million of these a week - it is hard to touch this table without
conflicts - we sort of gave up and now use the with(nolock) when touching the
table. The issue came up as this particular call to the transactions table
brought up the question of what does the "EXISTS" keyword actually do when it
accesses a table.
For example, does it open the PK index to see if it has a count and shut it
back down again?
or possibly:
Opens the table like a select top 1 call might do,
or worst possibly scenario - "EXISTS" opens the entire index and scans
through from beginning to end to determine the condition as true or false.
What is actually written is " IF NOT EXISTS " which flowed more logically -
from the context - if the table does not exist - which at about 2:30 AM in
the morning when it gets archived to the datawarehouse - it essentially does
not exist from that point until about 5 am when the warehouse starts up -
question remains:
Is the table accessing all the records with the "Exists" keyword, or does it
try to scan the entire index... etc?
--
Regards,
Jamie
"Hugo Kornelis" wrote:
> On Mon, 30 Jul 2007 11:02:05 -0700, thejamie wrote:
> >Curious about the above:
> >
> >If there is a query that says
> >If not exists(select * from table)
> >versus one that says
> >If not exists(select * from table with(NoLOCK))
> >
> >Is the second statement more correct than the first (considering that there
> >may be hundreds of hits to that table in a very short space of time -
> >possible 100's per second)?
> Hi thejamie,
> The seconds statement is faster, but LESS correct. If the table has no
> rows, one user proceeds to insert a row that violates a constraint (so
> that the insert will be rolled back) and another user runs the second
> version of the query, it will "see" the row inserted by the first user,
> even though that row logically never existed. And the revere goes to: if
> one user runs a DELETE without WHERE clause that gets rolled back later,
> the query with nolock hint will temporarily see no rows, even though
> they logically were never gone.
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
>|||On Tue, 31 Jul 2007 06:02:05 -0700, thejamie wrote:
>Yes. LESS correct, unfortunately it is the transaction table - roughly 2 or
>three million of these a week - it is hard to touch this table without
>conflicts - we sort of gave up and now use the with(nolock) when touching the
>table. The issue came up as this particular call to the transactions table
>brought up the question of what does the "EXISTS" keyword actually do when it
>accesses a table.
>For example, does it open the PK index to see if it has a count and shut it
>back down again?
>or possibly:
>Opens the table like a select top 1 call might do,
>or worst possibly scenario - "EXISTS" opens the entire index and scans
>through from beginning to end to determine the condition as true or false.
>What is actually written is " IF NOT EXISTS " which flowed more logically -
>from the context - if the table does not exist - which at about 2:30 AM in
>the morning when it gets archived to the datawarehouse - it essentially does
>not exist from that point until about 5 am when the warehouse starts up -
>question remains:
>Is the table accessing all the records with the "Exists" keyword, or does it
>try to scan the entire index... etc?
Hi thejamie,
Both EXISTS and NOT EXISTS will stop processing as soon as the first row
matching the WHERE clause (if any) is found.
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||On Jul 30, 1:02 pm, thejamie <theja...@.discussions.microsoft.com>
wrote:
> Curious about the above:
> If there is a query that says
> If not exists(select * from table)
> versus one that says
> If not exists(select * from table with(NoLOCK))
> Is the second statement more correct than the first (considering that there
> may be hundreds of hits to that table in a very short space of time -
> possible 100's per second)?
> Or is the second statement truly no different than the first as the
> statement would discover that the record had a count > 0 and thus validate
> the "if not exists" statment as false?
> --
> Regards,
> Jamie
Suggestion:
Try using Select 1 instead of Select * in this scenario. That is an
old db tuning trick. Let's it use whatever index fits the where
clause and doesn't have to go to the table, retrieve and subsequently
discard all of the data in the found rows.|||On Wed, 01 Aug 2007 17:12:50 -0700, karlag92 wrote:
>On Jul 30, 1:02 pm, thejamie <theja...@.discussions.microsoft.com>
>wrote:
>> Curious about the above:
>> If there is a query that says
>> If not exists(select * from table)
>> versus one that says
>> If not exists(select * from table with(NoLOCK))
(snip)
>Suggestion:
>Try using Select 1 instead of Select * in this scenario. That is an
>old db tuning trick.
Hi karlag92,
The trick is, in fact, so old that it doesn't apply anymore. And it
actually never has applied for SQL Server. It was, IIRC, either Oracle
or DB2 that erroneously decided to expand the * to a column list even if
it was part of a [NOT] EXISTS subquery - and that has of course long be
fixed.
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

IF NOT EXISTS problem

Hi,

I am migrating a project from SQL to SQL Compact Edition and the following statement keeps failing in the CE project:

Code Snippet

IF NOT EXISTS (SELECT * FROM Court2 WHERE BookingDate = '2007-05-28') INSERT INTO Court2 (BookingDate,T1100) VALUES ('2007-05-28',52) ELSE UPDATE Court2 SET T1100 = 52 WHERE (BookingDate = '2007-05-28')

It works fine in SQL.

I've done a fair bit of searching for the solution and it appears my syntax is not perfect but I can't see where.

Any suggestions.

Here's the error that CE produces:

Code Snippet

There was an error parsing the query. [ Token line number = 1,Token line offset = 1,Token in error = IF ]

Thanks,

Glen.

Hi Glen,

IF.. ELSE is not available in SQL CE, as only a subset of the full T-SQL grammar is supported by SQL CE,

so you will have to do this in code, like:

Get a DataReader with

Code Snippet

SELECT * FROM Court2 WHERE BookingDate = '2007-05-28'

and perform your insert or update depending on the result of this.

For documentation on SQL CE SQL syntax see: http://msdn2.microsoft.com/en-us/library/ms173372.aspx

|||

Thank you.

I'm now working on the next issue:

Unfortunately I'm only a hacker and learning the hard way .. Is there somewhere that I can go that lists the differences and workarounds for the CE that I can read.

Cheers.

|||See SQL CE Compact Edition BOL, - for documentation on SQL CE SQL syntax see: http://msdn2.microsoft.com/en-us/library/ms173372.aspx

IF NOT EXISTS in Oracle?

is oracle capable of doing this?
IF NOT EXISTS( SELECT item_id from PROJECT_WMS_TEMP where item_id = ITEM_ID) THEN
BUILD_PROJECT_WMS_TEMP;
End If;
Thanks
ChristianHello,

you can use PL/SQL Funktion in a SQL statement

something like ...

BEGIN
SELECT ...
EXCEPTION
WHEN NO_DATA_FOUND THEN
call proc;
END;

Hope this help ?

Greetings
Manfred Peter
(Alligator Company)
http://www.alligatorsql.com

*** did you ever use Oracle, MySQL, Interbase and Birdstep with one tool ? No ? Try AlligatorSQL ... ***sql

IF NOT EXISTS (... - EXISTS TABLE : nested iteration. Table Scan.Forward scan.

Hi,

This is on Sybase but I'm guessing that the same situation would happen on SQL Server. (Please confirm if you know).

I'm looking at these new databases and I'm seeing code similar to this all over the place:

if not exists (select 1 from dbo.t1 where f1 = @.p1)
begin
select @.errno = @.errno | 1
end

There's a unique clustered in dex on t1.f1.

The execution plan shows this for this statement:

FROM TABLE
dbo.t1
EXISTS TABLE : nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.

It's not using my index!!!!!

It seems to be the case with EXISTS statements. Can anybody confirm?

I also hinted to use the index but it still didn't use it.

If the existence check really doesn't use the index, what's a good code alternative to this check?

I did this and it's working great but I wonder if there's a better alternative. I don't really like doing the SET ROWCOUNT 1 and then SET ROWCOUNT 0 thing. SELECT TOP 1 won't work on Sybase, :-(.

SET ROWCOUNT 1
SELECT @.cnt = (SELECT 1 FROM dbo.t1 (index ix01)
WHERE f1 = @.p1
)
SET ROWCOUNT 0

Appreciate your help.

Do you have the code in a SP? I don't know if Sybase does parameter sniffing or not. Since the search argument is parameterized, the optimizer has to guess the value of @.p1 and use it for generating a plan. That will explain why it picks a table scan. And index hints will not help in this case. You could rewrite the code like below:

if (select min(1) from dbo.t1 where f1 = @.p1) is null

begin

select @.errno = @.errno|1

end

If Sybase has something like OPTIMIZE FOR clause then you can provide hint about the value of @.p1.

|||

Hi, thank you for the reply.

Yes, the code is in an sp. Not sure why the parameter needs to be evaluated for the plan to be generated correctly. I would think that it only needs the table and columns that are being queried. ?

I implemented your suggestion but it still didn't use the index. Once I added a hint, it did use the index.

I'd like to find a solution that doesn't have any aggregates in it, ie min(1). I'm looking for top performance; this query is executed a lot. I'll keep digging.

Thank you,

Etienne

|||

The optimizer analyzes the predicates and search arguments to decide which indexes to use. Indexes have statistics and if a particular search argument value is not known at the time of compilation then a guess will be made. For example, consider following:

Index on table t, column i

Statistics in the index will contain information on how the values of column "i" are distributed in the table

Given query like: "select i from t where i > 1"

Query optimizer can use the statistics to infer how many values of i are greater than 1. Based on this information, a plan to either use index seek or index scan or table scan will be used. The choice depends on the cardinality of the returned result set based on the search arguments.

Now, for the same query if you do: "select i from t where i > @.i"

If query optimizer cannot sniff the value of the parameter/variable @.i then it has to make a guess on what the value of @.i could be at run-time. This guess is often conservative because based on the value of @.i the query can either return zero rows, one row, many rows or entire table.

Both approaches has their pros and cons. Even with parameter sniffing the plan choice depends on the supplied value and that plan will not be great for other potential search arguments. So as you can see it is not just the tables, columns and indexes that matter.

Anyway, it looks like you have to ask this question in a Sybase forum. I don't know about the details of the query optimizer in Sybase SQL Server. You can try various rewrites but I think the crux of the problem is that the value of the search argument is guessed by the optimizer and then the query plan is generated.

IF NOT EXISTS

Hello,

I am trying to create a table if one with the same name does not exists. My code is:

Dim connectionStringAsString ="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\PensionDistrict4.mdf;Integrated Security=True;User Instance=True"Dim sqlConnectionAs SqlConnection =New SqlConnection(connectionString)Dim newTableAsString ="CREATE TABLE [" + titleString +"Comments" +"] (ID int NOT NULL PRIMARY KEY IDENTITY, Title varchar(100) NOT NULL, Name varchar(100) NOT NULL, Comment varchar(MAX) NOT NULL, Date datetime NOT NULL)"

sqlConnection.Open()

Dim sqlExistsAsString ="IF EXISTS (SELECT * FROM PensionDistrict4 WHERE name = '" + titleString +"Comments" +"')"Dim sqlCommandAsNew SqlCommand(newTable, sqlConnection)If sqlExists =TrueThen

sqlCommand.Cancel()

Else

sqlCommand.ExecuteNonQuery()

sqlConnection.Close()

EndIf

I keep getting a "Input String was incorrect format" for sqlExists? I am new to Transact-SQL statements, any help would be appreciated.

Thanks Matt

your sql Exists is just a string. so your code ofIf sqlExists =TrueThen doesnt make any sense. You need to execute it to find out if a table with the name exists. Alternatively its better to query sysobjects to find out if the table exists.

SELECT * FROM ssyobjects WHERE [Name] = '...' AND xtype = 'u'.

I'd recommend using a stored proc for this, so you can query the sysobjects to see if the table already exists and if it does not then create it else either drop and recreate ot exit appropriately.

If not cursors then what

I've noted quite a bit of articles about not using cursors in SQL Server.
That's OK with me. What then do you use to iterate through large sets of
records? Could I have an example or two and maybe a link to a location that
give a bit more info.
Thank you
PeteYou don't iterate through sets of rows (except maybe in a client
application). 99.99% of the time this is not necessary and set-based SELECT
/ UPDATE / INSERT / DELETE statements can do the job much more efficiently.
Many examples of cursor code are written by programmers who aren't familiar
with SQL and are more comfortable with row-by-row sequential processing than
with set-based SELECT statements. Familiarise yourself with the SQL concepts
of joins, aggregates, subqueries, etc and you'll soon find solutions to your
requirements which avoid iterative processing altogether.
There are a few situations where a cursor might offer a more efficient
solution than a SELECT statement. But in general, if you think you need a
cursor, think again. If you're sure you need a cursor, post a description of
your requirements to this group and see if someone can suggest an
alternative.
--
David Portas
--
Please reply only to the newsgroup
--|||> What then do you use to iterate through large sets of
> records?
The problem is not finding a way to iterate. The goal is to find a way to
*not* have to iterate. If you can solve the problem using a set, instead of
one row at a time. There is no "code sample" out there that will show you
how to eliminate a cursor and use a set-based query instead; it is very
situation-dependent.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||Set-Based' Could I see an example of that?
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:sIedndpffMQj11ai4p2dnA@.giganews.com...
> You don't iterate through sets of rows (except maybe in a client
> application). 99.99% of the time this is not necessary and set-based
SELECT
> / UPDATE / INSERT / DELETE statements can do the job much more
efficiently.
> Many examples of cursor code are written by programmers who aren't
familiar
> with SQL and are more comfortable with row-by-row sequential processing
than
> with set-based SELECT statements. Familiarise yourself with the SQL
concepts
> of joins, aggregates, subqueries, etc and you'll soon find solutions to
your
> requirements which avoid iterative processing altogether.
> There are a few situations where a cursor might offer a more efficient
> solution than a SELECT statement. But in general, if you think you need a
> cursor, think again. If you're sure you need a cursor, post a description
of
> your requirements to this group and see if someone can suggest an
> alternative.
> --
> David Portas
> --
> Please reply only to the newsgroup
> --
>|||> Set-Based' Could I see an example of that?
Show an example (DDL, sample data and desired results) of what you are doing
that "requires" a cursor. And hopefully someone will be able to show you
how to make it set-based instead.
See http://www.aspfaq.com/5006
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||This is a multi-part message in MIME format.
--=_NextPart_000_00DB_01C3B7ED.7FE0FFD0
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: 7bit
A set-based query is one that acts on all of the desired rows as a set. For
example, if you wanted a list of all customers who ordered ProductID = 7,
the following would give you that list:
select
c.*
from
Customers as c
where exists
(
select
*
from
Orders as o
join [Order Details] as od on od.OrderID = o.OrderID
where
od.ProductID = 7
and c.CustomerID = o.CustomerID
)
Here, you didn't set up a cursor on the Customers table and then picked
through the Orders and Order Details tables. You just ran the query in one
step.
HTH
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Peter Shankey" <shankeyp@.NOSPAM.comcast.net> wrote in message
news:h56dnbqeeY6o01aiRVn-sw@.comcast.com...
Set-Based' Could I see an example of that?
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:sIedndpffMQj11ai4p2dnA@.giganews.com...
> You don't iterate through sets of rows (except maybe in a client
> application). 99.99% of the time this is not necessary and set-based
SELECT
> / UPDATE / INSERT / DELETE statements can do the job much more
efficiently.
> Many examples of cursor code are written by programmers who aren't
familiar
> with SQL and are more comfortable with row-by-row sequential processing
than
> with set-based SELECT statements. Familiarise yourself with the SQL
concepts
> of joins, aggregates, subqueries, etc and you'll soon find solutions to
your
> requirements which avoid iterative processing altogether.
> There are a few situations where a cursor might offer a more efficient
> solution than a SELECT statement. But in general, if you think you need a
> cursor, think again. If you're sure you need a cursor, post a description
of
> your requirements to this group and see if someone can suggest an
> alternative.
> --
> David Portas
> --
> Please reply only to the newsgroup
> --
>
--=_NextPart_000_00DB_01C3B7ED.7FE0FFD0
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

A set-based query is one that acts on =all of the desired rows as a set. For example, if you wanted a list of all =customers who ordered ProductID =3D 7, the following would give you that =list:
select
=c.*
from
Customers as =c
where exists
(
=select
= *
=from
= Orders as =o
join [Order =Details] as od on od.OrderID =3D o.OrderID
=where
= od.ProductID =3D 7
and c.CustomerID =3D o.CustomerID
)
Here, you didn't set up a cursor on =the Customers table and then picked through the Orders and Order Details tables. =You just ran the query in one step.
HTH
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Peter Shankey" wrote in message news:h56dnbqeeY6o01aiRVn=-sw@.comcast.com...Set-Based' Could I see an example of that?"David Portas" wrote in messagenews:sIedndpffMQj11ai4p=2dnA@.giganews.com...> You don't iterate through sets of rows (except maybe in a client> =application). 99.99% of the time this is not necessary and set-basedSELECT> / UPDATE / INSERT / DELETE statements can do =the job much moreefficiently.>> Many examples of cursor code =are written by programmers who aren'tfamiliar> with SQL and are =more comfortable with row-by-row sequential processingthan> with =set-based SELECT statements. Familiarise yourself with the SQLconcepts> =of joins, aggregates, subqueries, etc and you'll soon find solutions toyour> requirements which avoid iterative processing altogether.>> There are a few situations where a cursor =might offer a more efficient> solution than a SELECT statement. But in =general, if you think you need a> cursor, think again. If you're sure you =need a cursor, post a descriptionof> your requirements to this group =and see if someone can suggest an> alternative.>> =--> David Portas> --> Please reply only to the =newsgroup> -->>

--=_NextPart_000_00DB_01C3B7ED.7FE0FFD0--|||I usually describe this with a drop dead simple example:
Say you want to increase the price with 10% for each product that comes from Sweden. Say we have
1,000,000 rows in the table and 1,000 come from Sweden.
A procedural thinking would be to write a SELECT statement which returns all rows. Then for each
row, the client app check if the country is Sweden. If so, the client app executes an UPDATE
statement to increase the price for that product.
This result in reading 1,000,000 rows to the client and executing 1,000 UPDATE statements.
A set based thinking is to execute one UPDATE statement:
UPDATE products
SET price = price * 1.1
WHERE country = 'Sweden'
Here we read 0 rows and execute 1 UPDATE statement.
I know, this is an extremely simplified example, but it serves to explain the differences in
approaching the problem. David posted a good, slightly more complex, example.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Peter Shankey" <shankeyp@.NOSPAM.comcast.net> wrote in message
news:h56dnbqeeY6o01aiRVn-sw@.comcast.com...
> Set-Based' Could I see an example of that?
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:sIedndpffMQj11ai4p2dnA@.giganews.com...
> > You don't iterate through sets of rows (except maybe in a client
> > application). 99.99% of the time this is not necessary and set-based
> SELECT
> > / UPDATE / INSERT / DELETE statements can do the job much more
> efficiently.
> >
> > Many examples of cursor code are written by programmers who aren't
> familiar
> > with SQL and are more comfortable with row-by-row sequential processing
> than
> > with set-based SELECT statements. Familiarise yourself with the SQL
> concepts
> > of joins, aggregates, subqueries, etc and you'll soon find solutions to
> your
> > requirements which avoid iterative processing altogether.
> >
> > There are a few situations where a cursor might offer a more efficient
> > solution than a SELECT statement. But in general, if you think you need a
> > cursor, think again. If you're sure you need a cursor, post a description
> of
> > your requirements to this group and see if someone can suggest an
> > alternative.
> >
> > --
> > David Portas
> > --
> > Please reply only to the newsgroup
> > --
> >
> >
>|||Oh, that's not a big deal.
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:OJ72$yBuDHA.2712@.tk2msftngp13.phx.gbl...
> I usually describe this with a drop dead simple example:
> Say you want to increase the price with 10% for each product that comes
from Sweden. Say we have
> 1,000,000 rows in the table and 1,000 come from Sweden.
> A procedural thinking would be to write a SELECT statement which returns
all rows. Then for each
> row, the client app check if the country is Sweden. If so, the client app
executes an UPDATE
> statement to increase the price for that product.
> This result in reading 1,000,000 rows to the client and executing 1,000
UPDATE statements.
> A set based thinking is to execute one UPDATE statement:
> UPDATE products
> SET price = price * 1.1
> WHERE country = 'Sweden'
> Here we read 0 rows and execute 1 UPDATE statement.
> I know, this is an extremely simplified example, but it serves to explain
the differences in
> approaching the problem. David posted a good, slightly more complex,
example.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Peter Shankey" <shankeyp@.NOSPAM.comcast.net> wrote in message
> news:h56dnbqeeY6o01aiRVn-sw@.comcast.com...
> > Set-Based' Could I see an example of that?
> >
> > "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> > news:sIedndpffMQj11ai4p2dnA@.giganews.com...
> > > You don't iterate through sets of rows (except maybe in a client
> > > application). 99.99% of the time this is not necessary and set-based
> > SELECT
> > > / UPDATE / INSERT / DELETE statements can do the job much more
> > efficiently.
> > >
> > > Many examples of cursor code are written by programmers who aren't
> > familiar
> > > with SQL and are more comfortable with row-by-row sequential
processing
> > than
> > > with set-based SELECT statements. Familiarise yourself with the SQL
> > concepts
> > > of joins, aggregates, subqueries, etc and you'll soon find solutions
to
> > your
> > > requirements which avoid iterative processing altogether.
> > >
> > > There are a few situations where a cursor might offer a more efficient
> > > solution than a SELECT statement. But in general, if you think you
need a
> > > cursor, think again. If you're sure you need a cursor, post a
description
> > of
> > > your requirements to this group and see if someone can suggest an
> > > alternative.
> > >
> > > --
> > > David Portas
> > > --
> > > Please reply only to the newsgroup
> > > --
> > >
> > >
> >
> >
>|||> Oh, that's not a big deal.
I know. But the purpose of the example is that each example is dependent on what you want to do. As
already mentioned, if you have a specific case, you can always post your details and see if anyone
can come up with a proposal for a set-based solution.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Peter Shankey" <shankeyp@.NOSPAM.comcast.net> wrote in message
news:utudnQyQnL9WxlaiRVn-tw@.comcast.com...
> Oh, that's not a big deal.
> "Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> wrote in message news:OJ72$yBuDHA.2712@.tk2msftngp13.phx.gbl...
> > I usually describe this with a drop dead simple example:
> >
> > Say you want to increase the price with 10% for each product that comes
> from Sweden. Say we have
> > 1,000,000 rows in the table and 1,000 come from Sweden.
> >
> > A procedural thinking would be to write a SELECT statement which returns
> all rows. Then for each
> > row, the client app check if the country is Sweden. If so, the client app
> executes an UPDATE
> > statement to increase the price for that product.
> > This result in reading 1,000,000 rows to the client and executing 1,000
> UPDATE statements.
> >
> > A set based thinking is to execute one UPDATE statement:
> > UPDATE products
> > SET price = price * 1.1
> > WHERE country = 'Sweden'
> > Here we read 0 rows and execute 1 UPDATE statement.
> >
> > I know, this is an extremely simplified example, but it serves to explain
> the differences in
> > approaching the problem. David posted a good, slightly more complex,
> example.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at:
> http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> > "Peter Shankey" <shankeyp@.NOSPAM.comcast.net> wrote in message
> > news:h56dnbqeeY6o01aiRVn-sw@.comcast.com...
> > > Set-Based' Could I see an example of that?
> > >
> > > "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> > > news:sIedndpffMQj11ai4p2dnA@.giganews.com...
> > > > You don't iterate through sets of rows (except maybe in a client
> > > > application). 99.99% of the time this is not necessary and set-based
> > > SELECT
> > > > / UPDATE / INSERT / DELETE statements can do the job much more
> > > efficiently.
> > > >
> > > > Many examples of cursor code are written by programmers who aren't
> > > familiar
> > > > with SQL and are more comfortable with row-by-row sequential
> processing
> > > than
> > > > with set-based SELECT statements. Familiarise yourself with the SQL
> > > concepts
> > > > of joins, aggregates, subqueries, etc and you'll soon find solutions
> to
> > > your
> > > > requirements which avoid iterative processing altogether.
> > > >
> > > > There are a few situations where a cursor might offer a more efficient
> > > > solution than a SELECT statement. But in general, if you think you
> need a
> > > > cursor, think again. If you're sure you need a cursor, post a
> description
> > > of
> > > > your requirements to this group and see if someone can suggest an
> > > > alternative.
> > > >
> > > > --
> > > > David Portas
> > > > --
> > > > Please reply only to the newsgroup
> > > > --
> > > >
> > > >
> > >
> > >
> >
> >
>

If not CURSORS ?

I have a requirement to check a value which MUST be unique forever even if it is removed and readded.I have created a seperate table to maintain this value.

Without using a cursor how would I be able to append a duplicate base value (i.e. smith.j@.here.now) with the next sequential value (i.e. smith.j02@.here.now)

Any takers?

Oh ya, These values are not manually entered but populated through a DTS script. The existing values are repopulated from historic tables and new entries are added automatically. Initially the values would be populated without a number but a number needs to be generated on duplicates.Concatenate the datetime(getdate()) down to 1/1000 second. I am sure it will be unique. That's most of the spam mailers do when they create a fake ID to get around your blocking.|||Better yet, concatenate newid(). That will guarantee you a unique value all the time.|||Originally posted by joejcheng
Better yet, concatenate newid(). That will guarantee you a unique value all the time.

It also has to be sequential, not just unique|||You can use substring and max functions to achieve the same sequentially.|||You can't do this with sequential values if you insist on the stipulation that a record can be removed from the database and readded at another time. Even using a cursor to find out that smith.j02@.here.now, smith.j03@.here.now, and smith.j04@.here.now already exist, there is simply no way to know whether smith.j05@.here.now was not previously created and deleted. You have to store the used values permanently somewhere.|||...if you create a table with two columns:

EMailPrefix varchar(50),
EMailIncrement int

...to store both parts of the e-mail address, it is a simple matter to

select EMailPrefix + cast(Max(EMailIncrement) + 1 as NewEMail from UsedEmails where EMailPrefix = @.NewSubscriber group by EMailPrefix

...to get a new unused E-mail variation. Not sure if the syntax above is correct, but you get the picture...|||Unfortunately it is possible that more than one new entry can be made in the same import. (i.e. smith.j exists and two more smith.j are imported).

The list of historic values are being saved in a seperate table as described without the "EMailIncrement int" field. I had considered your exact solution but did not know how to increment 2 newly added values with different increments.

Originally posted by blindman
...if you create a table with two columns:

EMailPrefix varchar(50),
EMailIncrement int

...to store both parts of the e-mail address, it is a simple matter to

select EMailPrefix + cast(Max(EMailIncrement) + 1 as NewEMail from UsedEmails where EMailPrefix = @.NewSubscriber group by EMailPrefix

...to get a new unused E-mail variation. Not sure if the syntax above is correct, but you get the picture...

Unfortunate|||Use a cursor in combination with the table of historical values.

If my data looks like this how do I one-off update it?

First_Name Last_Name Contact_Name
---- --- ----
NULL NULL YVES MERCIER
NULL NULL YUN CHUNG
NULL NULL YUKITO YAMASAKI
NULL NULL YUICHI MOCHIZUKI
NULL NULL YUGUNDERA BHIDE

My goal is to split the full contact name into first and last as well. Because that is what is in the database and I want to keep it consistent for the few thousand records missing it.

Do I update this using a stored Procedure?

How would it be formed?

CREATE PROCEDURE dbo.ap_Insert_FirstLastName_WhereNull

SELECT *
FROM Booth_Visitors_Data
WHERE First_Name IS NULL And Last_Name Is NULL And Contact_Name IS NOT NULL
ORDER BY Contact_Name Desc

Update Booth_Visitors_Data Set

Or is this impossible with a stored procedure?

SHould I wirte a .aspx page to do it?

Or can I do it all in code-behind??

ProtectedSub btnUpdate_Click(ByVal senderAsObject,ByVal eAs System.EventArgs)Handles btnUpdate.ClickDim conAsNew SqlClient.SqlConnection

con.ConnectionString ="Data Source=10.10.10.10;Initial Catalog=Leads;Persist Security Info=True;User ID=xxxxx;Password=xxxxx"

Dim myCommandAsNew SqlClient.SqlCommand

myCommand.CommandText ="SELECT * FROM(Booth_Visitors_Data) WHERE(First_Name Is NULL And Last_Name Is NULL And Contact_Name Is Not NULL) ORDER BY Contact_Name Desc"

myCommand.BeginExecuteReader()

myCommand.Connection = con

con.Open()

con.Close()

EndSub

If I can do it in code-behind, how does it look in modern .net code as opposed to the old classic asp way?

[Edited by Dinakar Nethi]

Masked userid/pwd in connection string.

;

Does the data in your contact_Name column have only 2 words - for first name and last name or could there be any middle names, Jr, Sr or I, II, III etc too?

|||

Here's a sample:

Declare@.Namevarchar(100)

Set@.Name='YUKITO YAMASAKI'

Select FirstName=substring(@.Name ,1,charindex(' ',@.Name) )

,LastName=substring(@.Name,charindex(' ',@.Name),len(@.Name)-charindex(' ',@.Name)+1)

|||

Well there *Could be* 3, so I was thinking we could go to the far right and go back to the first space and call that the last name.

YOO JI YE
For example...

SO the first name would be Yoo JI and the last name Ye

But I guess that would not work with all the sr. jr. possiblities.

So can this all be done in stored procedures?

Can Stored Procedures loop through records?

|||

>>So can this all be done in stored procedures?

Yes.

>> Can Stored Procedures loop through records?

Ofcourse.

The bigger issue here is to identify the pattern so you can split the full name into its constituent names. The sample I provided earlier works only if its 2 words. Anything more will be tricky.

Check if this post helps:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=44123

|||

It was fun coming up with this. This will work for 3 words but not for 2 words.

Declare @.Name varchar(100)

Set @.Name = 'YOO JI YE'

Select FirstName = substring(@.Name ,1, charIndex(' ', @.Name, charIndex(' ', @.Name)) - 1 + charIndex(' ', @.Name)),
LastName = SubString(@.Name, charIndex(' ', @.Name) - 1 + charIndex(' ', @.Name),len(@.name) - charIndex(' ', @.Name) - 1 + charIndex(' ', @.Name))

My suggestion would be to follow the link ndinakar provided for you.

|||

I worked with the link ndinakar provided for you abit and came up with a script for you that will work with both 2 part and 3 part full names.

If you pass in 'YOOJI YE' then you get back as a first name of 'YOOJI' and lastname of 'YE'. If you pass in 'YOO JI YE' then you get back a first name of 'YOO JI' with a last name of 'YE'

Declare @.Name varchar(100)Set @.Name ='YOOJI YE'selectcase When parsename(replace(@.name,' ','.'), 3) IS NULL Then parsename(replace(@.name,' ','.'), 2) Else parsename(replace(@.name,' ','.'), 3) +' ' + parsename(replace(@.name,' ','.'), 2)End As FirstName,
parsename(replace(@.name,' ','.'), 1) As LastName

sql

If my cube don't have a measure how do I design cube ?

suggest I will try to design customer complaint system

to keep information about customer's complaint it will store ex. department , date , complainant , complaint message

I can't figure out which column will be measure

Anyone can guide me about cube that don't have a measure

Thanks for kindness

You can create a "count" measure which simply counts fact table rows, so no particular fact table column is selected for such a measure, For example, if you select "Count of rows" for Usage in the dialog box below, and select the Source table, then Source column selection will be disabled.

http://msdn2.microsoft.com/en-us/library/ms187698.aspx

SQL Server 2005 Books Online

New Measure Dialog Box (SSAS)

...

Usage

Select the aggregation function to be used by the new measure.

Source table

Select the table from which the new measure is to be created.

Source column

Select the column in the table selected in Source table on which the new measure is to be based.

If my ASP.Net application crashes just after HOLDLOCK is issued?

Will the database get locked if my ASP.Net application that is calling a stored procedure in which a HOLDLOCK for table1 is issued to SQL Server, suddenly crashes just after the stored procedure is called?

Holdlocks works on tables, pages, or rows it won't lock an entire database. If the commit and/or rollback is in the stored procedure, or contained in the batch sent to the SQL Server, then no. The batch and/or stored procedure will run to completion.

If you do something like issue this to the SQL Server: BEGIN TRANSACTION (Or start a transaction using the transaction object), then issue a SELECT ... (WITH HOLDLOCK) then crash before your ASP.NET application rollsback or commits the transaction, and the database server is remote, then yes, it'll be locked until the SQL Server realizes the connection is dead, and I'm not sure how long that would take. If it's a local SQL Server, then it realizes it immediate, and rollsback the transaction.

|||So, it seems that thesafestpractice as far as preventing SQL Server being held up in above scenario, is to provide rollbacks/commits inside stored proecedures rather than in ASP.Net code through ADO.Net.Is that correct?|||

Is it safest? Yes.

However, like I said, I haven't tested myself to see how long it will take SQL Server to detect a dead connection. It could be seconds, it could be hours. And it only really makes a difference if you are running in a web-farm environment, or you have other applications (that don't also run on the web server machine) that need access to the table data in a quick manner -- AND you can't tolerate the database to be down should a machine totally crash (Which is pretty darn rare). I would suggest that if you are deploying to such an environment you test this out yourself.

If MSSQL says successfully backed up... should be fine.... ?

I have created the database maintenance plan for SQL Server for full backup,
differential backup and transactional backup and scheduled. If I see the job
status as completed successfully and if I can see the physically the file
being created, am I on the safe side of thinking that I have the recoverable
backup. I normally check the restore activity once in 6 months. Do I have to
check any thing else to make sure my back up is successful? Does any one has
any experience of not being able to restore the SQL Server instances from
the backup copies.
I am making sure that I am perfect in terms of backup and restore
activities.
SQL2K.
Thank you,
AllenAllenHubatka (AllenHubtka_67@.hotmail.com) writes:
> I have created the database maintenance plan for SQL Server for full
> backup, differential backup and transactional backup and scheduled. If I
> see the job status as completed successfully and if I can see the
> physically the file being created, am I on the safe side of thinking
> that I have the recoverable backup. I normally check the restore
> activity once in 6 months. Do I have to check any thing else to make
> sure my back up is successful? Does any one has any experience of not
> being able to restore the SQL Server instances from the backup copies.
There is really only one way to check if a backup is good, and that
is to restore it and run DBCC CHECKDB on it. Whether you always do this,
or only at random occassions depends on how paranoid you are. Personally,
I'd say twice a year is a tad seldom.
Not that backups fail that often, but then again maybe you have some
creeping corruption. I have experienced that DBCC on a restored file
revealed errors that did not exhibit in the original database. This
was in SQL 6.0, so this may not happen today. Then again, it's all a
matter of how paranoid you are...
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

If Microsoft planning to provide MSDE version of SQL Server 2005 ?

If the answer is "no", well, that's sad.
If the answer is "yes", then is there a list of 2005 features that will and
will not be available in MSDE 2005 ?
Thank you.
hi,
Marek wrote:
> If the answer is "no", well, that's sad.
> If the answer is "yes", then is there a list of 2005 features that
> will and will not be available in MSDE 2005 ?
> Thank you.
http://www.microsoft.com/sql/express/default.mspx
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

If Isnull Question

I have a SP I am trying to set a value for a variable in. The default value
for the variable is Null.
If there is not parameter passed I want to do an If Isnull statement to set
the variable in the SP, except I keep recveiving an incorrect Syntax error.
If any one can suggest a fix I would greatly appreciate it.
Here is my statement
If IsNull(Convert(VarChar(50), @.Client), Set @.Client in ('HUD601Solomon',
'Citi Special Servicing', 'HUD601SalomonDeal3'))
Thanks
-ChrisYou should look up the IF statement in Books Online, also look at how
variables should be checked for null values.

> If IsNull(Convert(VarChar(50), @.Client), Set @.Client in ('HUD601Solomon',
> 'Citi Special Servicing', 'HUD601SalomonDeal3'))
Is this a part of a query? Then you should also look up the CASE expression
in Books Online.
After you're done with BOL, check out this recent thread:
http://msdn.microsoft.com/newsgroup...5f-b8940be08178
I'm guessing you want something like that.
ML
http://milambda.blogspot.com/|||you want "if @.Client is null. "
isnull is a function which will replace a null occurence with a value
"@.Client is null" is using the is keyword, which will do an equality
check(ish - null != null).
beware not to use if @.Client = null as this will not work.sql

Friday, March 30, 2012

If numeric query

If numeric query

If Nothing Selected Into Variable What Is The Value Of Variable?

If Not Exists?

If Not Exists?

If Not Exists?

IF NOT EXISTS problem

IF NOT EXISTS in Oracle?

IF NOT EXISTS (... - EXISTS TABLE : nested iteration. Table Scan.Forward scan.

IF NOT EXISTS

If not cursors then what

If not CURSORS ?

If my data looks like this how do I one-off update it?

If my cube don't have a measure how do I design cube ?

If my ASP.Net application crashes just after HOLDLOCK is issued?

If MSSQL says successfully backed up... should be fine.... ?

If Microsoft planning to provide MSDE version of SQL Server 2005 ?

If Isnull Question

Identity column

Blog Archive

About Me