select statement

[BJ]

Hi all,
I just need to find out when we have a select statement in a procudure is it possible to store the outcome into a variable?
for example:
select @sort_num = 'select max(sortnum) from table1 where pipeid = 1000 '
exec @sort_num = @result_sortnum ->This statement is not working for me but I dont know anyother way to store the outcome into a variable

The sortnum is an interger datatype. So when I get the Max of sortnum the value would have to be an int.
Please help, any sort of suggestions will be of help!!
Thank you so much!!

[Karl]

Try this,

select @sort_num = max(sortnum) from table1 where pipeid = 1000.

The above statement will pass the value of max(sortnum) into your parameter @sort_num.

I'm not sure what you are trying to do with the next statement though:
"exec @sort_num = @result_sortnum"

If what you're trying to do is pass the value of @result_sortnum into @sort_num, try the following:

SET @sort_num = @result_sortnum.

Hope that helps a bit.

Karl

------------
BJ at 7/5/01 2:19:21 PM

Hi all,
I just need to find out when we have a select statement in a procudure is it possible to store the outcome into a variable?
for example:
select @sort_num = 'select max(sortnum) from table1 where pipeid = 1000 '
exec @sort_num = @result_sortnum ->This statement is not working for me but I dont know anyother way to store the outcome into a variable

The sortnum is an interger datatype. So when I get the Max of sortnum the value would have to be an int.
Please help, any sort of suggestions will be of help!!
Thank you so much!!

[Anu]

Hi,

Can you send the full query that you are trying to do.
I will give you a solution.

-Anu




------------
BJ at 7/5/01 2:19:21 PM

Hi all,
I just need to find out when we have a select statement in a procudure is it possible to store the outcome into a variable?
for example:
select @sort_num = 'select max(sortnum) from table1 where pipeid = 1000 '
exec @sort_num = @result_sortnum ->This statement is not working for me but I dont know anyother way to store the outcome into a variable

The sortnum is an interger datatype. So when I get the Max of sortnum the value would have to be an int.
Please help, any sort of suggestions will be of help!!
Thank you so much!!

Hi Anu,
The problem I had before has been resolved. Now I have another problem.
This is my code that I'm working on:

CREATE PROCEDURE [sp_check_MpMark_Values]

AS
drop table geo_check_pipeid
drop table test_table
waitfor delay '00:01:20'
create table test_table(mpmark_num smallint, mpmark_chainage numeric, mpmark_desc varchar(1000))
waitfor delay '00:01:20'
select distinct pipeid into geo_check_pipeid from geo_lineobj
order by pipeid
waitfor delay '00:01:20'


declare @max_sortorder int
declare @new_chainage numeric
declare @new_desc varchar(1000)
declare @new_num smallint
declare @geo_pipeid int


declare retrive_pipeid_crsr cursor
for

select pipeid from geo_check_pipeid
waitfor delay '00:02:20'
open retrive_pipeid_crsr
while @@fetch_status = 0
begin
fetch next from retrive_pipeid_crsr into @geo_pipeid
waitfor delay '00:02:20'

select @max_sortorder = max(sortorder) from geo_lineobj where pipeid = @geo_pipeid
waitfor delay '00:01:30'
select @new_num = mpmark_num, @new_chainage = mpmark_chainage, @new_desc = mpmark_desc from geo_lineobj where pipeid = @geo_pipeid
waitfor delay '00:02:30'
insert into test_table (mpmark_num, mpmark_chainage, mpmark_desc) values (@new_num, @new_chainage, @new_desc)
waitfor delay '00:02:30'


end
close retrive_pipeid_crsr

deallocate retrive_pipeid_crsr

***
I need to select three column from geo_lineobj based on distinct pipeid and max sortorder columns. Then I want to populate the table(test_table) with the values from geo_lineobj. I am having trouble with populating the test_table because the sql server is not taking the time to get all the information. I put a timer on my select statements, still its not working properly.
Can you give my any solution to this problem
Thank you again, I greatly appreciate your help!!







------------
Anu at 7/6/01 10:04:18 AM

Hi,

Can you send the full query that you are trying to do.
I will give you a solution.

-Anu




------------
BJ at 7/5/01 2:19:21 PM

Hi all,
I just need to find out when we have a select statement in a procudure is it possible to store the outcome into a variable?
for example:
select @sort_num = 'select max(sortnum) from table1 where pipeid = 1000 '
exec @sort_num = @result_sortnum ->This statement is not working for me but I dont know anyother way to store the outcome into a variable

The sortnum is an interger datatype. So when I get the Max of sortnum the value would have to be an int.
Please help, any sort of suggestions will be of help!!
Thank you so much!!

[Todd]

I am not sure what you are trying to do, but I think this might be it.

create table test_table(
mpmark_num smallint,
mpmark_chainage numeric,
mpmark_desc varchar(1000))

INSERT test_table
select mpmark_num, mpmark_chainage, mpmark_desc
from geo_lineobj A
JOIN (select pipeid ,Max(sortorder)as "SortOrder"
from geo_lineobj
order by pipeid) AS B
ON A.Pipeid = B.Pipeid and
A.Sortorder = B.Sortorder



------------
at 7/6/01 10:14:22 AM


Hi Anu,
The problem I had before has been resolved. Now I have another problem.
This is my code that I'm working on:

CREATE PROCEDURE [sp_check_MpMark_Values]

AS
drop table geo_check_pipeid
drop table test_table
waitfor delay '00:01:20'
create table test_table(mpmark_num smallint, mpmark_chainage numeric, mpmark_desc varchar(1000))
waitfor delay '00:01:20'
select distinct pipeid into geo_check_pipeid from geo_lineobj
order by pipeid
waitfor delay '00:01:20'


declare @max_sortorder int
declare @new_chainage numeric
declare @new_desc varchar(1000)
declare @new_num smallint
declare @geo_pipeid int


declare retrive_pipeid_crsr cursor
for

select pipeid from geo_check_pipeid
waitfor delay '00:02:20'
open retrive_pipeid_crsr
while @@fetch_status = 0
begin
fetch next from retrive_pipeid_crsr into @geo_pipeid
waitfor delay '00:02:20'

select @max_sortorder = max(sortorder) from geo_lineobj where pipeid = @geo_pipeid
waitfor delay '00:01:30'
select @new_num = mpmark_num, @new_chainage = mpmark_chainage, @new_desc = mpmark_desc from geo_lineobj where pipeid = @geo_pipeid
waitfor delay '00:02:30'
insert into test_table (mpmark_num, mpmark_chainage, mpmark_desc) values (@new_num, @new_chainage, @new_desc)
waitfor delay '00:02:30'


end
close retrive_pipeid_crsr

deallocate retrive_pipeid_crsr

***
I need to select three column from geo_lineobj based on distinct pipeid and max sortorder columns. Then I want to populate the table(test_table) with the values from geo_lineobj. I am having trouble with populating the test_table because the sql server is not taking the time to get all the information. I put a timer on my select statements, still its not working properly.
Can you give my any solution to this problem
Thank you again, I greatly appreciate your help!!







------------
Anu at 7/6/01 10:04:18 AM

Hi,

Can you send the full query that you are trying to do.
I will give you a solution.

-Anu




------------
BJ at 7/5/01 2:19:21 PM

Hi all,
I just need to find out when we have a select statement in a procudure is it possible to store the outcome into a variable?
for example:
select @sort_num = 'select max(sortnum) from table1 where pipeid = 1000 '
exec @sort_num = @result_sortnum ->This statement is not working for me but I dont know anyother way to store the outcome into a variable

The sortnum is an interger datatype. So when I get the Max of sortnum the value would have to be an int.
Please help, any sort of suggestions will be of help!!
Thank you so much!!

Hey Todd,
When I run your select statement I'm getting an error some thing like this:

An ORDER BY clause is invalid in views, derived tables, and subqueries unless TOP is also specified.
Thank you for all your help!!
------------
Todd at 7/6/01 10:56:38 AM

I am not sure what you are trying to do, but I think this might be it.

create table test_table(
mpmark_num smallint,
mpmark_chainage numeric,
mpmark_desc varchar(1000))

