inserting the max colum in the stored proc

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

iHavAQuestion - 13 May 2008 18:46 GMT

I have stored procedure
Create Stored Procedure Name
(
@Var1
@Var2
@Var3
)
Inseret into table1
(
Col1
col2
col3
)
Values
(
@Var1
@Var2
@Var3
)

But in col1 i need to insert select max(col) from tablename, which is in the
same database.

how do I do that??

Reply to this Message

George Ter-Saakov - 13 May 2008 19:01 GMT

you can use something like in (MS SQL)

INSERT INTO table1(Col1, Col2,Col3)
SELECT MAX(col1), col2, col3 GROUP BY col2, col3

George

>I have stored procedure
> Create Stored Procedure Name
[quoted text clipped - 21 lines]
>
> how do I do that??

Reply to this Message

iHavAQuestion - 13 May 2008 20:36 GMT

Thats fine...
But, I actually wanted to add the MAX(col) from tabe that exists from the
same data base in place of @var1/Col1 in the below stored procedure

Hope you got my question

Create Stored Procedure Name
(
@Var1
@Var2
@Var3
)
Inseret into table1
(
Col1
col2
col3
)
Values
(
@Var1
@Var2
@Var3
)

> you can use something like in (MS SQL)
>
[quoted text clipped - 28 lines]
> >
> > how do I do that??

Reply to this Message

George Ter-Saakov - 13 May 2008 21:10 GMT

Sorry, completely not clear....
Can you rephrase or may be write what you want in pseudo SQL....

PS: I meant to say
INSERT INTO table1(Col1, Col2,Col3)
SELECT MAX(col1), col2, col3 FROM table1 GROUP BY col2, col3

George.

> Thats fine...
> But, I actually wanted to add the MAX(col) from tabe that exists from the
[quoted text clipped - 54 lines]
>> >
>> > how do I do that??

Reply to this Message

bruce barker - 13 May 2008 19:23 GMT

Inseret into table1 (
col1
col2
col3
)
Values (
(select max(col1) from table1
@Var2
@Var3
)

of course if two inserts happen at the same time, they will get the same
result. you can set the isolation level to serializable (at a big performance
cost), or if col1 has a unique index, catch the duplicate key error and
resubmit on the failed insert (a better option)

a better option is to use the identity constaint, at let sqlserver assign
the max.

-- bruce (sqlwork.com)

> I have stored procedure
> Create Stored Procedure Name
[quoted text clipped - 20 lines]
>
> how do I do that??

Reply to this Message

iHavAQuestion - 13 May 2008 20:36 GMT

> Inseret into table1 (
> col1
[quoted text clipped - 41 lines]
> >
> > how do I do that??

Reply to this Message

inserting the max colum in the stored proc

Free Magazines