SP Union SP?

[MVisconte]

Hi gang.

I have a longish parameterized query consisting of several unions of bi-drectional outer joins. The resultant query just misses fitting into a single stored procedure (certain combinations of the parameters yield a nvarchar over 4000 characters). It has been suggested that I split the query in the sp into two parts, each in its own stored procedure, and then make a master stored procedure that executes both "mini" queries and unions THOSE together.

Interesting theory, but I can't get a union on the sp's to work. Is it me, or is it MS SQL2k?

Final segment of the master sp looks like:

exec usp_AllDat_p1 @dbowner, @table_name, @field_name
union
exec usp_AllDat_p2 @dbowner, @table_name, @field_name

where the @param are passed to the master upon calling it. Each "partial" sp runs as expected.

Any ideas?

 

[zhavic]

Create destination temp table ( with the same structure as stored procedure returns ), and then insert rows from stored procedures into that table

CREATE TABLE #destination ( ..... )

INSERT INTO #destination
EXECUTE usp_AllDat_p1 @dbowner, @table_name, @field_name

INSERT INTO #destination
EXECUTE usp_AllDat_p2 @dbowner, @table_name, @field_name

SELECT * FROM #destination

Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[MVisconte]

Argh! (Matey!)

The dba has told me that I can INSERT INTO #temptable directly from my first SELECT statement, to keep from having to know, a priori, what field type/size the user will run against the SP.

I certainly can't figure it out. Does that ring a bell?

The basis of the whole thing is I need to be able to hand the sp a table name and particular field name, chosen by user at run time, and get results.

Sigh. My head hurts.

 

[zhavic]

Ok, try it this way:

I assume that you have in your first procedure usp_AllDat_p1 some SELECT statement which creates the result.
So you can that result put into #temptable in usp_AllDat_p1,
after than call usp_AllDat_p2 ( directly from usp_AllDat_p1 ).

So you will have something like this in first procedure:

CREATE usp_AllDat_p1
@dbowner blahblah,
@table_name blahblah,
@field_name blahblah

...
some code
...

/* result from first procedure */
SELECT some_columns
INTO #temptable
FROM some_table

/* call second procedure and put the result from it into #temptable
INSERT INTO #temptable
EXECUTE usp_AllDat_p2 @dbowner, @table_name, @field_name

/* final result */
SELECT * FROM #temptable


Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[hneal98]

For your temp table, you can set it up with ##tmpTableName. Using the ##, makes it visible like a global variable, to both procedures.

 

[Bygbobbo]

You can avoid all that extra hokus - pokus.

In your original query you mentioned that you may excede 4000 char.

If you are dynamically creating your query, break it up into segments that you know will not execede any thing

For example:

set @sql_select = 'Select <field1>,<field2>,<fieldN>... '
set @sql_from = 'from <table1> Join <table2> Join <tableN>... '
set @sql_criteria = 'where 0 = 0 and ... '

Exec(@sql_select + @sql_from + @sql_criteria)

The sql scripting engine executes the strings allowing you to exceed the limitations of data types.

I have some queries that generate over 50,000 characters and execute fine.

Look up Exec in the help menu in query analyzer it states the same thing and is a great resource for some other goodies.

Bygs

 

[MVisconte]

Bygbobbo, et al,

Thanks for the replies. I have tried various things to the point that I'm thinking that a career in high school counseling might be a nice, relaxing, change. (grin)

Status:
My temp table adventures have not been successful, and, due to some recent internal politics, I cannot call upon the DBA resources has I have. Politics? Sigh.

My attempt at using the EXEC w/ variables has fallen flat... the SQL BOL states that there are only a couple of things one cannot do w/ the EXEC sp_executesql @whatever, and one of those things is complex SQL, and another is string query concatenation. Argh.

I have tried
EXEC(@String1 + @String2)
and,
EXEC sp_executesql (@String1 + @String2)
both to no avail.

I even tried to find a way to make the whole thing reside inside another query as a derived table -- but no joy. I couldn't get the main (huge) query to execute as a string.

Next question:

