about "trailing Space" in the record

Hi, there;
We know that: Both select * from mytable where column1="data" and select * from mytable where column1="data " give us same result. (Please note the spaces in second query) This means the trailing space doesn't affect the query result.

How can I make SQL to return different result?

Thanks.

That is the result of the ANSI standard.

One way that you could use to determine if the values, including trailing blanks, are different, is to use the datalength() functions. Something like this:

Code Snippet

DECLARE
@.MyVar1 varchar(20),
@.MyVar2 varchar(20)

SELECT
@.MyVar1 = 'data',
@.MyVar2 = 'data '

IF datalength( @.MyVar1 ) = datalength( @.MyVar2 )
PRINT 'Values are the same'
ELSE
PRINT 'Values are NOT the same'

|||Thanks Arnie.

My case is a bit different. I need to run a sql statement (DELETE FROM MYTABLE WHERE key='KEYVALUE') from an application. The data in the table is imported from raining data. Some data has trailing space. Because of the ANSI standard, 'KEYVALUE ' will be deleted if I run that statement which is not I want.

I just wonder if there is a switch to we can turn it on/off to make it different.|||

Perhaps this will work for you:

WHERE ( KeyValue = 'KeyValue'

AND datalength( KeyValue ) = datalength( 'KeyValue' )
)

|||Thanks. That will do.

Ability to update multiple tables simultaneously via stored proc

Anyone have any ideas on how to use a stored procedure to update multiple tables simultaneously? I am updating a parent record and zero or more child records. I would like to make one stored procedure call if possible to do so. Any ideas on doing this would be appreciated. Thanks!

EverettYou can update more than one table within a stored procedure, just make sure to use BEGIN TRAN/COMMIT TRAN/ROLLBACK TRAN

Example:

CREATE PROCEDURE sp_ModifyMasterDetail
(
.
.
.
@.msg varchar(255) output
)
AS
SET NOCOUNT ON

DECLARE @.error int
, @.tfTran tinyint

--
-- Start Transaction
--
IF (@.@.TRANCOUNT = 0) BEGIN
SELECT @.tfTran = 1
BEGIN TRAN
END
ELSE
SELECT @.tfTran = 0

UPDATE tblMaster
.
WHERE ID = @.ID

SELECT @.error = @.@.error

IF (@.error <> 0)
GOTO Error_Exit

UPDATE tblDetail
.
WHERE ID = @.ID
AND SubID = @.SubID

SELECT @.error = @.@.error

IF (@.error <> 0)
GOTO Error_Exit

--
-- Check to see if an error occured during processing. If so then
-- ROLLBACK else COMMIT transactions
--
Error_Exit:

IF (@.error <> 0) BEGIN
IF (@.tfTran = 1)
ROLLBACK TRAN

SELECT @.msg = 'ERROR: Transaction failed with error ' + CONVERT(varchar(20),@.error),
END
ELSE BEGIN
IF (@.tfTran = 1)
COMMIT TRAN

SELECT @.msg = 'Transaction successful'
END

RETURN @.error
GO|||begin tran
update parent set ...
if @.@.error <> 0
begin
raiserror('failed',16,-1)
rollback tran
return
end
update child set ...
if @.@.error <> 0
begin
raiserror('failed',16,-1)
rollback tran
return
end
commit tran

Or you could put a trigger on the parent (or child) table or on a view of the combination - depends on the updates you want to do.|||Thanks guys! Either one of these will do the trick, except that I'm not sure how to get the data into the stored procedure! I guess I could munge it into varchar(8000), but I'm not sure that it would always be long enough. Any way of passing either an array or a recordset/cursor into a stored procedure?

Everett|||You can create a temp table on the spid, populate it then access it in the SP.

Call the SP repeated times with the values and the SP can populate a table keyed on spid.

Call the sp with comma delimitted strings with the values.

Have lots of parameters - up to the max you think you will need.

A tricky code problem!

I have a report that looks like the following:

record record record null null record record
supress supress supress null record1 null supress
supress supress supress record2 null null supress

This is the best way to describe my data, its 3 rows of records and I want to show record1 and record2 on the top row. I have the other data beneath supressed. I am sure this maybe be possible using SQL code or using a loop or something but I have no idea where to start and have been trawling the web for answers or some code samples but have found none to date as it is quite a specific thing. Its tricky I know but could someone help me please?Hi,
I want to make a page break after section3 in crystal report(ver. 8) after displaying 5 records. I'm using .Dsr report. I am using the following code.

'*******************************
Set Report = New CrystalReport1
Report.Database.AddADOCommand db, cmd
For i = 1 To rs.Recordcount

Set txtObj = Report.Section2.AddTextObject(l, 150, vHight)
txtObj.Width = 300
txtObj.HorAlignment = crLeftAlign
txtObj.Font.Bold = True

Set txtObj = Report.Section2.AddTextObject(rs!ITEM_DESC, 400, vHight)
txtObj.Width = 2000
txtObj.HorAlignment = crLeftAlign
txtObj.Font.Bold = True

Set txtObj = Report.Section2.AddTextObject(rs!narmst_sItemName, 2800, vHight)
txtObj.Width = 1200
txtObj.HorAlignment = crLeftAlign
txtObj.Font.Bold = True