INSERT test_table
select mpmark_num, mpmark_chainage, mpmark_desc
from geo_lineobj A
JOIN (select pipeid ,Max(sortorder)as "SortOrder"
from geo_lineobj
order by pipeid) AS B
ON A.Pipeid = B.Pipeid and
A.Sortorder = B.Sortorder



------------
at 7/6/01 10:14:22 AM


Hi Anu,
The problem I had before has been resolved. Now I have another problem.
This is my code that I'm working on:

CREATE PROCEDURE [sp_check_MpMark_Values]

AS
drop table geo_check_pipeid
drop table test_table
waitfor delay '00:01:20'
create table test_table(mpmark_num smallint, mpmark_chainage numeric, mpmark_desc varchar(1000))
waitfor delay '00:01:20'
select distinct pipeid into geo_check_pipeid from geo_lineobj
order by pipeid
waitfor delay '00:01:20'


declare @max_sortorder int
declare @new_chainage numeric
declare @new_desc varchar(1000)
declare @new_num smallint
declare @geo_pipeid int


declare retrive_pipeid_crsr cursor
for

select pipeid from geo_check_pipeid
waitfor delay '00:02:20'
open retrive_pipeid_crsr
while @@fetch_status = 0
begin
fetch next from retrive_pipeid_crsr into @geo_pipeid
waitfor delay '00:02:20'

select @max_sortorder = max(sortorder) from geo_lineobj where pipeid = @geo_pipeid
waitfor delay '00:01:30'
select @new_num = mpmark_num, @new_chainage = mpmark_chainage, @new_desc = mpmark_desc from geo_lineobj where pipeid = @geo_pipeid
waitfor delay '00:02:30'
insert into test_table (mpmark_num, mpmark_chainage, mpmark_desc) values (@new_num, @new_chainage, @new_desc)
waitfor delay '00:02:30'


end
close retrive_pipeid_crsr

deallocate retrive_pipeid_crsr

***
I need to select three column from geo_lineobj based on distinct pipeid and max sortorder columns. Then I want to populate the table(test_table) with the values from geo_lineobj. I am having trouble with populating the test_table because the sql server is not taking the time to get all the information. I put a timer on my select statements, still its not working properly.
Can you give my any solution to this problem
Thank you again, I greatly appreciate your help!!







------------
Anu at 7/6/01 10:04:18 AM

Hi,

Can you send the full query that you are trying to do.
I will give you a solution.

-Anu




------------
BJ at 7/5/01 2:19:21 PM

Hi all,
I just need to find out when we have a select statement in a procudure is it possible to store the outcome into a variable?
for example:
select @sort_num = 'select max(sortnum) from table1 where pipeid = 1000 '
exec @sort_num = @result_sortnum ->This statement is not working for me but I dont know anyother way to store the outcome into a variable

The sortnum is an interger datatype. So when I get the Max of sortnum the value would have to be an int.
Please help, any sort of suggestions will be of help!!
Thank you so much!!

[Anu]

Here you go,

CREATE PROCEDURE [sp_check_MpMark_Values]
AS