Is there a (reasonably) simple way to do a union of resultsets from stored procedures? I can cut the HUGE query into three sub-components (or even two, I don't really need three) and just pass the params to each sp.

 

[MVisconte]

I know it's a pain for some to look at, but, if you guys think it would be useful, I can sanitize my query and post it. Maybe there's a better way of getting results from remote sites than using OPENROWSET.

--
Marc Visconte
CSC
Lead RMS Developer
Crystal Reports

 

[zhavic]

Ok, so, you sad, that structure of returned table is based on parameters passed to the stored procedure.

Can you create some table ( in this time, it is not important if it will be temp table or something else )
which will contains all possible columns that can by in the result ?

If yes, there may be the way using 'table variable' for example. ( based on your first post I assume you have MS SQL 2000 )

You can post your query there, it can help to solve this.

Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[MVisconte]

I will have to transfer the query from the other server (serparate network, no interconnectivity).

The fields returned (and hence the structure) is determined by the parameters. That way, instead of needing to update the query for every table (this field for this table, that field or those fields for that table, etc.), I just pass the table name and one or two fields (two diff't SPs) in text form, and they are pushed into the string and interpreted as fields - type almost doesn't matter.

The alternative is find/replace on queries, which I then would try to run from Crystal Reports (and I've never run SQL .qry-ies from CR). Whew.



--
Marc Visconte
CSC
Lead RMS Developer
Crystal Reports

 

[MVisconte]

Well, here is the &quot;sanitized&quot; version. It's not pretty, but I had to substitute very generic names for the individual sites, and of course, the account and pw.
HQSvr = the main (local) server, that everything is being compared TO
Site_1 ... - ... Site_4 -- Generic names for the four servers at four remote locations. These and &quot;HQSvr&quot; above show up as the servers that the queries are run against, and as text strings in the output. The results should look like:
[tt]
CheckData HQSvr Site_1 Site_2 Site_3 Site_4
--------- ----- ------ ------ ------ ------
Item 1 HQSvr
Item 2 HQSvr
Item 3 HQSvr+ Site_3-
Item 4 HQSvr- Site_1+ Site_3-
Item 5 HQSvr- Site_2+ Site_3+
Item 6 HQSvr[/tt]
...etc.

The emphasis shows up in the report. The appended &quot;+&quot; or &quot;-&quot; shows that the site (column) has an item missing from the HQ site or vice-versa.

Hope that is understandable.

Query, in all its bulk, appears below:


USE model
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'usp_AllDat_l_y' AND type = 'P')
DROP PROCEDURE usp_AllDat_l_y
GO
USE model
GO

create proc usp_AllDat_l_y
-- examples to run against:
-- usp_AllDat_l_y dbo, tm_location_db, location_state_country
-- usp_AllDat_l_y dbo, Group_x, Group_name

@DBO_Name_to_compare varchar(50) --i.e., dbo, or ARAdmin -
-- ONLY HQ, and ONLY certain tables
,@Table_name_to_compare varchar(100) --i.e., Tm_Location_Db
,@Field_name_to_compare nvarchar(50) --i.e., location_state_country
with encryption
as

-- Declare the string variables:
declare @SQL nvarchar(4000)
declare @HQSvr_as_a nvarchar(190)

declare @OJ_1 nvarchar(95)
declare @OJ_2 nvarchar(120)

declare @LOJ_as_b_Svr1 nvarchar(210)
declare @ROJ_as_b_Svr1 nvarchar(210)

declare @LOJ_as_b_Svr2 nvarchar(210)
declare @ROJ_as_b_Svr2 nvarchar(210)

declare @LOJ_as_b_Svr3 nvarchar(210)
declare @ROJ_as_b_Svr3 nvarchar(210)

declare @LOJ_as_b_Svr4 nvarchar(210)
declare @ROJ_as_b_Svr4 nvarchar(210)

declare @On_a_b nvarchar(120)
declare @A_null nvarchar(90)
declare @B_null nvarchar(90)

declare @Select_a nvarchar(100)
declare @Select_b nvarchar(100)

-- Build the string components:
set @Select_a = 'select a.'+rtrim(@Field_name_to_compare) +' as CheckData'
set @Select_b = 'select b.'+rtrim(@Field_name_to_compare) +' as CheckData'

set @HQSvr_as_a = 'FROM OPENROWSET(''MSDASQL'', ''DRIVER={SQL Server};
SERVER=HQSvrRMS;UID=RptAct;PWD=RptActPwd'',
ARSystem.'+ rtrim(@DBO_Name_to_compare)+'.'+
rtrim(@Table_name_to_compare)+') as a'

set @OJ_1 = 'outer join OPENROWSET(''MSDASQL'', ''DRIVER={SQL Server};
SERVER='
set @OJ_2 = ';UID=RptAct;PWD=RptActPwd'',
ARSystem.dbo.'+rtrim(@Table_name_to_compare)+') as b'

Set @LOJ_as_b_Svr1 = ' Left ' + @OJ_1 + 'Site1Svr' + @OJ_2
Set @ROJ_as_b_Svr1 = ' right ' + @OJ_1 + 'Site1Svr' + @OJ_2

Set @LOJ_as_b_Svr2 = ' Left ' + @OJ_1 + 'Site2Svr' + @OJ_2
Set @ROJ_as_b_Svr2 = ' right ' + @OJ_1 + 'Site2Svr' + @OJ_2

Set @LOJ_as_b_Svr3 = ' Left ' + @OJ_1 + 'Site3Svr' + @OJ_2
Set @ROJ_as_b_Svr3 = ' right ' + @OJ_1 + 'Site3Svr' + @OJ_2

Set @LOJ_as_b_Svr4 = ' Left ' + @OJ_1 + 'Site4Svr' + @OJ_2
Set @ROJ_as_b_Svr4 = ' right ' + @OJ_1 + 'Site4Svr' + @OJ_2

Set @On_a_b = ' on a.'+ rtrim(@Field_name_to_compare)+
' = b.'+ rtrim(@Field_name_to_compare)
Set @A_Null= ' where a.'+ rtrim(@Field_name_to_compare)+' is null'
Set @B_Null= ' where b.'+ rtrim(@Field_name_to_compare)+' is null'

-- Make the Query:
set @SQL =

@Select_a +
', '' HQSvr'' as HQSvr, '''' as Site_1, '''' as Site_2, '''' '+
'as Site_3, '''' as Site_4 ' +
@HQSvr_as_a
-- Above is FIRST / MAIN recordset: HQSvr _FULL_ listing

+' Union '+

@Select_a +
', '' HQSvr+'' as HQSvr, ''Site_1-'' as Site_1, '''' as Site_2, '''' '+
'as Site_3, '''' as Site_4 ' +
@HQSvr_as_a +
@LOJ_as_b_Svr1 +
@On_a_b +
@B_null
-- Above is HQSvr against Site_1 for records MISSING in Site_1

+' union '+

@Select_b +
', '' HQSvr-'' as HQSvr, ''Site_1+'' as Site_1, '''' as Site_2, '''' '+
'as Site_3, '''' as Site_4 ' +
@HQSvr_as_a +
@ROJ_as_b_Svr1 +
@On_a_b +
@A_null
-- Above is Site_1 against HQSvr for records MISSING in HQSvr

+' union '+

@Select_a +
', '' HQSvr+'' as HQSvr, '''' as Site_1, ''Site_2-'' as Site_2, '''' '+
'as Site_3, '''' as Site_4 ' +
@HQSvr_as_a +
@LOJ_as_b_Svr2 +
@On_a_b +
@B_null
-- Above is HQSvr against Site_2 for records MISSING in Site_2

+' union '+

@Select_b +
', '' HQSvr-'' as HQSvr, '''' as Site_1, ''Site_2+'' as Site_2, '''' '+
'as Site_3, '''' as Site_4 ' +
@HQSvr_as_a +
@ROJ_as_b_Svr2 +
@On_a_b +
@A_null
-- Above is Site_2 against HQSvr for records MISSING in HQSvr

+' Union ' +

@Select_a +
', '' HQSvr+'' as HQSvr, '''' as Site_1, '''' as Site_2, ''Site_3-'' '+
'as Site_3, '''' Site_4 ' +
@HQSvr_as_a +
@LOJ_as_b_Svr3 +
@On_a_b +
@B_null
-- Above is HQSvr against Site_3 for records MISSING in Site_3

+' union '+

@Select_b +
', '' HQSvr-'' as HQSvr, '''' as Site_1, '''' as Site_2, ''Site_3+'' '+
'as Site_3, '''' Site_4 ' +
@HQSvr_as_a +
@ROJ_as_b_Svr3 +
@On_a_b +
@A_null
-- Above is Site_3 against HQSvr for records MISSING in HQSvr

+' Union ' +

@Select_a +
', '' HQSvr+'' as HQSvr, '''' as Site_1, '''' as Site_2, '''' '+
'as Site_3, ''Site_4-'' Site_4 ' +
@HQSvr_as_a +
@LOJ_as_b_Svr4 +
@On_a_b +
@B_null
-- Above is HQSvr against Site_4 for records MISSING in Site_4

+' union '+

@Select_b +
', '' HQSvr-'' as HQSvr, '''' as Site_1, '''' as Site_2, '''' '+
'as Site_3, ''Site_4+'' Site_4 ' +
@HQSvr_as_a +
@ROJ_as_b_Svr4 +
@On_a_b +
@A_null
-- Above is Site_4 against HQSvr for records MISSING in HQSvr


-- Use Below to Test SQL
--print @sql
exec sp_executesql @SQL


-- to test it run the below code without the --
-- usp_AllDat_l_y aradmin,Tm_Action_Type_Db,Action_Type


I'll add a post containing the OUTPUT of the query string (i.e., the concatonated @SQL).

--
Marc Visconte
CSC
Lead RMS Developer
Crystal Reports

 

[MVisconte]

(Partial) Output of SQL. This is the results in @SQL that my convoluted code turns into. I have included only the first three parts, as all the rest are repeats of the second two parts, w/ just server names changed. I provided structured formatting, hoping that it would make the result easier to understand.

usp_AllDat_l_y dbo,Group_x,Group_name yields:
[tt]
SELECT a.Group_name AS CheckDat,
' HQSvr' AS HQSvr,
'' AS Site_1,
'' AS Site_2,
'' AS Site_3,
'' AS Site_4
FROM OPENROWSET('MSDASQL', 'DRIVER={SQL Server};
SERVER=HQSvrRMS;UID=RptAct;PWD=RptActPwd',
ARSystem.dbo.Group_x) AS a

UNION

SELECT a.Group_name AS CheckDat,
' HQSvr+' AS HQSvr,
'Site_1-' AS Site_1,
'' AS Site_2,
'' AS Site_3,
'' AS Site_4
FROM OPENROWSET('MSDASQL', 'DRIVER={SQL Server};
SERVER=HQSvrRMS;UID=RptAct;PWD=RptActPwd',
ARSystem.dbo.Group_x) AS a
LEFT OUTER JOIN
OPENROWSET('MSDASQL', 'DRIVER={SQL Server};
SERVER=Site_1;UID=RptAct;PWD=RptActPwd',
ARSystem.dbo.Group_x) AS b
ON a.Group_name = b.Group_name
WHERE b.Group_name is null

UNION

SELECT b.Group_name AS CheckDat,
' HQSvr-' AS HQSvr,
'Site_1+' AS Site_1,
'' AS Site_2,
'' AS Site_3,
'' AS Site_4
FROM OPENROWSET('MSDASQL', 'DRIVER={SQL Server};
SERVER=HQSvrRMS;UID=RptAct;PWD=RptActPwd',
ARSystem.dbo.Group_x) AS a
RIGHT OUTER JOIN
OPENROWSET('MSDASQL', 'DRIVER={SQL Server};
SERVER=Site_1;UID=RptAct;PWD=RptActPwd',
ARSystem.dbo.Group_x) AS b
ON a.Group_name = b.Group_name
WHERE a.Group_name IS NULL

UNION
...
[/tt]

The first part generates a full list of what exists on the main server.
The second part gets a list of items on the main server that DON'T exits on the Site_1 server.
The third part gets a list of items on the Site_1 server that DON'T exist on the main server. This leads to some records that look redundant, but this is later collapsed in reporting.
And, of course, parts 2 & 3 are repeated over and over substituting in Site_2, Site_3, and Site_4. Whew.

Amazingly, when it runs, it seems to do a good job of getting what we need.

Hope this helps you see what I'm trying to do (and maybe figure out a better way).


--
Marc Visconte
CSC
Lead RMS Developer
Crystal Reports

 

[zhavic]

Hmmmmmm, without temp table ? Funny

Could you create normal table ( not temp ) with unique name and than drop it on the end of procedure ?

If yes, than this will be simple.

If not, do you have rights to create functions in your procedure ? ( total funny way but could work )


Zhavic



---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[MVisconte]

Well, I don't have the resources of the DBA any more. He gave me some code that should have made temp tables (but didn't). As I am running these queries on my developement machine, I can set up whatever priveleges I need. Once they get their data synced between sites, I can talk to them about making these queries available at the other sites (not necessarily for the same purpose), and I can use whatever code for other reports requiring unions/joins from the main site to the remote sites. (Can you say global reports, boys and girls? I knew you could.)

Just to be on the safe side (and because I got tired of needing to re-run all the sp's every time I booted my machine), I made my own reporting database -- just to hold views and sp's that look at other sites.

Hm... functions in my procedures... I wasn't aware that was available (I thought that's what the sp's were) but I can see how it would clean my hacked @SQL= up tremendously. Sigh. Back to Books OnLine. Maybe I'll work on one of the major comparison reports (off a view?) first. So many things... all of &quot;highest importance&quot;.

--
Marc Visconte
CSC
Lead RMS Developer
Crystal Reports

 

[MVisconte]

Whoa! Dude!

&quot;Inline User-Defined Functions

Inline user-defined funtions are a subset of user-defined functions that return a table. Inline functions can be used to achieve the functionality of parameterized views.&quot;

WHOA! THIS may be what I've been looking for. The DBA helped me hack code to make union/join views from the main system to all the sites, and I can report off of views in Crystal fairly easily. If I can use this and hack the view code he gave me, I can either:
do parameterized views (from Crystal???) or
automate view generation through parameters -- which is not the ultimate solution, but it's a darned GOOD one.



--
Marc Visconte
CSC
Lead RMS Developer
Crystal Reports

 

[MVisconte]

Crap. Sigh...

Seems that NONE of the stuff that *I* want to do, is allowed.

Back to the drawing board...

--
Marc Visconte
CSC
Lead RMS Developer
Crystal Reports

 

[zhavic]

So that means, in that stored procedure:

1. you can't using temp tables -- OK
2. you can't create normal tables -- Why ?

And what about this:
create user functions or user stored procedures ( during execution of your procedure ), I mean this:

in your procedure try run this:

DECLARE @cSQL varchar(1000)

SET @cSQL = 'CREATE FUNCTION fn_my_function () RETURNS integer
BEGIN
RETURN 1
END'

EXECUTE ( @cSQL )


Try to run it in your procedure ( that functions will be needed only on that server, on which you run your stored procedure )


Post there if it can be done.

Zhavic



---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[MVisconte]

Zavic,

Didn't get a chance to test that. Wound up taking a less-desired way out: I made views of each join -- didn't run into any size limitation.

The down side is,

I need a view for each query that I run... I can't use a single Crystal Report to report on any set of tables (using parameters to feed the Stored Procedure),


If I need to change the query, I have to change each and every one of the views -- or, because I used a stored procedure to make the views, I have to RUN the blamed thing for each view.

But, at least I have some reports. Sheesh. NOW they want me to put a PACKAGE together to distrubute to the other sites... how they heck are they going to understand everything that I've been through in order to run these things? Oh well. To quote a Peter Gabrial song off of the Sledgehammer album, &quot;We do what we're told...&quot;

;-)


--
Marc Visconte
CSC
Lead RMS Developer
Crystal Reports