Set txtObj = Report.Section2.AddTextObject(rs!dlnsp_dItemRate, 4200, vHight)
txtObj.Width = 1200
txtObj.HorAlignment = crLeftAlign
txtObj.Font.Bold = True

Set txtObj = Report.Section2.AddTextObject(rs!dlnsp_dItemQty, 5700, vHight)
txtObj.Width = 1200
txtObj.HorAlignment = crLeftAlign
txtObj.Font.Bold = True

Set txtObj = Report.Section2.AddTextObject(rs!dlnsp_dItemRate * rs!dlnsp_dItemQty, 7100, vHight)
txtObj.Width = 1200
txtObj.HorAlignment = crLeftAlign
txtObj.Font.Bold = True

Set txtObj = Report.Section2.AddTextObject(rs!dlnsp_dItemRate * rs!dlnsp_dItemQty, 8500, vHight)
txtObj.Width = 1200
txtObj.HorAlignment = crLeftAlign
txtObj.Font.Bold = True

If IsNull(rs!VAT_AMT) Then
m = 0
Else
m = Val(rs!VAT_AMT)
End If

Set txtObj = Report.Section2.AddTextObject(m, 9600, vHight)
txtObj.Width = 1200
txtObj.HorAlignment = crLeftAlign
txtObj.Font.Bold = True

Set txtObj = Report.Section2.AddTextObject(Val(rs!dlnsp_dItemRate * rs!dlnsp_dItemQty + m), 11200, vHight)
txtObj.Width = 1200
txtObj.HorAlignment = crLeftAlign
txtObj.Font.Bold = True

If i > 5 Then
Report.Section3.NewPageAfter = True
End If

rs.MoveNext
vHight = vHight + 250
l = l + 1
Next i
'**************************

But it is not working. Please Help me.

with regards,
Arindam

A SQL / STored procedure question.

I have following two tables.

Table1 (Columns: ID, SessionID, URL) Note: URL Column currently is empty. Table1 has one record per SessionID.

Table2 (Columns: ID, SessionID, fromURL) Note: Table 2 has multiple records per Session ID.

So in essence, for one SessionID in Table1 there are multiple records in Table2.

Also the value in fromURL column in Table2 is unique for a SessionID.

What I want to do:

1). Select Table2.fromURL where Table1.SessionID = Table2.SessionID with DISTINCT Table2.fromURL.(I will get lot of records from the above statement)

2). For each record in step 1,Insert value from Table2.fromURL into Table1.URL where Table2.SessionID = Table1.SessionID

Phew.. difficult to explain.. i am sure.. easy to implement.Any help would be appreciated. Regards.

UPDATE Table1 SET URL = fromURL FROM Table2 WHERE Table2.SessionID = Table1.SessionID

A Simple Insert statement in European version of SQL

