store procedure did not have output

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

store procedure did not have output

Author

Topic

naturale02
Starting Member

7 Posts

Posted - 2006-02-22 : 03:48:31

Here is my SP:

CREATE procedure test as
select top 10 count (*) as Download, a.item_code as Item_Code, b.item_title as Item_Title
into report..ten
from tbl_statistics_archive a , shabox..tbl_items b
where a.item_class = '10'
and convert(char(10),a.created,111) >= convert(char(10),getdate()-7,111)
and convert(char(10),a.created,111) <= convert(char(10),getdate()-1,111)
and a.item_code = b.item_code and a.item_class = b.item_class
group by a.item_code, b.item_title
order by 1 desc
GO

I have create table called 'ten', and schedule this SP, when i start the job, there was nothing inside 'ten' table, which part i have doing wrong, kindly help me

Thank you.

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2006-02-22 : 04:09:20

Are yu sure you want to use Select Into as Once the SP is executed ten will be created and if you try to execute it again it will return an error.
Instead of using Slect Into .
use
insert into ten (<column list>)
Select statement

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-22 : 04:42:32

select ... into will create a table from the result set of the select statement. Unless you are select .. into a temp table (table name which prefix by #), executing your stored procedure the 2nd time will result in error as the table would have been created the first time you executed the stored procedure.

It seems that your current stored procedure inserting 10 records into a table each time it is executed. And if you continously run it 10 times, your target table will contain 10 sets of identical records. Is this what you want ? What are your trying to achieve ?

----------------------------------
'KH'
It is inevitable

naturale02
Starting Member

7 Posts

Posted - 2006-02-22 : 22:18:46

quote:
Originally posted by khtan

select ... into will create a table from the result set of the select statement. Unless you are select .. into a temp table (table name which prefix by #), executing your stored procedure the 2nd time will result in error as the table would have been created the first time you executed the stored procedure.

It seems that your current stored procedure inserting 10 records into a table each time it is executed. And if you continously run it 10 times, your target table will contain 10 sets of identical records. Is this what you want ? What are your trying to achieve ?

----------------------------------
'KH'
It is inevitable

yes, i just need 10 record every week. ii have try by using
insert into ...
select ...
but the record is increasing by x10
what i want is just 10 record
hw should i write?
thank you

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-22 : 22:23:06

delete your table ten before insert

----------------------------------
'KH'
It is inevitable

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-23 : 01:33:44

If you want to overwrite the data, truncate the table and do insertion.

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources