Joing two queries into one procedure

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

SQL Server 2000 Forums

SQL Server Development (2000)

Joing two queries into one procedure

Author

Topic

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2006-06-27 : 10:20:19

Hi guys I have two queries that return the required results when I run them both individually in query analyser but when I join them into one procedure I only get the one queries results back and not the last ones, is there any condition in the first query that prevents the last one from sho

wing results please ?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-27 : 10:23:40

Select query1
Union all
Select query2

Madhivanan

Failing to plan is Planning to fail

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2006-06-27 : 11:03:13

cheers Madhi
I thought that would work but I have a stored procedure that has a number of inserts and bulk inserts based on the contents of a xp_cmdshell I then would like to show the user a count of totals imported into the aforemetioned inserted tables using a cross join but this cross join doesn't show results within the same procedure(even after I put a Union All in).
It only shows the results if I run it seperatley ???

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-06-27 : 11:55:20

Are the two queries adjacent to each other or is there any logic in between them ?

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2006-06-27 : 12:04:24

well the first is inserting into a couple of tables based on certain fixed conditions and the second is just trying to report out what has been inserted which doesnt seem to work when they are merged together!

so to answer your question there is no logic in between them!

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-06-27 : 12:33:42

This is a great time to post some sample code!?

rockmoose

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2006-06-27 : 17:06:46

No problem Rock Man, this is going to upset Nigel(NR) because he has tryed to steer me clear of this cursor but I find it the most un complicated way to go about what I am trying to do, so to Nigel I apologise.Here it is:

ALTER PROCEDURE InsertItemsales
as
SET NOCOUNT on
DECLARE @exists as varchar(300)
DECLARE @result int
DECLARE @date as varchar(100)
DECLARE @sql as varchar(200)
DECLARE @storeno as smallint
DECLARE storecursor CURSOR FOR
Select store_no From
retail2 Where store_no in ( 208,189,111,181,270,204,206,182,185,201,112,110,272,209,103,108,279,109,278,104,203,186,202,180,281,271,276,273,207,280,183,117,211,114,190,113,105,210,274,118,205,184)

DROP TABLE dbo.tmpisaleimport
CREATE TABLE dbo.tmpisaleimport ([salesdata] [char] (56))
DROP TABLE dbo.itemsales_stores_input_error
CREATE TABLE [itemsales_stores_input_error] (
[StoreNumber] [smallint] NULL ,
[DateInserted] [datetime] NULL CONSTRAINT [DF__itemsales__DateI__43F17C88] DEFAULT (getdate())
) ON [PRIMARY]
OPEN storecursor
FETCH NEXT FROM storecursor INTO @storeno
WHILE @@FETCH_STATUS = 0

BEGIN
select
@date = cast(datediff(day,min(date),getdate()) as varchar(3))
from
dbo.DatesToPeriods_Virtual
where
period = 1
and week = 1
and finyear in
(select
finyear
from
dbo.DatesToPeriods_Virtual
where
date > DATEADD(day, -1, getdate()))

select @date = right('000' + convert(varchar(10), @date), 3)
--find if the current isale file exists in the itemsale folder
select @exists = '"' + 'DIR /B \\servername\Itemsale\isale'+ cast(@storeno as char(3))+'.'+@date+'"'
EXEC @result = master..xp_cmdshell @exists
-- if it does then bulk insert it into the tmpisaleimport table
IF (@result = 0)
begin
select @date =''''+ '\\servername\Itemsale\isale'+ cast(@storeno as char(3))+'.'+@date+''''
Set @sql='
BULK INSERT [tmpisaleimport]
FROM '+@date
Exec(@sql)
end
ELSE
-- if it doesn't plug it into the the error table which shows all failed store imports
begin
INSERT INTO itemsales_stores_input_error(storenumber)
VALUES (@storeno)
end

FETCH NEXT FROM storecursor INTO @storeno
END

CLOSE storecursor
DEALLOCATE storecursor
GO

SELECT * FROM
(
SELECT count(*) as [Number of Failed Imports]
FROM dbo.itemsales_stores_input_error
) a
cross join
(SELECT count(distinct substring(salesdata, 10, 3)) as [Total Store Numbers Imported]
FROM dbo.tmpisaleimport
) b
GO

The cross join query that follows the first GO is what is not getting displayed when this sproc is run but if I split it up into two sproc's it works 100%.
Any ideas would be muchly appreciatted ?

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-06-27 : 17:18:31

Remove the first GO.
In QA the GO separates the sql batches.
So the cross join query doesn't go into the the InsertItemsales procedure.

Can it be that simple?