I have a client that uses my utility program to insert a record into a
table. Once my program receives the values, creates an insert statement
with comma separated values. Ie:
Insert into T (a,b) values (1.578, 2)
I now understand that those numeric values could have ',' in place of
decimal point for European version. So, the above values would look like
1,578 and 2.
How does the insert statement would know ',' is not a separator in this
case ? Ie:
Insert into T (a,b) values (1,578, 2) --> resulting
into syntax error
Should I be using a different value separator character ?
TIA.
MacI am not the one producing the values with ',' in place of decimal points.
It is SQL Server of European version that returns the data I am collecting
with embedded comma. So, when I submit a query of "Select a from T"
( a is defined to be a real type number), it returns 2,476 instead of
2.476. So, my question is how do I take these returned value with
embedded comma and insert them back into say another field in a table ? I
do not have such SQL version in my site to see what is going on and how to
accomplish such inserts.
- Mac
""Bill Cheng [MSFT]"" <billchng@.online.microsoft.com> wrote in message
news:PyBEKO#aDHA.2108@.cpmsftngxa06.phx.gbl...
> Hi Mac,
> Please do not use comma as decimal separator. It will cause problems. Use
> period as decimal point.
> Character expressions being converted to an exact numeric data type must
> consist of digits, a decimal point, and an optional plus (+) or minus (-).
> Leading blanks are ignored. Comma separators (such as the thousands
> separator in 123,456.00) are not allowed in the string.
>
> Bill Cheng
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> --
> | From: "Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com>
> | Newsgroups: microsoft.public.sqlserver.server
> | Subject: A Simple Insert statement in European version of SQL
> | Date: Mon, 25 Aug 2003 16:49:15 -0700
> | Organization: Unisys - Roseville, MN
> | Lines: 20
> | Message-ID: <bie79r$1rmm$1@.si05.rsvl.unisys.com>
> | NNTP-Posting-Host: 192.59.171.175
> | X-Trace: si05.rsvl.unisys.com 1061855355 61142 192.59.171.175 (25 Aug
> 2003 23:49:15 GMT)
> | X-Complaints-To: news@.rsvl.unisys.com
> | NNTP-Posting-Date: 25 Aug 2003 23:49:15 GMT
> | X-Priority: 3
> | X-MSMail-Priority: Normal
> | X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
> | Path:
>
cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!news-out.cwix.com!newsfeed.cwix.co
>
m!feed2.news.rcn.net!rcn!news-out.visi.com!petbe.visi.com!ash.uu.net!bbnews1
> .unisys.com!trsvr.tr.unisys.com!si05!not-for-mail
> | Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:303093
> | X-Tomcat-NG: microsoft.public.sqlserver.server
> |
> | I have a client that uses my utility program to insert a record into a
> | table. Once my program receives the values, creates an insert
statement
> | with comma separated values. Ie:
> | Insert into T (a,b) values (1.578, 2)
> |
> | I now understand that those numeric values could have ',' in place of
> | decimal point for European version. So, the above values would look
> like
> | 1,578 and 2.
> |
> | How does the insert statement would know ',' is not a separator in this
> | case ? Ie:
> | Insert into T (a,b) values (1,578, 2) --> resulting
> | into syntax error
> |
> | Should I be using a different value separator character ?
> |
> | TIA.
> | Mac
> |
> |
> |
>|||Are you using Visual basic ?
VB does use the client-settings to format numbers, dates,...
You'll have to use a format function to convert to a propre string.
e.g. strsql = "insert into table1 (col1) values (" & Format(numcol,
"###0.00") & ")"
jobi
"Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
news:big51p$5vu$1@.si05.rsvl.unisys.com...
> I am not the one producing the values with ',' in place of decimal points.
> It is SQL Server of European version that returns the data I am collecting
> with embedded comma. So, when I submit a query of "Select a from T"
> ( a is defined to be a real type number), it returns 2,476 instead of
> 2.476. So, my question is how do I take these returned value with
> embedded comma and insert them back into say another field in a table ?
I
> do not have such SQL version in my site to see what is going on and how to
> accomplish such inserts.
> - Mac
>
> ""Bill Cheng [MSFT]"" <billchng@.online.microsoft.com> wrote in message
> news:PyBEKO#aDHA.2108@.cpmsftngxa06.phx.gbl...
> > Hi Mac,
> >
> > Please do not use comma as decimal separator. It will cause problems.
Use
> > period as decimal point.
> >
> > Character expressions being converted to an exact numeric data type must
> > consist of digits, a decimal point, and an optional plus (+) or minus
(-).
> > Leading blanks are ignored. Comma separators (such as the thousands
> > separator in 123,456.00) are not allowed in the string.
> >
> >
> >
> > Bill Cheng
> > Microsoft Online Partner Support
> >
> > Get Secure! - www.microsoft.com/security
> > This posting is provided "as is" with no warranties and confers no
rights.
> > --
> > | From: "Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com>
> > | Newsgroups: microsoft.public.sqlserver.server
> > | Subject: A Simple Insert statement in European version of SQL
> > | Date: Mon, 25 Aug 2003 16:49:15 -0700
> > | Organization: Unisys - Roseville, MN
> > | Lines: 20
> > | Message-ID: <bie79r$1rmm$1@.si05.rsvl.unisys.com>
> > | NNTP-Posting-Host: 192.59.171.175
> > | X-Trace: si05.rsvl.unisys.com 1061855355 61142 192.59.171.175 (25 Aug
> > 2003 23:49:15 GMT)
> > | X-Complaints-To: news@.rsvl.unisys.com
> > | NNTP-Posting-Date: 25 Aug 2003 23:49:15 GMT
> > | X-Priority: 3
> > | X-MSMail-Priority: Normal
> > | X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
> > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
> > | Path:
> >
>
cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!news-out.cwix.com!newsfeed.cwix.co
> >
>
m!feed2.news.rcn.net!rcn!news-out.visi.com!petbe.visi.com!ash.uu.net!bbnews1
> > .unisys.com!trsvr.tr.unisys.com!si05!not-for-mail
> > | Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:303093
> > | X-Tomcat-NG: microsoft.public.sqlserver.server
> > |
> > | I have a client that uses my utility program to insert a record into a
> > | table. Once my program receives the values, creates an insert
> statement
> > | with comma separated values. Ie:
> > | Insert into T (a,b) values (1.578, 2)
> > |
> > | I now understand that those numeric values could have ',' in place of
> > | decimal point for European version. So, the above values would look
> > like
> > | 1,578 and 2.
> > |
> > | How does the insert statement would know ',' is not a separator in
this
> > | case ? Ie:
> > | Insert into T (a,b) values (1,578, 2) -->
resulting
> > | into syntax error
> > |
> > | Should I be using a different value separator character ?
> > |
> > | TIA.
> > | Mac
> > |
> > |
> > |
> >
>|||As jobi point out: You have to differentiate between input and output. Just because a client tool
formats something that SQL Server outputs with a comma doesn't mean that SQL Server accepts that as
a valid input format.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"jobi" <jobi@.reply2.group> wrote in message news:bihkck$boq$1@.reader08.wxs.nl...
> Are you using Visual basic ?
> VB does use the client-settings to format numbers, dates,...
> You'll have to use a format function to convert to a propre string.
> e.g. strsql = "insert into table1 (col1) values (" & Format(numcol,
> "###0.00") & ")"
> jobi
> "Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
> news:big51p$5vu$1@.si05.rsvl.unisys.com...
> > I am not the one producing the values with ',' in place of decimal points.
> > It is SQL Server of European version that returns the data I am collecting
> > with embedded comma. So, when I submit a query of "Select a from T"
> > ( a is defined to be a real type number), it returns 2,476 instead of
> > 2.476. So, my question is how do I take these returned value with
> > embedded comma and insert them back into say another field in a table ?
> I
> > do not have such SQL version in my site to see what is going on and how to
> > accomplish such inserts.
> >
> > - Mac
> >
> >
> > ""Bill Cheng [MSFT]"" <billchng@.online.microsoft.com> wrote in message
> > news:PyBEKO#aDHA.2108@.cpmsftngxa06.phx.gbl...
> > > Hi Mac,
> > >
> > > Please do not use comma as decimal separator. It will cause problems.
> Use
> > > period as decimal point.
> > >
> > > Character expressions being converted to an exact numeric data type must
> > > consist of digits, a decimal point, and an optional plus (+) or minus
> (-).
> > > Leading blanks are ignored. Comma separators (such as the thousands
> > > separator in 123,456.00) are not allowed in the string.
> > >
> > >
> > >
> > > Bill Cheng
> > > Microsoft Online Partner Support
> > >
> > > Get Secure! - www.microsoft.com/security
> > > This posting is provided "as is" with no warranties and confers no
> rights.
> > > --
> > > | From: "Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com>
> > > | Newsgroups: microsoft.public.sqlserver.server
> > > | Subject: A Simple Insert statement in European version of SQL
> > > | Date: Mon, 25 Aug 2003 16:49:15 -0700
> > > | Organization: Unisys - Roseville, MN
> > > | Lines: 20
> > > | Message-ID: <bie79r$1rmm$1@.si05.rsvl.unisys.com>
> > > | NNTP-Posting-Host: 192.59.171.175
> > > | X-Trace: si05.rsvl.unisys.com 1061855355 61142 192.59.171.175 (25 Aug
> > > 2003 23:49:15 GMT)
> > > | X-Complaints-To: news@.rsvl.unisys.com
> > > | NNTP-Posting-Date: 25 Aug 2003 23:49:15 GMT
> > > | X-Priority: 3
> > > | X-MSMail-Priority: Normal
> > > | X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
> > > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
> > > | Path:
> > >
> >
> cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!news-out.cwix.com!newsfeed.cwix.co
> > >
> >
> m!feed2.news.rcn.net!rcn!news-out.visi.com!petbe.visi.com!ash.uu.net!bbnews1
> > > .unisys.com!trsvr.tr.unisys.com!si05!not-for-mail
> > > | Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:303093
> > > | X-Tomcat-NG: microsoft.public.sqlserver.server
> > > |
> > > | I have a client that uses my utility program to insert a record into a
> > > | table. Once my program receives the values, creates an insert
> > statement
> > > | with comma separated values. Ie:
> > > | Insert into T (a,b) values (1.578, 2)
> > > |
> > > | I now understand that those numeric values could have ',' in place of
> > > | decimal point for European version. So, the above values would look
> > > like
> > > | 1,578 and 2.
> > > |
> > > | How does the insert statement would know ',' is not a separator in
> this
> > > | case ? Ie:
> > > | Insert into T (a,b) values (1,578, 2) -->
> resulting
> > > | into syntax error
> > > |
> > > | Should I be using a different value separator character ?
> > > |
> > > | TIA.
> > > | Mac
> > > |
> > > |
> > > |
> > >
> >
> >
>|||Mac,
I've checked my vb-code again, and found this extra.
'Aparently the format still uses the client-setting for decimal point !!!
replace$(string, ",",".")
so you'll have to come up to this :
e.g. strsql = "insert into table1 (col1) values (" &
Replace$(Format(numcol, "###0.00"),",",".") & ")"
I guess you don't need the ###-part, in fact, you only need the format if
you want control of the format, else you can use the cstr-function.
Replace$(CStr(numcol), ",", ".")
jobi
"Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
news:bijdkf$2fsd$1@.si05.rsvl.unisys.com...
> Jobi,
> Yes. I am using VB. Also when I am collecting the data, I use Format
> function to make sure I only get 2 digits decimal point. The format
> sysntax I use is following:
> Format(numValue, "0.00").
> Is this not correct ? Do I need the ### in front of them ? More like
aVB
> question...
> By using the Format function I thought I am also forcing the decimal point
> to show up as decimal point despite the local setting of the computer.
This
> way then I can turn around and use the result in another insert statement
> without further formatting.
> Thanks for the input.
> Mac
> "jobi" <jobi@.reply2.group> wrote in message
> news:bihkck$boq$1@.reader08.wxs.nl...
> > Are you using Visual basic ?
> >
> > VB does use the client-settings to format numbers, dates,...
> > You'll have to use a format function to convert to a propre string.
> >
> > e.g. strsql = "insert into table1 (col1) values (" & Format(numcol,
> > "###0.00") & ")"
> >
> > jobi
> > "Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
> > news:big51p$5vu$1@.si05.rsvl.unisys.com...
> > > I am not the one producing the values with ',' in place of decimal
> points.
> > > It is SQL Server of European version that returns the data I am
> collecting
> > > with embedded comma. So, when I submit a query of "Select a from
T"
> > > ( a is defined to be a real type number), it returns 2,476 instead
of
> > > 2.476. So, my question is how do I take these returned value with
> > > embedded comma and insert them back into say another field in a table
?
> > I
> > > do not have such SQL version in my site to see what is going on and
how
> to
> > > accomplish such inserts.
> > >
> > > - Mac
> > >
> > >
> > > ""Bill Cheng [MSFT]"" <billchng@.online.microsoft.com> wrote in message
> > > news:PyBEKO#aDHA.2108@.cpmsftngxa06.phx.gbl...
> > > > Hi Mac,
> > > >
> > > > Please do not use comma as decimal separator. It will cause
problems.
> > Use
> > > > period as decimal point.
> > > >
> > > > Character expressions being converted to an exact numeric data type
> must
> > > > consist of digits, a decimal point, and an optional plus (+) or
minus
> > (-).
> > > > Leading blanks are ignored. Comma separators (such as the thousands
> > > > separator in 123,456.00) are not allowed in the string.
> > > >
> > > >
> > > >
> > > > Bill Cheng
> > > > Microsoft Online Partner Support
> > > >
> > > > Get Secure! - www.microsoft.com/security
> > > > This posting is provided "as is" with no warranties and confers no
> > rights.
> > > > --
> > > > | From: "Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com>
> > > > | Newsgroups: microsoft.public.sqlserver.server
> > > > | Subject: A Simple Insert statement in European version of SQL
> > > > | Date: Mon, 25 Aug 2003 16:49:15 -0700
> > > > | Organization: Unisys - Roseville, MN
> > > > | Lines: 20
> > > > | Message-ID: <bie79r$1rmm$1@.si05.rsvl.unisys.com>
> > > > | NNTP-Posting-Host: 192.59.171.175
> > > > | X-Trace: si05.rsvl.unisys.com 1061855355 61142 192.59.171.175 (25
> Aug
> > > > 2003 23:49:15 GMT)
> > > > | X-Complaints-To: news@.rsvl.unisys.com
> > > > | NNTP-Posting-Date: 25 Aug 2003 23:49:15 GMT
> > > > | X-Priority: 3
> > > > | X-MSMail-Priority: Normal
> > > > | X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
> > > > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
> > > > | Path:
> > > >
> > >
> >
>
cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!news-out.cwix.com!newsfeed.cwix.co
> > > >
> > >
> >
>
m!feed2.news.rcn.net!rcn!news-out.visi.com!petbe.visi.com!ash.uu.net!bbnews1
> > > > .unisys.com!trsvr.tr.unisys.com!si05!not-for-mail
> > > > | Xref: cpmsftngxa06.phx.gbl
microsoft.public.sqlserver.server:303093
> > > > | X-Tomcat-NG: microsoft.public.sqlserver.server
> > > > |
> > > > | I have a client that uses my utility program to insert a record
into
> a
> > > > | table. Once my program receives the values, creates an insert
> > > statement
> > > > | with comma separated values. Ie:
> > > > | Insert into T (a,b) values (1.578, 2)
> > > > |
> > > > | I now understand that those numeric values could have ',' in
place
> of
> > > > | decimal point for European version. So, the above values would
> look
> > > > like
> > > > | 1,578 and 2.
> > > > |
> > > > | How does the insert statement would know ',' is not a separator
in
> > this
> > > > | case ? Ie:
> > > > | Insert into T (a,b) values (1,578, 2) -->
> > resulting
> > > > | into syntax error
> > > > |
> > > > | Should I be using a different value separator character ?
> > > > |
> > > > | TIA.
> > > > | Mac
> > > > |
> > > > |
> > > > |
> > > >
> > >
> > >
> >
> >
>

a query using GROUP BY or?

Is there a simple way to do the following?
The database table has many records, each record has its own unique RecordID
(PK, int), some of the records can have one text field like an intenrifier
(SomeID) with the same value. A simplified schema is looking like this:
RecordID SomeID Action
1 134 2
2 123 2
3 1243 2
4 134 1
5 1ytr 2
6 1fgh 2
7 1243 1
8 hgf 2
9 b4rfg 2
I need to assign the value '1' or '2' to the Action field so that if we
order the whole list by the RecordID and then group it by the SomeID field,
the first record in each group (with the same SomeID field) should have
Action=2, all next entries inside each group should have Action=1. All
records without duplicates should have Action=2.
I can set Action=2 to all records, it's fast and easy. How can I assign '1'
to all appropriate (duplicate) records?
Thanks,
Just D.Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications.
CREATE TABLE Foobar
(foo_id INTEGER NOT NULL PRIMARY KEY,
grp_id CHAR(5) NOT NULL,
action_code INTEGER DEFAULT 2 NOT NULL
CHECK (action_code IN (1,2)));
Action=2, all next entries inside each group should have Action=1. All
records [sic] without duplicates should have Action=2. I can set
Action=2 to all records [sic], it's fast and easy. How can I assign '1'
to all appropriate (duplicate) records [sic] ? <<
Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files; there is no sequential access or
ordering in an RDBMS, so "first", "next" and "last" are totally
meaningless. A normalized table should not have redundant duplicates.
I am going to guess that this is what you want:
UPDATE Foobar
SET action_code
= CASE WHEN foo_id
< (SELECT MAX(foo_id)
FROM Foobar AS F1
WHERE F1.grp_id = Foobar.grp_id)
THEN 1 ELSE 2 END;|||Try this one:
update tbl set
Action =
case when RecordID = (select min(RecordID) from tbl as t where
t.SomeID = tb.SomeID)
then 2
else 1
end|||HI,
Excellent! That's a very good idea! Thanks!
Just D.
"Sergei Almazov" <almazik@.ukr.net> wrote in message
news:1127474970.840245.308070@.g44g2000cwa.googlegroups.com...
> Try this one:
> update tbl set
> Action =
> case when RecordID = (select min(RecordID) from tbl as t where
> t.SomeID = tb.SomeID)
> then 2
> else 1
> end
>