if exists(select * from sysobjects where name='geo_check_pipeid&#39
begin
drop table geo_check_pipeid
select distinct pipeid into geo_check_pipeid from geo_lineobj
order by pipeid
end
else
begin
select distinct pipeid into geo_check_pipeid from geo_lineobj
order by pipeid
end

if exists(select * from sysobjects where name='test_table&#39
begin
drop table test_table
create table test_table(mpmark_num smallint, mpmark_chainage numeric, mpmark_desc varchar(1000))
end
else
begin
create table test_table(mpmark_num smallint, mpmark_chainage numeric, mpmark_desc varchar(1000))
end

declare @max_sortorder int
declare @new_chainage numeric
declare @new_desc varchar(1000)
declare @new_num smallint
declare @geo_pipeid int

set nocount on
declare retrive_pipeid_crsr cursor
for
select pipeid from geo_check_pipeid

open retrive_pipeid_crsr

fetch next from retrive_pipeid_crsr into @geo_pipeid

while @@fetch_status = 0
begin

set @max_sortorder = (select max(sortorder) from geo_lineobj where pipeid = @geo_pipeid)
select @max_sortorder

select @new_num = mpmark_num, @new_chainage = mpmark_chainage, @new_desc = mpmark_desc from geo_lineobj where pipeid = @geo_pipeid

insert into test_table (mpmark_num, mpmark_chainage, mpmark_desc) select @new_num, @new_chainage, @new_desc

fetch next from retrive_pipeid_crsr into @geo_pipeid
end

close retrive_pipeid_crsr

deallocate retrive_pipeid_crsr

select * from test_table



Please let me know if you any problem with this.

-Anu


------------
at 7/6/01 10:14:22 AM


Hi Anu,
The problem I had before has been resolved. Now I have another problem.
This is my code that I'm working on:

CREATE PROCEDURE [sp_check_MpMark_Values]

AS
drop table geo_check_pipeid
drop table test_table
waitfor delay '00:01:20'
create table test_table(mpmark_num smallint, mpmark_chainage numeric, mpmark_desc varchar(1000))
waitfor delay '00:01:20'
select distinct pipeid into geo_check_pipeid from geo_lineobj
order by pipeid
waitfor delay '00:01:20'


declare @max_sortorder int
declare @new_chainage numeric
declare @new_desc varchar(1000)
declare @new_num smallint
declare @geo_pipeid int


declare retrive_pipeid_crsr cursor
for

select pipeid from geo_check_pipeid
waitfor delay '00:02:20'
open retrive_pipeid_crsr
while @@fetch_status = 0
begin
fetch next from retrive_pipeid_crsr into @geo_pipeid
waitfor delay '00:02:20'

select @max_sortorder = max(sortorder) from geo_lineobj where pipeid = @geo_pipeid
waitfor delay '00:01:30'
select @new_num = mpmark_num, @new_chainage = mpmark_chainage, @new_desc = mpmark_desc from geo_lineobj where pipeid = @geo_pipeid
waitfor delay '00:02:30'
insert into test_table (mpmark_num, mpmark_chainage, mpmark_desc) values (@new_num, @new_chainage, @new_desc)
waitfor delay '00:02:30'


end
close retrive_pipeid_crsr

deallocate retrive_pipeid_crsr

***
I need to select three column from geo_lineobj based on distinct pipeid and max sortorder columns. Then I want to populate the table(test_table) with the values from geo_lineobj. I am having trouble with populating the test_table because the sql server is not taking the time to get all the information. I put a timer on my select statements, still its not working properly.
Can you give my any solution to this problem
Thank you again, I greatly appreciate your help!!







------------
Anu at 7/6/01 10:04:18 AM

Hi,

Can you send the full query that you are trying to do.
I will give you a solution.

-Anu




------------
BJ at 7/5/01 2:19:21 PM

Hi all,
I just need to find out when we have a select statement in a procudure is it possible to store the outcome into a variable?
for example:
select @sort_num = 'select max(sortnum) from table1 where pipeid = 1000 '
exec @sort_num = @result_sortnum ->This statement is not working for me but I dont know anyother way to store the outcome into a variable

The sortnum is an interger datatype. So when I get the Max of sortnum the value would have to be an int.
Please help, any sort of suggestions will be of help!!
Thank you so much!!

Hi Anu,
I think what you have send me is working but (I need to check for sure) at the moment I cannot access the sql server so I have to try it a bit later. Thank you so much for your help!! Do you get paid for all this? Your response time is so quick!! THANK YOU!!


------------
Anu at 7/6/01 1:04:40 PM

Here you go,

CREATE PROCEDURE [sp_check_MpMark_Values]
AS

if exists(select * from sysobjects where name='geo_check_pipeid&#39
begin
drop table geo_check_pipeid
select distinct pipeid into geo_check_pipeid from geo_lineobj
order by pipeid
end
else
begin
select distinct pipeid into geo_check_pipeid from geo_lineobj
order by pipeid
end

if exists(select * from sysobjects where name='test_table&#39
begin
drop table test_table
create table test_table(mpmark_num smallint, mpmark_chainage numeric, mpmark_desc varchar(1000))
end
else
begin
create table test_table(mpmark_num smallint, mpmark_chainage numeric, mpmark_desc varchar(1000))
end

declare @max_sortorder int
declare @new_chainage numeric
declare @new_desc varchar(1000)
declare @new_num smallint
declare @geo_pipeid int

set nocount on
declare retrive_pipeid_crsr cursor
for
select pipeid from geo_check_pipeid

open retrive_pipeid_crsr

fetch next from retrive_pipeid_crsr into @geo_pipeid

while @@fetch_status = 0
begin

set @max_sortorder = (select max(sortorder) from geo_lineobj where pipeid = @geo_pipeid)
select @max_sortorder

select @new_num = mpmark_num, @new_chainage = mpmark_chainage, @new_desc = mpmark_desc from geo_lineobj where pipeid = @geo_pipeid

insert into test_table (mpmark_num, mpmark_chainage, mpmark_desc) select @new_num, @new_chainage, @new_desc

fetch next from retrive_pipeid_crsr into @geo_pipeid
end

close retrive_pipeid_crsr

deallocate retrive_pipeid_crsr

select * from test_table



Please let me know if you any problem with this.

-Anu


------------
at 7/6/01 10:14:22 AM


Hi Anu,
The problem I had before has been resolved. Now I have another problem.
This is my code that I'm working on:

CREATE PROCEDURE [sp_check_MpMark_Values]

AS
drop table geo_check_pipeid
drop table test_table
waitfor delay '00:01:20'
create table test_table(mpmark_num smallint, mpmark_chainage numeric, mpmark_desc varchar(1000))
waitfor delay '00:01:20'
select distinct pipeid into geo_check_pipeid from geo_lineobj
order by pipeid
waitfor delay '00:01:20'


declare @max_sortorder int
declare @new_chainage numeric
declare @new_desc varchar(1000)
declare @new_num smallint
declare @geo_pipeid int


declare retrive_pipeid_crsr cursor
for

select pipeid from geo_check_pipeid
waitfor delay '00:02:20'
open retrive_pipeid_crsr
while @@fetch_status = 0
begin
fetch next from retrive_pipeid_crsr into @geo_pipeid
waitfor delay '00:02:20'

select @max_sortorder = max(sortorder) from geo_lineobj where pipeid = @geo_pipeid
waitfor delay '00:01:30'
select @new_num = mpmark_num, @new_chainage = mpmark_chainage, @new_desc = mpmark_desc from geo_lineobj where pipeid = @geo_pipeid
waitfor delay '00:02:30'
insert into test_table (mpmark_num, mpmark_chainage, mpmark_desc) values (@new_num, @new_chainage, @new_desc)
waitfor delay '00:02:30'


end
close retrive_pipeid_crsr

deallocate retrive_pipeid_crsr

***
I need to select three column from geo_lineobj based on distinct pipeid and max sortorder columns. Then I want to populate the table(test_table) with the values from geo_lineobj. I am having trouble with populating the test_table because the sql server is not taking the time to get all the information. I put a timer on my select statements, still its not working properly.
Can you give my any solution to this problem
Thank you again, I greatly appreciate your help!!







------------
Anu at 7/6/01 10:04:18 AM

Hi,

Can you send the full query that you are trying to do.
I will give you a solution.

-Anu




------------
BJ at 7/5/01 2:19:21 PM

Hi all,
I just need to find out when we have a select statement in a procudure is it possible to store the outcome into a variable?
for example:
select @sort_num = 'select max(sortnum) from table1 where pipeid = 1000 '
exec @sort_num = @result_sortnum ->This statement is not working for me but I dont know anyother way to store the outcome into a variable

The sortnum is an interger datatype. So when I get the Max of sortnum the value would have to be an int.
Please help, any sort of suggestions will be of help!!
Thank you so much!!

[Anu]

Oh Yes,

I am paid like Bill Gates hahahaha LOL

- Anu




------------
at 7/6/01 1:53:34 PM

Hi Anu,
I think what you have send me is working but (I need to check for sure) at the moment I cannot access the sql server so I have to try it a bit later. Thank you so much for your help!! Do you get paid for all this? Your response time is so quick!! THANK YOU!!


------------
Anu at 7/6/01 1:04:40 PM

Here you go,

CREATE PROCEDURE [sp_check_MpMark_Values]
AS

if exists(select * from sysobjects where name='geo_check_pipeid&#39
begin
drop table geo_check_pipeid
select distinct pipeid into geo_check_pipeid from geo_lineobj
order by pipeid
end
else
begin
select distinct pipeid into geo_check_pipeid from geo_lineobj
order by pipeid
end

if exists(select * from sysobjects where name='test_table&#39
begin
drop table test_table
create table test_table(mpmark_num smallint, mpmark_chainage numeric, mpmark_desc varchar(1000))
end
else
begin
create table test_table(mpmark_num smallint, mpmark_chainage numeric, mpmark_desc varchar(1000))
end

declare @max_sortorder int
declare @new_chainage numeric
declare @new_desc varchar(1000)
declare @new_num smallint
declare @geo_pipeid int

set nocount on
declare retrive_pipeid_crsr cursor
for
select pipeid from geo_check_pipeid

open retrive_pipeid_crsr

fetch next from retrive_pipeid_crsr into @geo_pipeid

while @@fetch_status = 0
begin

set @max_sortorder = (select max(sortorder) from geo_lineobj where pipeid = @geo_pipeid)
select @max_sortorder

select @new_num = mpmark_num, @new_chainage = mpmark_chainage, @new_desc = mpmark_desc from geo_lineobj where pipeid = @geo_pipeid

insert into test_table (mpmark_num, mpmark_chainage, mpmark_desc) select @new_num, @new_chainage, @new_desc

fetch next from retrive_pipeid_crsr into @geo_pipeid
end

close retrive_pipeid_crsr

deallocate retrive_pipeid_crsr

select * from test_table



Please let me know if you any problem with this.

-Anu


------------
at 7/6/01 10:14:22 AM


Hi Anu,
The problem I had before has been resolved. Now I have another problem.
This is my code that I'm working on:

CREATE PROCEDURE [sp_check_MpMark_Values]

AS
drop table geo_check_pipeid
drop table test_table
waitfor delay '00:01:20'
create table test_table(mpmark_num smallint, mpmark_chainage numeric, mpmark_desc varchar(1000))
waitfor delay '00:01:20'
select distinct pipeid into geo_check_pipeid from geo_lineobj
order by pipeid
waitfor delay '00:01:20'


declare @max_sortorder int
declare @new_chainage numeric
declare @new_desc varchar(1000)
declare @new_num smallint
declare @geo_pipeid int


declare retrive_pipeid_crsr cursor
for

select pipeid from geo_check_pipeid
waitfor delay '00:02:20'
open retrive_pipeid_crsr
while @@fetch_status = 0
begin
fetch next from retrive_pipeid_crsr into @geo_pipeid
waitfor delay '00:02:20'

select @max_sortorder = max(sortorder) from geo_lineobj where pipeid = @geo_pipeid
waitfor delay '00:01:30'
select @new_num = mpmark_num, @new_chainage = mpmark_chainage, @new_desc = mpmark_desc from geo_lineobj where pipeid = @geo_pipeid
waitfor delay '00:02:30'
insert into test_table (mpmark_num, mpmark_chainage, mpmark_desc) values (@new_num, @new_chainage, @new_desc)
waitfor delay '00:02:30'


end
close retrive_pipeid_crsr

deallocate retrive_pipeid_crsr

***
I need to select three column from geo_lineobj based on distinct pipeid and max sortorder columns. Then I want to populate the table(test_table) with the values from geo_lineobj. I am having trouble with populating the test_table because the sql server is not taking the time to get all the information. I put a timer on my select statements, still its not working properly.
Can you give my any solution to this problem
Thank you again, I greatly appreciate your help!!







------------
Anu at 7/6/01 10:04:18 AM

Hi,

Can you send the full query that you are trying to do.
I will give you a solution.

-Anu




------------
BJ at 7/5/01 2:19:21 PM

Hi all,
I just need to find out when we have a select statement in a procudure is it possible to store the outcome into a variable?
for example:
select @sort_num = 'select max(sortnum) from table1 where pipeid = 1000 '
exec @sort_num = @result_sortnum ->This statement is not working for me but I dont know anyother way to store the outcome into a variable

The sortnum is an interger datatype. So when I get the Max of sortnum the value would have to be an int.
Please help, any sort of suggestions will be of help!!
Thank you so much!!