compare two columns for each row of resultset

Hi ,
I have a result set including two columns , I must compare two
columns
and put the larger number in another columns. I can use a loop ,
but I that's too slow on large datasets so I need something like
maxnum(Expr1,Expr2)
which can be applied on two numbers :
sample:
Expr1 Expr2 Expr3 must be :
10 15 15
70 32 70
12 40 40
50 25 50
any tricks ?
Best regards ,
Mehdy
--CREATE a FUNCTION (UDF) that accepts the comma delimited set of values, and
then parses them into a table variable, and returns the TOP 1.
For help, see:
http://realsqlguy.com/serendipity/a...le
.html
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<bihoode@.gmail.com> wrote in message
news:1157576675.449860.135530@.p79g2000cwp.googlegroups.com...
> Hi ,
> I have a result set including two columns , I must compare two
> columns
> and put the larger number in another columns. I can use a loop ,
> but I that's too slow on large datasets so I need something like
> maxnum(Expr1,Expr2)
> which can be applied on two numbers :
> sample:
> Expr1 Expr2 Expr3 must be :
> 10 15 15
> 70 32 70
> 12 40 40
> 50 25 50
> any tricks ?
>
> Best regards ,
> Mehdy
> --
>|||-- Your Function is here
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[MaxOfTwoNumbers] (@.num1 int, @.num2 int)
RETURNS int AS
BEGIN
declare @.ReturnNum int
if @.num1 > @.num2
set @.ReturnNum = @.num1
else
set @.ReturnNum = @.num2
return @.ReturnNum
END
-- Simple function test
select dbo.MaxOfTwoNumbers (10,2)
-- So your ultimate update becomes:
Update yourtable set Expr3 = dbo.MaxOfTwoNumbers (Expr1,Expr2)
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||bihoode@.gmail.com wrote:
> Hi ,
> I have a result set including two columns , I must compare two
> columns
> and put the larger number in another columns. I can use a loop ,
> but I that's too slow on large datasets so I need something like
> maxnum(Expr1,Expr2)
> which can be applied on two numbers :
> sample:
> Expr1 Expr2 Expr3 must be :
> 10 15 15
> 70 32 70
> 12 40 40
> 50 25 50
> any tricks ?
>
> Best regards ,
> Mehdy
> --
>
SELECT
Col1,
Col2,
CASE WHEN Col1 > Col2 THEN Col1 ELSE Col2 END AS Col3
FROM MyTable
Tracy McKibben
MCDBA
http://www.realsqlguy.com

compare two columns for each row of resultset

Hi ,
I have a result set including two columns , I must compare two
columns
and put the larger number in another columns. I can use a loop ,
but I that's too slow on large datasets so I need something like
maxnum(Expr1,Expr2)
which can be applied on two numbers :
sample:
Expr1 Expr2 Expr3 must be :
10 15 15
70 32 70
12 40 40
50 25 50
any tricks ?
Best regards ,
Mehdy
--CREATE a FUNCTION (UDF) that accepts the comma delimited set of values, and
then parses them into a table variable, and returns the TOP 1.
For help, see:
http://realsqlguy.com/serendipity/archives/4-Parse-A-Delimited-String-Into-A-Table.html
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<bihoode@.gmail.com> wrote in message
news:1157576675.449860.135530@.p79g2000cwp.googlegroups.com...
> Hi ,
> I have a result set including two columns , I must compare two
> columns
> and put the larger number in another columns. I can use a loop ,
> but I that's too slow on large datasets so I need something like
> maxnum(Expr1,Expr2)
> which can be applied on two numbers :
> sample:
> Expr1 Expr2 Expr3 must be :
> 10 15 15
> 70 32 70
> 12 40 40
> 50 25 50
> any tricks ?
>
> Best regards ,
> Mehdy
> --
>|||-- Your Function is here
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[MaxOfTwoNumbers] (@.num1 int, @.num2 int)
RETURNS int AS
BEGIN
declare @.ReturnNum int
if @.num1 > @.num2
set @.ReturnNum = @.num1
else
set @.ReturnNum = @.num2
return @.ReturnNum
END
-- Simple function test
select dbo.MaxOfTwoNumbers (10,2)
-- So your ultimate update becomes:
Update yourtable set Expr3 = dbo.MaxOfTwoNumbers (Expr1,Expr2)
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||bihoode@.gmail.com wrote:
> Hi ,
> I have a result set including two columns , I must compare two
> columns
> and put the larger number in another columns. I can use a loop ,
> but I that's too slow on large datasets so I need something like
> maxnum(Expr1,Expr2)
> which can be applied on two numbers :
> sample:
> Expr1 Expr2 Expr3 must be :
> 10 15 15
> 70 32 70
> 12 40 40
> 50 25 50
> any tricks ?
>
> Best regards ,
> Mehdy
> --
>
SELECT
Col1,
Col2,
CASE WHEN Col1 > Col2 THEN Col1 ELSE Col2 END AS Col3
FROM MyTable
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Commit a loop of inserting

I use loop to insert few record into a table:
But the for_Loop only loop once and throw an error:
"The variable name '@.res_name' has already been declared. Variable names must be unique within a query batch or stored procedure."
What should i do to get this fix?

Code:

Protected Sub confirm_button_Click(ByVal sender As Object, ByVal e As System.EventArgs)

Dim DataSources1 As New SqlDataSource()
DataSources1.ConnectionString = ConfigurationManager.ConnectionStrings("ConnectionString").ToString()

DataSources1.InsertCommandType = SqlDataSourceCommandType.Text
DataSources1.InsertCommand = "INSERT INTO cust_order (res_name, my_menu) VALUES (@.res_name, @.my_menu)"

Dim c As Integer
For c = 0 To selectListBox.Items.Count - 1 Step +2

DataSources1.InsertParameters.Add("res_name", selectListBox.Items(c).Text)
DataSources1.InsertParameters.Add("my_menu", selectListBox.Items(c + 1).Text)
DataSources1.Insert()
Next
End Sub

your loop is trying the re-add the same parameters on each pass.

For c = 0To selectListBox.Items.Count - 1Step +2 DataSources1.InsertParameters.Add("res_name", selectListBox.Items(c).Text) DataSources1.InsertParameters.Add("my_menu", selectListBox.Items(c + 1).Text) DataSources1.Insert() DataSources1.InsertParameters.Clear()'remove the previous parametersNext

you can clear them after you perform the insert so they can then be re-added with new values on the next pass through the loop

|||thanks, now the loop works fine.