PS.
btw, I like to use the undocumented xp_fileexists for checking file existance, but that's me,
and your way is at least documented.

rockmoose

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2006-06-27 : 17:31:09

I tried that Rock Man initially but that did not work the first Go was just put there in the hope to get the second batch to display but that did not work either
Thanks for the xp_fileexists advice though, cheers.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-06-27 : 17:50:48

Are you sure the last cross join is in the proc?

-- double check
exec sp_helptext 'InsertItemsales'

Do the tables contain any data atm?

I would resort to put simple debug statements in the proc as:
"select @@rowcount as [rows bulk inserted into tmpisaleimport]"
"select @@rowcount as [rows inserted into itemsales_stores_input_error]"
just to check that it is running as expected.

rockmoose

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2006-06-27 : 18:07:46

ok mate will have to check this out tomorrow when i am back at the ranch under a live environment so to speak and let you know.
I am sure that the last cross join is in the sproc though and I am sure that the correct tables are getting inserted into because if I run this cross join seperatley after the sproc I get the desired results ,When I run this alter proc though i always get the cross join results but when I execute the proc I only get the results from the xp_cmdshell xsproc for each of the 42 stores either exsisting or not and no results from the cross join underneath ,
I will have to check this more thoroughly tomorrow but will let you know my findings, It aint no show stopper though just frustrating.
Thanks for your time and advice though RockMoose, cheers.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-06-27 : 18:26:28

If you cross join a table with 0 rows, the you get 0 rows back.
But here that doesn't seem that it can be the problem, since you are using count in both derived tables.
And count always returns 1 row, whether there are rows in the table or not.

Strange, GL tomorrow.

rockmooe

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2006-06-27 : 18:39:06

til tomorrow ...

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-06-28 : 05:48:12

I think ROCK is right...better try to get rid off all the go statements...why don't you go for the simple Begin...End approach.
I feel that last query is simply not the part of sp. You can add some print statements to check whether sp contains all the statements which u assume it contains...to find the real end point of the sp.

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2006-06-28 : 07:24:26

cheers Rock and Harsh as you suggested after taking a 2nd look and removing the last GO I do get this query in my sproc but because there are so many of these:
OUTPUT
isale102.150
NULL
OUTPUT
isale103.150
NULL
OUTPUT
isale104.150
NULL
OUTPUT
isale105.150
NULL
etc etc.....

I only get my results right at the bottom of the screen.
Does anyone know how to remove the above information please ?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-06-28 : 08:02:10

Why do u bother?

If u are accessing ur SP from front-end, anyway, u are going to get the last resultset only.

Harsh Athalye
India.
"Nothing is Impossible"

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-06-28 : 09:06:17

It's because of the xp_cmdshell and dir command.
suggestions:
a) use xp_fileexists instead
b) do 1 dir command "DIR /B \\servername\Itemsale\isale*" and input the data from that dir into a temporary table.
You can use that temporary table to determine if the file exists.
c) use no_output flag for the xp_cmdshell.

but really, I think it would be better to do this "background" processing as a scheduled job.
Is there another process that takes care of the data in tmpSaleImport table after running this?
The results of this processing should be logged in tables, and the info in those tables should be accessible from the front-end.

More, instead of drop table, use truncate table.

" Why do u bother?
If u are accessing ur SP from front-end, anyway, u are going to get the last resultset only."
Maybe because he's trying to actually develop software, not doing sunday hacking!!!

rockmoose

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-06-28 : 09:13:50

Hey rock, what do u mean by sunday hacking ??

Harsh Athalye
India.
"Nothing is Impossible"

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-06-28 : 10:01:48

quote:
Originally posted by harsh_athalye

Hey rock, what do u mean by sunday hacking ??

I mean that "Why do u bother?"
is very unprofessional.
and
"If u are accessing ur SP from front-end, anyway, u are going to get the last resultset only."
is wrong.

rockmoose

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-28 : 10:05:30

>>If u are accessing ur SP from front-end, anyway, u are going to get the last resultset only.

No you will get multiple recordsets if any and you can use next recordset using NextRecordset property of Recordset

Madhivanan

Failing to plan is Planning to fail

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2006-06-28 : 10:56:37

The No_output flag does the trick RockMoose exactley what i was after thank you sincerely.
The temp table idea is what NR wanted me to do similar to this link :

http://www.nigelrivett.net/SQLTsql/ImportTextFiles.html

I chose not to do this as I liked the idea of scrolling through each of our 40 stores and checking them individually, I know this is not preferable from a performance point of view but it just didn't seem as complicated as the temp table idea and it only takes 10 seconds to run so I went for what I thought was a easy option.

RockMoose you are legend.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources