Capturing the output from store procedure and use it

How do I call capture the output (not return value) from calling a store procedure from within a store procedure so I can use that data for further processing (say join it with another table)?

For example,

CREATE PROCEDURE dbo.sp_test AS
-- returns all words not in Mastery Level 0

EXEC sp_anothertest

-- use the data coming back from sp_test and join it with another table here and say insert them into tblFinalResults

SELECT * tblFinalResults
GO

Thanks!I do not think you can do this exactly as you would like. You may need to resort to either a UDF which returns a table, using table variables within the stored procedure, or using temporary tables. Functions are the most flexiable and temporary tables are the slowest. You can also use table variables as output parameters of the stored procedures. Below is an example of using table variables.
begin
DECLARE @.Result1 table (key1 int, foo varchar(32) )
insert into @.Result1 select 1, 'This is Table 1'

DECLARE @.Result2 table ( key2 int, foo varchar(32) )
insert into @.Result2 select 1, 'This is Table 2'

select * from @.Result1 inner join @.Result2 on( key1 = key2 )
end

Caption: 'ms_sqlce_se_notification' on reopen application window

Hello,

another problem i couldn't figure out myself:

I have limited my application to one instance. So i call SetForegroundWindow() upon other in InitInstance() of the application when there is already an instance of the same application running.

This works fine most of the time. But sometimes it only shows the title bar(incl. OK-button) of the application with the caption 'ms_sqlce_se_notification'.
Then i have to manually activate it within the list of running programs.

Could it be a notification i don't handle? Or what could be the reason for such a behaviour?

I've already searched the web for it and found almost no resource for this issue. So it would be lovely if someone already solved this problem and could tell me the solution.

Kind regards,
AndreSometimes also 'Default Ime' instead of 'ms_sqlce_se_notification' is displayed as caption on reopening.

So far i have found out that there are often hidden 'system windows' in pda applications on the same level than the main application windows. And that the function FindWindow(..) sometimes find one of the hidden windows first instead of the main applications window. Is that correct?

Greetings,
Andre
|||

Yes you are right. ms_sqlce_se_notification is a hidden system window for use in SQL CE.

Thanks,

Laxmi

|||So, comming back to the original question: How do i by-pass this issue?

Regards,
Andre

Caption: 'ms_sqlce_se_notification' on reopen application window

Hello,

another problem i couldn't figure out myself:

I have limited my application to one instance. So i call SetForegroundWindow() upon other in InitInstance() of the application when there is already an instance of the same application running.

This works fine most of the time. But sometimes it only shows the title bar(incl. OK-button) of the application with the caption 'ms_sqlce_se_notification'.
Then i have to manually activate it within the list of running programs.

Could it be a notification i don't handle? Or what could be the reason for such a behaviour?

I've already searched the web for it and found almost no resource for this issue. So it would be lovely if someone already solved this problem and could tell me the solution.

Kind regards,
AndreSometimes also 'Default Ime' instead of 'ms_sqlce_se_notification' is displayed as caption on reopening.

So far i have found out that there are often hidden 'system windows' in pda applications on the same level than the main application windows. And that the function FindWindow(..) sometimes find one of the hidden windows first instead of the main applications window. Is that correct?

Greetings,
Andre
|||

Yes you are right. ms_sqlce_se_notification is a hidden system window for use in SQL CE.

Thanks,

Laxmi

|||So, comming back to the original question: How do i by-pass this issue?

Regards,
Andre

Capacity Planning

We are going to install a call centre application.
According to end user, there will be around 500 request
has to be inputted into the system.
We will use SQL Server 2000 as the DB. We would like to
know what factors we have to consider - Like recovery
model, database maintenance plan, fill factors ? Is it
necessary for us to archive some old data to an archive
datbase ?
Thanks"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:1cb701c4b569$d40000a0$a401280a@.phx.gbl...
> We are going to install a call centre application.
> According to end user, there will be around 500 request
> has to be inputted into the system.
>
500 requests over what time period?
> We will use SQL Server 2000 as the DB. We would like to
> know what factors we have to consider - Like recovery
> model, database maintenance plan, fill factors ? Is it
> necessary for us to archive some old data to an archive
> datbase ?
>
Those are really business decisions.
i.e. if you need to run 24x7 vs 9-5x5, your decisions will be different.
If you can do with downtime, you may go with a different decisions on
architecture.
As for archiving, again, that's a business decision. Do you want to archive
data or not?
> Thanks|||Dear Greg,
It should be 500 requests between 9:00am to 5:00pm from
Monday to Friday.
We will make full database backup daily. What is the
difference between full database backup and archive then ?
Thanks|||"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:0fba01c4b574$0e2f4830$a501280a@.phx.gbl...
> Dear Greg,
> It should be 500 requests between 9:00am to 5:00pm from
> Monday to Friday.
500 a day? That's about one a minute. You can run this thing on a desktop
machine.
> We will make full database backup daily. What is the
> difference between full database backup and archive then ?
Generally a backup is for disaster recovery. An archive is for storing data
for later analysis or for other reasons.
For example, I keep backups of only a few days (my databases generally have
enough churn that in a few days the bulk of the data has changed anyway.)
But there's certain data I archive to tape (in a different schema etc) that
I may keep for much longer.
Now, as for once a day backups, that may or may not work. Your database
sounds like it will probably be fairly small to start, so recovery time will
be about the same as backup. i.e. if it takes 10 minutes to backup, it'll
take about 10 minutes to restore. Plus any time to fix up minor issues.
However, let's say you start a backup at 5:01 PM.
What happens if your DB crashes at 5:00 PM. Can you afford to lose a day's
worth of data?
> Thanks
>

Capacity Planning

We are going to install a call centre application.
According to end user, there will be around 500 request
has to be inputted into the system.
We will use SQL Server 2000 as the DB. We would like to
know what factors we have to consider - Like recovery
model, database maintenance plan, fill factors ? Is it
necessary for us to archive some old data to an archive
datbase ?
Thanks"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:1cb701c4b569$d40000a0$a401280a@.phx.gbl...
> We are going to install a call centre application.
> According to end user, there will be around 500 request
> has to be inputted into the system.
>
500 requests over what time period?

> We will use SQL Server 2000 as the DB. We would like to
> know what factors we have to consider - Like recovery
> model, database maintenance plan, fill factors ? Is it
> necessary for us to archive some old data to an archive
> datbase ?
>
Those are really business decisions.
i.e. if you need to run 24x7 vs 9-5x5, your decisions will be different.
If you can do with downtime, you may go with a different decisions on
architecture.
As for archiving, again, that's a business decision. Do you want to archive
data or not?

> Thanks|||Dear Greg,
It should be 500 requests between 9:00am to 5:00pm from
Monday to Friday.
We will make full database backup daily. What is the
difference between full database backup and archive then ?
Thanks|||"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:0fba01c4b574$0e2f4830$a501280a@.phx.gbl...
> Dear Greg,
> It should be 500 requests between 9:00am to 5:00pm from
> Monday to Friday.
500 a day? That's about one a minute. You can run this thing on a desktop
machine.

> We will make full database backup daily. What is the
> difference between full database backup and archive then ?
Generally a backup is for disaster recovery. An archive is for storing data
for later analysis or for other reasons.
For example, I keep backups of only a few days (my databases generally have
enough churn that in a few days the bulk of the data has changed anyway.)
But there's certain data I archive to tape (in a different schema etc) that
I may keep for much longer.
Now, as for once a day backups, that may or may not work. Your database
sounds like it will probably be fairly small to start, so recovery time will
be about the same as backup. i.e. if it takes 10 minutes to backup, it'll
take about 10 minutes to restore. Plus any time to fix up minor issues.
However, let's say you start a backup at 5:01 PM.
What happens if your DB crashes at 5:00 PM. Can you afford to lose a day's
worth of data?

> Thanks
>

Capacity Planning

We are going to install a call centre application.
According to end user, there will be around 500 request
has to be inputted into the system.
We will use SQL Server 2000 as the DB. We would like to
know what factors we have to consider - Like recovery
model, database maintenance plan, fill factors ? Is it
necessary for us to archive some old data to an archive
datbase ?
Thanks
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:1cb701c4b569$d40000a0$a401280a@.phx.gbl...
> We are going to install a call centre application.
> According to end user, there will be around 500 request
> has to be inputted into the system.
>
500 requests over what time period?

> We will use SQL Server 2000 as the DB. We would like to
> know what factors we have to consider - Like recovery
> model, database maintenance plan, fill factors ? Is it
> necessary for us to archive some old data to an archive
> datbase ?
>
Those are really business decisions.
i.e. if you need to run 24x7 vs 9-5x5, your decisions will be different.
If you can do with downtime, you may go with a different decisions on
architecture.
As for archiving, again, that's a business decision. Do you want to archive
data or not?

> Thanks
|||Dear Greg,
It should be 500 requests between 9:00am to 5:00pm from
Monday to Friday.
We will make full database backup daily. What is the
difference between full database backup and archive then ?
Thanks
|||"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:0fba01c4b574$0e2f4830$a501280a@.phx.gbl...
> Dear Greg,
> It should be 500 requests between 9:00am to 5:00pm from
> Monday to Friday.
500 a day? That's about one a minute. You can run this thing on a desktop
machine.

> We will make full database backup daily. What is the
> difference between full database backup and archive then ?
Generally a backup is for disaster recovery. An archive is for storing data
for later analysis or for other reasons.
For example, I keep backups of only a few days (my databases generally have
enough churn that in a few days the bulk of the data has changed anyway.)
But there's certain data I archive to tape (in a different schema etc) that
I may keep for much longer.
Now, as for once a day backups, that may or may not work. Your database
sounds like it will probably be fairly small to start, so recovery time will
be about the same as backup. i.e. if it takes 10 minutes to backup, it'll
take about 10 minutes to restore. Plus any time to fix up minor issues.
However, let's say you start a backup at 5:01 PM.
What happens if your DB crashes at 5:00 PM. Can you afford to lose a day's
worth of data?

> Thanks
>

Cant use cursor with SP

Hi

I have a SP and in it I call another SP which returns one row in one column, I need to concatenate the value (varchar) to the query in the first SP.
I tried to use cursor with FAST_FORWARD to fetch the result and concatenate it, but I get an error, here is what I tried:

DECLARE Cur CURSOR FAST_FORWARD
FOR SP_Something @.SomeValue

So is it possible to use cursor on SP ? And if it's possible so how ?

Thanks,

Inon.I don't think so...why not put the result set into a table variable?|||I don't think so...why not put the result set into a table variable?

This also don't work, I get an error when I try to INSERT the result from the inner SP to a table var... so is THIS option possible ??

I just want to add that I have several ways to make this work, but I'm trying to be efficient, this is why I want to use an inner SP and not just make the process in the same big SP, I can also make changes in the code (PHP) but I'm trying to make it work this way.

Thanks,

Inon.|||doooh

Yup...can't do that...but you can do

USE Northwind
GO

SET NOCOUNT ON
GO

CREATE PROC mySproc99
AS
SELECT OrderId FROM Orders
GO

CREATE TABLE #myTemp99(OrderId int)

INSERT INTO #myTemp99(OrderId) EXEC mySproc99

SELECT * FROM #myTemp99
GO

SET NOCOUNT ON
DROP TABLE #myTemp99
DROP PROC mySproc99
GO|||doooh

Yup...can't do that...but you can do

USE Northwind
GO

SET NOCOUNT ON
GO

CREATE PROC mySproc99
AS
SELECT OrderId FROM Orders
GO

CREATE TABLE #myTemp99(OrderId int)

INSERT INTO #myTemp99(OrderId) EXEC mySproc99

SELECT * FROM #myTemp99
GO

SET NOCOUNT ON
DROP TABLE #myTemp99
DROP PROC mySproc99
GO

Hmmm... there goes the efficient part... :)

Inon.|||Well...

I don't think (I hate when that happens) that I would ever use effecient and cursor in the same sentence...

Unless it was like

"I wish the developer wrote effecient code instead of using a cursor"

:D|||Well...

I don't think (I hate when that happens) that I would ever use effecient and cursor in the same sentence...

Unless it was like

"I wish the developer wrote effecient code instead of using a cursor"

:D

I agree, didn't mean to express discontent of your solution BTW...

You see, the DB is for a web page, that SP will be called many times.

Thanks for the help,

Inon.|||I'm not sure what you're trying to do, but it looks like you want to use a SP to return a varchar that contains a query that you then want to execute - am I right ?

If so, try making the SP a function, viz:

create function dbo.some_func (@.inp_value char(?))
returns varchar(100)
as
begin
declare @.temp_var varchar(100)

set @.temp_var = 'select * from ' + @.inp_value
return @.temp_var

end

then in your calling sp:

select @.mysql = some_func(@.parm)
execute(@.mysql)

HTH|||I'm not sure what you're trying to do, but it looks like you want to use a SP to return a varchar that contains a query that you then want to execute - am I right ?

Almost right, the inner SP returns only a string that will be concatenate to the query in the outer SP, meaning, the inner SP returns only a part of a query, not entire query.

I have a SP that returns a result, I want to use this result in another SP, I didn't want to run both queries because (and correct me if I'm wrong) I know that for optimal performance it's not recommended to run two separated queries on two table in one SP, because the optimizer will confuse with the best execution plans for each query...

Anyway, this is what I finally did, ran both in one SP... is it as bad as I think ?? And should I just make some process in the code and just run two separated SPs? I wanted to make it in one connection session since it will run many many times and by using one SP I cut traffic (for this option only of course) in 50%...

So what do you say is better? One SP with two queries or two SP's with two connections? (But each has its own optimal execution plan).

Thanks,

Inon.