a problem in sqldatasource

How I can know my sqldatasourse found any record or not I dont want to use datalist or another data table .and how I can put each feild that sqldatasource found on a string object( ineed just one record that sqldatasource found.)
I need a code that on page load automaticly goto another page

In the data source's Selected handler ther is a param SqlDataSourceStatusEventArgs e, e.AffectedRows will expose how many rows are selected~

But since you don't bind it to a data present control, and you have only one row to select, why not useDataReader? Manipulating data isn't data source's job, but DataSet and DataReader are much better at that~

And to goto another page, the simplest way is Response.Redirect() or Server.Transfer()

a Null check for a single record

Hi,
I have a table that may contains Null filled columns.
I have a SP that do Update and insert.
How can i do a null check to avoid trying to update null fields?
let say Type_1 column is null, I need a condition that will do a quick check (single record is sufficient) to see that it has null value and to skip the update command:

CREATE PROCEDURE Lan_Insert_Data_Type
as
DECLARE @.Single_Rec int
Begin

-- ??? HERE comes the condition --

UPDATE Data_type
SET Product_Num = LanTable.ProductNum,
Data =LanTable.Type_1
FROM Data_Type p
JOIN LanTable
ON Lantable.ProductNum = p.Product_num where p.Data_Type =1

Thanks

Yossi--Try This

CASE When Type_1 IS NULL
Then
--Do nothing
SET @.Single_Rec = @.Single_Rec

ELSE

UPDATE Data_type
SET Product_Num = LanTable.ProductNum,
Data =LanTable.Type_1
FROM Data_Type p
JOIN LanTable
ON Lantable.ProductNum = p.Product_num where p.Data_Type =1

END|||Originally posted by eschapir
--Try This

CASE When Type_1 IS NULL
Then
--Do nothing
SET @.Single_Rec = @.Single_Rec

ELSE

UPDATE Data_type
SET Product_Num = LanTable.ProductNum,
Data =LanTable.Type_1
FROM Data_Type p
JOIN LanTable
ON Lantable.ProductNum = p.Product_num where p.Data_Type =1

END
Thaks for the reply
but its not working,
maybe i missed some thing|||/* something like this ? */

if not exists(
select *
FROM Data_Type p
JOIN LanTable
ON Lantable.ProductNum = p.Product_num
where p.Data_Type=1 and LanTable.Type_1 is null
)
UPDATE Data_type
SET Product_Num = LanTable.ProductNum,
Data =LanTable.Type_1
FROM Data_Type p
JOIN LanTable
ON Lantable.ProductNum = p.Product_num where p.Data_Type =1|||Originally posted by ispaleny
/* something like this ? */

if not exists(
select *
FROM Data_Type p
JOIN LanTable
ON Lantable.ProductNum = p.Product_num
where p.Data_Type=1 and LanTable.Type_1 is null
)
UPDATE Data_type
SET Product_Num = LanTable.ProductNum,
Data =LanTable.Type_1
FROM Data_Type p
JOIN LanTable
ON Lantable.ProductNum = p.Product_num where p.Data_Type =1

Works great...
Thanks mate

A little help on a proc

I'm fairly new to the stored procedure world, I can create one to return a r
ecord set, look at me go. However, I need to get a little more advanced. I n
eed to do the following in a proc:
Query a honken huge query and check if login is valid. If so, continue throu
gh proc and return the necessary data, if not return an invalid status.
Thanks a million!!
--
David Lozzi
Web Applications/Network Specialist
Delphi Technology Solutions, Inc.
dlozzi(remove-this)@.delphi-ts.comDavid
(untested)
CREATE PROC spSomething
@.login VARCHAR(20)
AS
IF EXISTS(SELECT name FROM master..syslogins WHERE name =@.login )
BEGIN
DO something
ELSE
PRINT 'A login does not exist'
END
GO
"David Lozzi" <dlozzi@.(remove-this)delphi-ts.com> wrote in message news:eShj
WtRDFHA.512@.TK2MSFTNGP15.phx.gbl...
I'm fairly new to the stored procedure world, I can create one to return a r
ecord set, look at me go. However, I need to get a little more advanced. I n
eed to do the following in a proc:
Query a honken huge query and check if login is valid. If so, continue throu
gh proc and return the necessary data, if not return an invalid status.
Thanks a million!!
--
David Lozzi
Web Applications/Network Specialist
Delphi Technology Solutions, Inc.
dlozzi(remove-this)@.delphi-ts.com|||This is great, but how do I now access that recordset if it exists?
--
David Lozzi
Web Applications/Network Specialist
Delphi Technology Solutions, Inc.
dlozzi(remove-this)@.delphi-ts.com
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:%23hYr9xRDFHA.1524@.TK2
MSFTNGP09.phx.gbl...
David
(untested)
CREATE PROC spSomething
@.login VARCHAR(20)
AS
IF EXISTS(SELECT name FROM master..syslogins WHERE name =@.login )
BEGIN
DO something
ELSE
PRINT 'A login does not exist'
END
GO
"David Lozzi" <dlozzi@.(remove-this)delphi-ts.com> wrote in message news:eShj
WtRDFHA.512@.TK2MSFTNGP15.phx.gbl...
I'm fairly new to the stored procedure world, I can create one to return a r
ecord set, look at me go. However, I need to get a little more advanced. I n
eed to do the following in a proc:
Query a honken huge query and check if login is valid. If so, continue throu
gh proc and return the necessary data, if not return an invalid status.
Thanks a million!!
--
David Lozzi
Web Applications/Network Specialist
Delphi Technology Solutions, Inc.
dlozzi(remove-this)@.delphi-ts.com|||This should get you started:
create proc foo as
@.loginname varchar(20),
@.password varchar(50)
as
--verify that the login is valid
if not exists (select * from sometable where somelogincolumn = @.loginname)
BEGIN
RETURN (1)
END
--verify that the password is valid
if not exists (select * from sometable where somelogincolumn = @.loginname
AND somepasswordcolumn = @.password)
BEGIN
RETURN (2)
END
select somecolumn
from sometable
where somelogincolumn = @.loginnamae
and somepasswordcolumn = @.password
RETURN (0)
GO
The return code will be 1 if the login does not exist, 2 if the password is
not correct. If the login exists and the password is correct the third
statement will be executed and the return code will be set to 0.
Keith
"David Lozzi" <dlozzi@.(remove-this)delphi-ts.com> wrote in message
news:eShjWtRDFHA.512@.TK2MSFTNGP15.phx.gbl...
I'm fairly new to the stored procedure world, I can create one to return a
record set, look at me go. However, I need to get a little more advanced. I
need to do the following in a proc:
Query a honken huge query and check if login is valid. If so, continue
through proc and return the necessary data, if not return an invalid status.
Thanks a million!!
David Lozzi
Web Applications/Network Specialist
Delphi Technology Solutions, Inc.
dlozzi(remove-this)@.delphi-ts.com|||So the best process is to rerun the query? Wouldnt that get extensive for
the server? Can I run something like this:
create proc users
@.Fullname VARCHAR Output, @.Security Integer OUTPUT, etc.
Run query that verifies username
if username valid
check password against previously returned recordset
if password valid
@.fullname = returned data, @.security, etc.
else
return 2
else
return 1
end
By the way, i'm accessing this from an ASP.NET page.
David Lozzi
Web Applications/Network Specialist
Delphi Technology Solutions, Inc.
dlozzi(remove-this)@.delphi-ts.com
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:eMvhnASDFHA.2632@.TK2MSFTNGP12.phx.gbl...
> This should get you started:
> create proc foo as
> @.loginname varchar(20),
> @.password varchar(50)
> as
> --verify that the login is valid
> if not exists (select * from sometable where somelogincolumn = @.loginname)
> BEGIN
> RETURN (1)
> END
> --verify that the password is valid
> if not exists (select * from sometable where somelogincolumn = @.loginname
> AND somepasswordcolumn = @.password)
> BEGIN
> RETURN (2)
> END
> select somecolumn
> from sometable
> where somelogincolumn = @.loginnamae
> and somepasswordcolumn = @.password
> RETURN (0)
> GO
> The return code will be 1 if the login does not exist, 2 if the password
> is
> not correct. If the login exists and the password is correct the third
> statement will be executed and the return code will be set to 0.
> --
> Keith
>
> "David Lozzi" <dlozzi@.(remove-this)delphi-ts.com> wrote in message
> news:eShjWtRDFHA.512@.TK2MSFTNGP15.phx.gbl...
> I'm fairly new to the stored procedure world, I can create one to return a
> record set, look at me go. However, I need to get a little more advanced.
> I
> need to do the following in a proc:
> Query a honken huge query and check if login is valid. If so, continue
> through proc and return the necessary data, if not return an invalid
> status.
> Thanks a million!!
>
> --
> David Lozzi
> Web Applications/Network Specialist
> Delphi Technology Solutions, Inc.
> dlozzi(remove-this)@.delphi-ts.com
>|||You would change the DO SOMETHING into a proper select statement that
returns the data that you are interested in.
Keith
"David Lozzi" <dlozzi@.(remove-this)delphi-ts.com> wrote in message
news:ubZno6RDFHA.936@.TK2MSFTNGP12.phx.gbl...
This is great, but how do I now access that recordset if it exists?
David Lozzi
Web Applications/Network Specialist
Delphi Technology Solutions, Inc.
dlozzi(remove-this)@.delphi-ts.com
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23hYr9xRDFHA.1524@.TK2MSFTNGP09.phx.gbl...
David
(untested)
CREATE PROC spSomething
@.login VARCHAR(20)
AS
IF EXISTS(SELECT name FROM master..syslogins WHERE name =@.login )
BEGIN
DO something
ELSE
PRINT 'A login does not exist'
END
GO
"David Lozzi" <dlozzi@.(remove-this)delphi-ts.com> wrote in message
news:eShjWtRDFHA.512@.TK2MSFTNGP15.phx.gbl...
I'm fairly new to the stored procedure world, I can create one to return
a record set, look at me go. However, I need to get a little more advanced.
I need to do the following in a proc:
Query a honken huge query and check if login is valid. If so, continue
through proc and return the necessary data, if not return an invalid status.
Thanks a million!!
David Lozzi
Web Applications/Network Specialist
Delphi Technology Solutions, Inc.
dlozzi(remove-this)@.delphi-ts.com|||You can do anything you want. Do you care if the loginname is invalid? Do
you care if the password is invalid? Or do you just want a "Yes,
authentication passed -- here is your data" or an "Authentication Failed"
notification?
Keith
"David Lozzi" <dlozzi@.(remove-this)delphi-ts.com> wrote in message
news:e8kNiGSDFHA.560@.TK2MSFTNGP15.phx.gbl...
> So the best process is to rerun the query? Wouldnt that get extensive for
> the server? Can I run something like this:
> create proc users
> @.Fullname VARCHAR Output, @.Security Integer OUTPUT, etc.
> Run query that verifies username
> if username valid
> check password against previously returned recordset
> if password valid
> @.fullname = returned data, @.security, etc.
> else
> return 2
> else
> return 1
> end
>
> By the way, i'm accessing this from an ASP.NET page.
> --
> David Lozzi
> Web Applications/Network Specialist
> Delphi Technology Solutions, Inc.
> dlozzi(remove-this)@.delphi-ts.com
>
> "Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
> news:eMvhnASDFHA.2632@.TK2MSFTNGP12.phx.gbl...
@.loginname)
@.loginname
a
advanced.
>|||I guess, I'm confuzzled about how to access the initial query throughout the
rest of the proc?
David Lozzi
Web Applications/Network Specialist
Delphi Technology Solutions, Inc.
dlozzi(remove-this)@.delphi-ts.com
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:O3oE6RSDFHA.3452@.TK2MSFTNGP09.phx.gbl...
> You can do anything you want. Do you care if the loginname is invalid?
> Do
> you care if the password is invalid? Or do you just want a "Yes,
> authentication passed -- here is your data" or an "Authentication Failed"
> notification?
>
> --
> Keith
>
> "David Lozzi" <dlozzi@.(remove-this)delphi-ts.com> wrote in message
> news:e8kNiGSDFHA.560@.TK2MSFTNGP15.phx.gbl...
> @.loginname)
> @.loginname
> a
> advanced.
>|||I am why you think you need to access the initial query throughout
the rest of the stored procedure.
It would be helpful if you told us what the requirements are for the stored
procedure. What are the inputs (parameters)? What are the expected results
(recordset/output variables/return code)?
Keith
"David Lozzi" <dlozzi@.(remove-this)delphi-ts.com> wrote in message
news:%23UjJYVSDFHA.3120@.TK2MSFTNGP12.phx.gbl...
> I guess, I'm confuzzled about how to access the initial query throughout
the
> rest of the proc?|||Yes, that would me sense, I reposted at 11:12.
thanks!
David Lozzi
Web Applications/Network Specialist
Delphi Technology Solutions, Inc.
dlozzi(remove-this)@.delphi-ts.com
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:e6HoWBTDFHA.392@.TK2MSFTNGP14.phx.gbl...
>I am why you think you need to access the initial query throughout
> the rest of the stored procedure.
> It would be helpful if you told us what the requirements are for the
> stored
> procedure. What are the inputs (parameters)? What are the expected
> results
> (recordset/output variables/return code)?
> --
> Keith
>
> "David Lozzi" <dlozzi@.(remove-this)delphi-ts.com> wrote in message
> news:%23UjJYVSDFHA.3120@.TK2MSFTNGP12.phx.gbl...
> the
>

A Good insert record if non existant Stored Procedure

Can anyone spare me a good SP that ...

checks if the record exists - and if so return the identity.

and if the record does not exist

inserts it and returns the identity.

Mine keep flipping up :) so I rather look at someone else and use theirs.

Thanks in advance.

Create Proc Proc1
(
parameters...,
@.out int = null output
)
as
select @.out = identcolumn from table1 where ... (use other parameters to determine what to look for)
if @.out is not null
 return
insert into table1 (field list)
values (parameters)
set @.out = @.@.identity
return

You could use EXISTS, but in this case you want the identity column. So, if you use EXISTS, you'd have to do a second query to get the identity. Just do one query, if it's there send back the ID, if not, insert it and send back the ID. You could add another output parameter if want to indicate whether you inserted or not.|||Its the same thing I was doing - somehow mine keeps tripping up. Anyway, I was hoping maybe there was a more efficient means aside from two queries.

Thanks for the insight!|||you could combine the insert with the (not) exists into one batch, but really what's the point? It a bit like those mad people that write whole programs in C on one line.