OUTPUT and Recordset

I have created a 'Intelligent' search stored procedure that accepts two input parameters SearchFor and SearchIn and an OUTPUT parameter @.Result

Basically it starts by looking for a match for the SearchFor and if more than 1 is found, returns a recordset of the matches so the user can select a SearchFor category, at the same time the OUTPUT parameter is set to let the calling script know what is being returned.

Likewise it does the same for the SearchIn until both are satisfied and the true search results recordset can be returned.

Is it possible to get an OUTPUT parameter AND a recordset at the same time?

Any help will be appreciated,

PhlazarI would put the results of the stored procedure into a global temp table....

Cheers
C

Outer table inside stored procedure

Hi,
I am using SQL Server 2000
I need to select values from a table that belongs to other database from
within a stored procedure. Hardcoding table's fully qualified name is not
appropriate. Table's database name must be a parameter of a stored procedure
.
Is there any better way than using dynamic query string generation? If query
is a dynamic string it is difficult to mainain it.
Thanks in advance.You could create a view which includes the database name - then when you nee
d
to change the database just change the view and all the SPs will reference
tthe correct database.
"Alexander Korol" wrote:

> Hi,
> I am using SQL Server 2000
> I need to select values from a table that belongs to other database from
> within a stored procedure. Hardcoding table's fully qualified name is not
> appropriate. Table's database name must be a parameter of a stored procedu
re.
> Is there any better way than using dynamic query string generation? If que
ry
> is a dynamic string it is difficult to mainain it.
> Thanks in advance.|||Sorry I made a multipost. Was not intended to just missed the window :) .
From now on let's refer to "How to refer from a stored procedure to a table
in another database"
Thanks for your reply.
I can not do anything beforehand. In the runtime my procedure can get only
the name of a database of guaranteed structure. Thereis a way to change the
database name in the view from stored procedure in the runtime ALTER VIEW. I
s
that what you ment? I will try.|||Dynamic altering the view helped. Thanks.
A very simple example:
exec('ALTER VIEW ViewName AS SELECT * FROM ' + @.NM_DATABASE +
'.dbo.TableName')

OUTER JOIN issue

The following stored procedure was created by one of our developers and it's
taking too long to be executed, we already identified that the section of th
e
coding with the problem are the outer joins, can anybody suggest something t
o
improve it?
Thanks
-- Populate performance layer table with latest data
----
SET @.StepCount = @.StepCount + 1
SET @.StepName = 'Populate performance layer table - part 1'
EXEC @.StepID = EDW_Manager.adm.get_Step_Reference @.ProcessID, @.StepName,
@.StepCount
SELECT isnull(isnull(isnull(poobd.clinical_hierarchy_ward_did,
unknown.clinical_hierarchy_ward_did), sday.clinical_hierarchy_ward_did),
sep.clinical_hierarchy_ward_did) as clinical_hierarchy_ward_did,
isnull(isnull(isnull(poobd.clinical_hierarchy_unit_did,
unknown.clinical_hierarchy_unit_did), sday.clinical_hierarchy_unit_did),
sep.clinical_hierarchy_unit_did) as clinical_hierarchy_unit_did,
isnull(isnull(isnull(poobd.cost_centre_did, unknown.cost_centre_did),
sday.cost_centre_did), sep.cost_centre_did) as cost_centre_did,
isnull(isnull(isnull(poobd.charge_cost_centre_did,
unknown.charge_cost_centre_did), sday.charge_cost_centre_did),
sep.charge_cost_centre_did) as charge_cost_centre_did,
isnull(isnull(isnull(poobd.budget_date_did, unknown.budget_date_did),
sday.budget_date_did), sep.budget_date_did) as budget_date_did,
isnull(isnull(isnull(poobd.initiative_did, unknown.initiative_did),
sday.initiative_did), sep.initiative_did) as initiative_did,
isnull(unknown.measure_UNKNOWN, 0) as measure_UNKNOWN,
isnull(poobd.measure_POOBD, 0) as measure_POOBD,
isnull(sday.measure_SDAY, 0) as measure_SDAY,
isnull(sep.measure_SEP, 0) as measure_SEP
INTO #activity_measures_part_1
FROM @.measure_UNKNOWN unknown
FULL OUTER JOIN @.measure_POOBD poobd
ON unknown.clinical_hierarchy_ward_did =
poobd.clinical_hierarchy_ward_did and
unknown.clinical_hierarchy_unit_did =
poobd.clinical_hierarchy_unit_did and
unknown.cost_centre_did = poobd.cost_centre_did and
unknown.charge_cost_centre_did = poobd.charge_cost_centre_did and
unknown.budget_date_did = poobd.budget_date_did and
unknown.initiative_did = poobd.initiative_did
FULL OUTER JOIN @.measure_SDAY sday
ON sday.clinical_hierarchy_ward_did =
poobd.clinical_hierarchy_ward_did and
sday.clinical_hierarchy_unit_did =
poobd.clinical_hierarchy_unit_did and
sday.cost_centre_did = poobd.cost_centre_did and
sday.charge_cost_centre_did = poobd.charge_cost_centre_did and
sday.budget_date_did = poobd.budget_date_did and
sday.initiative_did = poobd.initiative_did
FULL OUTER JOIN @.measure_SEP sep
ON sep.clinical_hierarchy_ward_did =
poobd.clinical_hierarchy_ward_did and
sep.clinical_hierarchy_unit_did =
poobd.clinical_hierarchy_unit_did and
sep.cost_centre_did = poobd.cost_centre_did and
sep.charge_cost_centre_did = poobd.charge_cost_centre_did and
sep.budget_date_did = poobd.budget_date_did and
sep.initiative_did = poobd.initiative_did
EXEC @.Error = EDW_Manager.adm.log_Step_Results @.StepID, @.@.ERROR, @.@.ROWCOUNT
IF (@.Error <> 0) GOTO LBL_Abort
SET @.StepCount = @.StepCount + 1
SET @.StepName = 'Populate performance layer table - part 2'
EXEC @.StepID = EDW_Manager.adm.get_Step_Reference @.ProcessID, @.StepName,
@.StepCount
SELECT isnull(isnull(isnull(soobd.clinical_hierarchy_ward_did,
wsep9.clinical_hierarchy_ward_did),ptobd.clinical_hierarchy_ward_did),stobd.
clinical_hierarchy_ward_did) as clinical_hierarchy_ward_did,
isnull(isnull(isnull(soobd.clinical_hierarchy_unit_did,
wsep9.clinical_hierarchy_unit_did),ptobd.clinical_hierarchy_unit_did),stobd.
clinical_hierarchy_unit_did) as clinical_hierarchy_unit_did,
isnull(isnull(isnull(soobd.cost_centre_did,
wsep9.cost_centre_did),ptobd.cost_centre_did),stobd.cost_centre_did) as
cost_centre_did,
isnull(isnull(isnull(soobd.charge_cost_centre_did,
wsep9.charge_cost_centre_did),ptobd.charge_cost_centre_did),stobd.charge_cos
t_centre_did) as charge_cost_centre_did,
isnull(isnull(isnull(soobd.budget_date_did,
wsep9.budget_date_did),ptobd.budget_date_did),stobd.budget_date_did) as
budget_date_did,
isnull(isnull(isnull(soobd.initiative_did,
wsep9.initiative_did),ptobd.initiative_did),stobd.initiative_did) as
initiative_did,
isnull(soobd.measure_SOOBD, 0) as measure_SOOBD,
isnull(wsep9.measure_WSEP9, 0) as measure_WSEP9,
isnull(ptobd.measure_PTOBD, 0) as measure_PTOBD,
isnull(stobd.measure_STOBD, 0) as measure_STOBD
INTO #activity_measures_part_2
FROM @.measure_SOOBD soobd
FULL OUTER JOIN @.measure_WSEP9 wsep9
ON wsep9.clinical_hierarchy_ward_did =
soobd.clinical_hierarchy_ward_did and
wsep9.clinical_hierarchy_unit_did =
soobd.clinical_hierarchy_unit_did and
wsep9.cost_centre_did = soobd.cost_centre_did and
wsep9.charge_cost_centre_did = soobd.charge_cost_centre_did and
wsep9.budget_date_did = soobd.budget_date_did and
wsep9.initiative_did = soobd.initiative_did
FULL OUTER JOIN @.measure_PTOBD ptobd
ON ptobd.clinical_hierarchy_ward_did =
soobd.clinical_hierarchy_ward_did and
ptobd.clinical_hierarchy_unit_did =
soobd.clinical_hierarchy_unit_did and
ptobd.cost_centre_did = soobd.cost_centre_did and
ptobd.charge_cost_centre_did = soobd.charge_cost_centre_did and
ptobd.budget_date_did = soobd.budget_date_did and
ptobd.initiative_did = soobd.initiative_did
FULL OUTER JOIN @.measure_STOBD stobd
ON stobd.clinical_hierarchy_ward_did =
soobd.clinical_hierarchy_ward_did and
stobd.clinical_hierarchy_unit_did =
soobd.clinical_hierarchy_unit_did and
stobd.cost_centre_did = soobd.cost_centre_did and
stobd.charge_cost_centre_did = soobd.charge_cost_centre_did and
stobd.budget_date_did = soobd.budget_date_did and
stobd.initiative_did = soobd.initiative_did
EXEC @.Error = EDW_Manager.adm.log_Step_Results @.StepID, @.@.ERROR, @.@.ROWCOUNT
IF (@.Error <> 0) GOTO LBL_Abort
select count(*) from #activity_measures_part_1
select count(*) from #activity_measures_part_2
SET @.StepCount = @.StepCount + 1
SET @.StepName = 'Populate performance layer table - final'
EXEC @.StepID = EDW_Manager.adm.get_Step_Reference @.ProcessID, @.StepName,
@.StepCount
INSERT INTO EDW_reporting.whs.PERF_activity_budget_ward_by_measure
(clinical_hierarchy_ward_did,
clinical_hierarchy_unit_did,
cost_centre_did,
charge_cost_centre_did,
budget_date_did,
initiative_did,
measure_UNKNOWN,
measure_POOBD,
measure_SDAY,
measure_SEP,
measure_SOOBD,
measure_WSEP9,
measure_PTOBDs,
measure_STOBDs,
batch_id)
SELECT isnull(part1.clinical_hierarchy_ward_did,
part2.clinical_hierarchy_ward_did) as clinical_hierarchy_ward_did,
isnull(part1.clinical_hierarchy_unit_did,
part2.clinical_hierarchy_unit_did) as clinical_hierarchy_unit_did,
isnull(part1.cost_centre_did, part2.cost_centre_did) as
cost_centre_did,
isnull(part1.charge_cost_centre_did, part2.charge_cost_centre_did) as
charge_cost_centre_did,
isnull(part1.budget_date_did, part2.budget_date_did) as
budget_date_did,
isnull(part1.initiative_did, part2.initiative_did) as initiative_did,
isnull(part1.measure_UNKNOWN, 0) as measure_UNKNOWN,
isnull(part1.measure_POOBD, 0) as measure_POOBD,
isnull(part1.measure_SDAY, 0) as measure_SDAY,
isnull(part1.measure_SEP, 0) as measure_SEP,
isnull(part2.measure_SOOBD, 0) as measure_SOOBD,
isnull(part2.measure_WSEP9, 0) as measure_WSEP9,
isnull(part2.measure_PTOBD, 0) as measure_PTOBD,
isnull(part2.measure_STOBD, 0) as measure_STOBD,
@.BatchID
FROM #activity_measures_part_1 part1
FULL OUTER JOIN #activity_measures_part_2 part2
ON part1.clinical_hierarchy_ward_did =
part2.clinical_hierarchy_ward_did and
part1.clinical_hierarchy_unit_did =
part2.clinical_hierarchy_unit_did and
part1.cost_centre_did = part2.cost_centre_did and
part1.charge_cost_centre_did = part2.charge_cost_centre_did and
part1.budget_date_did = part2.budget_date_did and
part1.initiative_did = part2.initiative_did
EXEC @.Error = EDW_Manager.adm.log_Step_Results @.StepID, @.@.ERROR, @.@.ROWCOUNT
IF (@.Error <> 0) GOTO LBL_AbortWithout the table structures, index details and size of tables it is very
difficult to say what is wrong - but it may be worth running it through the
index tuning wizard to see what it comes up with
Have a look at the execution plan and see where the expensive bits are.
May be worth replacing all those nested isnulls with a coalesce of all the
columns in the nested isnulls - makes it much easier to read, less code and
may help.
If no luck try posting the executiuon plan and ddl for all the tables -
someone may then have some more suggestions
Mike John
"FJC" <FJC@.discussions.microsoft.com> wrote in message
news:30F9AE02-48DD-4FC9-BD5C-636224FB5CE3@.microsoft.com...
> The following stored procedure was created by one of our developers and
> it's
> taking too long to be executed, we already identified that the section of
> the
> coding with the problem are the outer joins, can anybody suggest something
> to
> improve it?
> Thanks
> -- Populate performance layer table with latest data
> ----
> SET @.StepCount = @.StepCount + 1
> SET @.StepName = 'Populate performance layer table - part 1'
> EXEC @.StepID = EDW_Manager.adm.get_Step_Reference @.ProcessID, @.StepName,
> @.StepCount
> SELECT isnull(isnull(isnull(poobd.clinical_hierarchy_ward_did,
> unknown.clinical_hierarchy_ward_did), sday.clinical_hierarchy_ward_did),
> sep.clinical_hierarchy_ward_did) as clinical_hierarchy_ward_did,
> isnull(isnull(isnull(poobd.clinical_hierarchy_unit_did,
> unknown.clinical_hierarchy_unit_did), sday.clinical_hierarchy_unit_did),
> sep.clinical_hierarchy_unit_did) as clinical_hierarchy_unit_did,
> isnull(isnull(isnull(poobd.cost_centre_did, unknown.cost_centre_did),
> sday.cost_centre_did), sep.cost_centre_did) as cost_centre_did,
> isnull(isnull(isnull(poobd.charge_cost_centre_did,
> unknown.charge_cost_centre_did), sday.charge_cost_centre_did),
> sep.charge_cost_centre_did) as charge_cost_centre_did,
> isnull(isnull(isnull(poobd.budget_date_did, unknown.budget_date_did),
> sday.budget_date_did), sep.budget_date_did) as budget_date_did,
> isnull(isnull(isnull(poobd.initiative_did, unknown.initiative_did),
> sday.initiative_did), sep.initiative_did) as initiative_did,
> isnull(unknown.measure_UNKNOWN, 0) as measure_UNKNOWN,
> isnull(poobd.measure_POOBD, 0) as measure_POOBD,
> isnull(sday.measure_SDAY, 0) as measure_SDAY,
> isnull(sep.measure_SEP, 0) as measure_SEP
> INTO #activity_measures_part_1
> FROM @.measure_UNKNOWN unknown
> FULL OUTER JOIN @.measure_POOBD poobd
> ON unknown.clinical_hierarchy_ward_did =
> poobd.clinical_hierarchy_ward_did and
> unknown.clinical_hierarchy_unit_did =
> poobd.clinical_hierarchy_unit_did and
> unknown.cost_centre_did = poobd.cost_centre_did and
> unknown.charge_cost_centre_did = poobd.charge_cost_centre_did
> and
> unknown.budget_date_did = poobd.budget_date_did and
> unknown.initiative_did = poobd.initiative_did
> FULL OUTER JOIN @.measure_SDAY sday
> ON sday.clinical_hierarchy_ward_did =
> poobd.clinical_hierarchy_ward_did and
> sday.clinical_hierarchy_unit_did =
> poobd.clinical_hierarchy_unit_did and
> sday.cost_centre_did = poobd.cost_centre_did and
> sday.charge_cost_centre_did = poobd.charge_cost_centre_did and
> sday.budget_date_did = poobd.budget_date_did and
> sday.initiative_did = poobd.initiative_did
> FULL OUTER JOIN @.measure_SEP sep
> ON sep.clinical_hierarchy_ward_did =
> poobd.clinical_hierarchy_ward_did and
> sep.clinical_hierarchy_unit_did =
> poobd.clinical_hierarchy_unit_did and
> sep.cost_centre_did = poobd.cost_centre_did and
> sep.charge_cost_centre_did = poobd.charge_cost_centre_did and
> sep.budget_date_did = poobd.budget_date_did and
> sep.initiative_did = poobd.initiative_did
> EXEC @.Error = EDW_Manager.adm.log_Step_Results @.StepID, @.@.ERROR,
> @.@.ROWCOUNT
> IF (@.Error <> 0) GOTO LBL_Abort
> SET @.StepCount = @.StepCount + 1
> SET @.StepName = 'Populate performance layer table - part 2'
> EXEC @.StepID = EDW_Manager.adm.get_Step_Reference @.ProcessID, @.StepName,
> @.StepCount
> SELECT isnull(isnull(isnull(soobd.clinical_hierarchy_ward_did,
> wsep9.clinical_hierarchy_ward_did),ptobd.clinical_hierarchy_ward_did),stob
d.clinical_hierarchy_ward_did)
> as clinical_hierarchy_ward_did,
> isnull(isnull(isnull(soobd.clinical_hierarchy_unit_did,
> wsep9.clinical_hierarchy_unit_did),ptobd.clinical_hierarchy_unit_did),stob
d.clinical_hierarchy_unit_did)
> as clinical_hierarchy_unit_did,
> isnull(isnull(isnull(soobd.cost_centre_did,
> wsep9.cost_centre_did),ptobd.cost_centre_did),stobd.cost_centre_did) as
> cost_centre_did,
> isnull(isnull(isnull(soobd.charge_cost_centre_did,
> wsep9.charge_cost_centre_did),ptobd.charge_cost_centre_did),stobd.charge_c
ost_centre_did)
> as charge_cost_centre_did,
> isnull(isnull(isnull(soobd.budget_date_did,
> wsep9.budget_date_did),ptobd.budget_date_did),stobd.budget_date_did) as
> budget_date_did,
> isnull(isnull(isnull(soobd.initiative_did,
> wsep9.initiative_did),ptobd.initiative_did),stobd.initiative_did) as
> initiative_did,
> isnull(soobd.measure_SOOBD, 0) as measure_SOOBD,
> isnull(wsep9.measure_WSEP9, 0) as measure_WSEP9,
> isnull(ptobd.measure_PTOBD, 0) as measure_PTOBD,
> isnull(stobd.measure_STOBD, 0) as measure_STOBD
> INTO #activity_measures_part_2
> FROM @.measure_SOOBD soobd
> FULL OUTER JOIN @.measure_WSEP9 wsep9
> ON wsep9.clinical_hierarchy_ward_did =
> soobd.clinical_hierarchy_ward_did and
> wsep9.clinical_hierarchy_unit_did =
> soobd.clinical_hierarchy_unit_did and
> wsep9.cost_centre_did = soobd.cost_centre_did and
> wsep9.charge_cost_centre_did = soobd.charge_cost_centre_did and
> wsep9.budget_date_did = soobd.budget_date_did and
> wsep9.initiative_did = soobd.initiative_did
> FULL OUTER JOIN @.measure_PTOBD ptobd
> ON ptobd.clinical_hierarchy_ward_did =
> soobd.clinical_hierarchy_ward_did and
> ptobd.clinical_hierarchy_unit_did =
> soobd.clinical_hierarchy_unit_did and
> ptobd.cost_centre_did = soobd.cost_centre_did and
> ptobd.charge_cost_centre_did = soobd.charge_cost_centre_did and
> ptobd.budget_date_did = soobd.budget_date_did and
> ptobd.initiative_did = soobd.initiative_did
> FULL OUTER JOIN @.measure_STOBD stobd
> ON stobd.clinical_hierarchy_ward_did =
> soobd.clinical_hierarchy_ward_did and
> stobd.clinical_hierarchy_unit_did =
> soobd.clinical_hierarchy_unit_did and
> stobd.cost_centre_did = soobd.cost_centre_did and
> stobd.charge_cost_centre_did = soobd.charge_cost_centre_did and
> stobd.budget_date_did = soobd.budget_date_did and
> stobd.initiative_did = soobd.initiative_did
> EXEC @.Error = EDW_Manager.adm.log_Step_Results @.StepID, @.@.ERROR,
> @.@.ROWCOUNT
> IF (@.Error <> 0) GOTO LBL_Abort
>
> select count(*) from #activity_measures_part_1
> select count(*) from #activity_measures_part_2
> SET @.StepCount = @.StepCount + 1
> SET @.StepName = 'Populate performance layer table - final'
> EXEC @.StepID = EDW_Manager.adm.get_Step_Reference @.ProcessID, @.StepName,
> @.StepCount
> INSERT INTO EDW_reporting.whs.PERF_activity_budget_ward_by_measure
> (clinical_hierarchy_ward_did,
> clinical_hierarchy_unit_did,
> cost_centre_did,
> charge_cost_centre_did,
> budget_date_did,
> initiative_did,
> measure_UNKNOWN,
> measure_POOBD,
> measure_SDAY,
> measure_SEP,
> measure_SOOBD,
> measure_WSEP9,
> measure_PTOBDs,
> measure_STOBDs,
> batch_id)
> SELECT isnull(part1.clinical_hierarchy_ward_did,
> part2.clinical_hierarchy_ward_did) as clinical_hierarchy_ward_did,
> isnull(part1.clinical_hierarchy_unit_did,
> part2.clinical_hierarchy_unit_did) as clinical_hierarchy_unit_did,
> isnull(part1.cost_centre_did, part2.cost_centre_did) as
> cost_centre_did,
> isnull(part1.charge_cost_centre_did, part2.charge_cost_centre_did) as
> charge_cost_centre_did,
> isnull(part1.budget_date_did, part2.budget_date_did) as
> budget_date_did,
> isnull(part1.initiative_did, part2.initiative_did) as initiative_did,
> isnull(part1.measure_UNKNOWN, 0) as measure_UNKNOWN,
> isnull(part1.measure_POOBD, 0) as measure_POOBD,
> isnull(part1.measure_SDAY, 0) as measure_SDAY,
> isnull(part1.measure_SEP, 0) as measure_SEP,
> isnull(part2.measure_SOOBD, 0) as measure_SOOBD,
> isnull(part2.measure_WSEP9, 0) as measure_WSEP9,
> isnull(part2.measure_PTOBD, 0) as measure_PTOBD,
> isnull(part2.measure_STOBD, 0) as measure_STOBD,
> @.BatchID
> FROM #activity_measures_part_1 part1
> FULL OUTER JOIN #activity_measures_part_2 part2
> ON part1.clinical_hierarchy_ward_did =
> part2.clinical_hierarchy_ward_did and
> part1.clinical_hierarchy_unit_did =
> part2.clinical_hierarchy_unit_did and
> part1.cost_centre_did = part2.cost_centre_did and
> part1.charge_cost_centre_did = part2.charge_cost_centre_did and
> part1.budget_date_did = part2.budget_date_did and
> part1.initiative_did = part2.initiative_did
> EXEC @.Error = EDW_Manager.adm.log_Step_Results @.StepID, @.@.ERROR,
> @.@.ROWCOUNT
> IF (@.Error <> 0) GOTO LBL_Abort|||Consider using temporary tables instead of table variables. The options to
create indexes on table variables are very limited.
For better help, please post DDL, sample data and expected results.
ML
http://milambda.blogspot.com/|||Only a minor point (not performance related), but
you can use a single "coalesce" instead of multiple
nested "isnull"s. This may make your query easier to
read.

Outer Join and WHERE clause strange behavior

I am writing a stored procedure to get aggregrate data out of a web site log
table by joining it with a view
DDL for the table:
CREATE TABLE w3cexlog (
pacifictime datetime,
[cs-method] varchar(20),
target varchar(8000),
query varchar(2000),
username varchar(100),
browser varchar(8000),
referrer varchar(2000),
status int,
substatus int,
win32status int,
[sc-bytes] bigint,
[cs-bytes] bigint,
[time-taken] bigint
)
The view is through ADSI, and pulls the Active Directory OU, samAccountName
(which corresponds to username), and full name for the user.
If the view where a base table, it's DDL would look like this
CREATE TABLE PortalUsers (
name nvarchar(512),
samAccountName nvarchar(512),
OU nvarchar(8000)
)
I want to find out how many hits where generated by users in each OU, along
with visitors from each OU, by joining PortalUsers to w3cexlog on
PortalUsers.samAccountName = w3cexlog.username. (The web site uses AD
authentication, so they must match).
Here is the query:
SELECT p.ou, count(w.username) AS hits, count(distinct w.username) AS visito
rs
FROM portalusers p LEFT JOIN w3cexlog w on
p.samaccountname=w.username
GROUP BY p.ou
ORDER BY 2 DESC, 1
This query gives the expected results; OUs for which none of its users
produced a "hit" still show in the output, but with zero's in the "hits" and
"visitors" columns. However, if I add a WHERE clause to query a specific dat
e
range:
SELECT p.ou, count(w.username) AS hits, count(distinct w.username) AS visito
rs
FROM portalusers p LEFT JOIN w3cexlog w on
p.samaccountname=w.username
WERE w.pacifictime BETWEEN '20060102' AND '20060112'
GROUP BY p.ou
ORDER BY 2 DESC, 1
The OUs that produces no hits are now gone from the result set! That WHERE
clause wouldn't have removed any rows for the OUs that didn't have hits; the
y
where never there to begin with; so why does the behavior of the outer join
change?
In order to get around this, I constructed the following query
SELECT t1.OU, t2.hits, t2.visitors FROM
(
SELECT DISTINCT OU FROM PortalUsers
) t1
LEFT JOIN
(
SELECT p.ou, count(w.username) AS hits, count(distinct w.username) AS visito
rs
FROM portalusers p LEFT JOIN w3cexlog w on
p.samaccountname=w.username
WERE w.pacifictime BETWEEN '20060102' AND '20060112'
GROUP BY p.ou
) t2
ON t1.OU = t2.OU
ORDER BY 2 DESC, 1
But the performance of this is terrible."Mark Williams" <MarkWilliams@.discussions.microsoft.com> wrote in message
news:5002E6BF-4C4F-48B1-B333-D7B398C42430@.microsoft.com...
>I am writing a stored procedure to get aggregrate data out of a web site
>log
> table by joining it with a view
>
> SELECT p.ou, count(w.username) AS hits, count(distinct w.username) AS
> visitors
> FROM portalusers p LEFT JOIN w3cexlog w on
> p.samaccountname=w.username
> WERE w.pacifictime BETWEEN '20060102' AND '20060112'
> GROUP BY p.ou
> ORDER BY 2 DESC, 1
> The OUs that produces no hits are now gone from the result set! That WHERE
> clause wouldn't have removed any rows for the OUs that didn't have hits;
> they
> where never there to begin with; so why does the behavior of the outer
> join
> change?
>
Mark,
This is the way things work. An outer join may contain results
that fail the ON condition, but it will never contain results that fail
the WHERE condition. The WHERE condition is a real filter
than can be thought of as being applied to the result of the
outer join.
If you have result rows with 0 hits, all the result columns from
w3cexlog in those result rows contain NULL. NULL will never
be between '20060102' and '20060112', so you'll see no rows
that failed the ON condition.
Maybe what you want is just
... on p.samaccountname=w.username
AND w.pacifictime BETWEEN '20060102' AND '20060112'
...
Steve Kass
Drew University|||Another option is to leave the join unchanged and alter the where caluse lik
e
this:
...
WHERE (w.pacifictime BETWEEN '20060102' AND '20060112' or w.pacifictime is
null)
...
ML
http://milambda.blogspot.com/|||Thanks Steve and ML. I should have seen it, but I was a little tired! I had
the incorrect thinking that the WHERE clause was applied before the join,
instead of after.
"Steve Kass" wrote:

> "Mark Williams" <MarkWilliams@.discussions.microsoft.com> wrote in message
> news:5002E6BF-4C4F-48B1-B333-D7B398C42430@.microsoft.com...
> Mark,
> This is the way things work. An outer join may contain results
> that fail the ON condition, but it will never contain results that fail
> the WHERE condition. The WHERE condition is a real filter
> than can be thought of as being applied to the result of the
> outer join.
> If you have result rows with 0 hits, all the result columns from
> w3cexlog in those result rows contain NULL. NULL will never
> be between '20060102' and '20060112', so you'll see no rows
> that failed the ON condition.
> Maybe what you want is just
> ... on p.samaccountname=w.username
> AND w.pacifictime BETWEEN '20060102' AND '20060112'
> ...
> Steve Kass
> Drew University
>
>|||It will work, but I wouldn't suggest this as a good option, I'm afraid.
This is like adding "and I really meant that is was an outer join" to the
where clause.
Or did you see some advantage with putting it in the where clause, like some
problem that doing it this way might solve?
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"ML" <ML@.discussions.microsoft.com> wrote in message
news:3F48CD19-D89E-4535-8FFD-0045DF823CA1@.microsoft.com...
> Another option is to leave the join unchanged and alter the where caluse
> like
> this:
> ...
> WHERE (w.pacifictime BETWEEN '20060102' AND '20060112' or w.pacifictime is
> null)
> ...
>
> ML
> --
> http://milambda.blogspot.com/|||Another option is to move the condition to the ON clause:
SELECT t1.OU, t2.hits, t2.visitors FROM
(
SELECT DISTINCT OU FROM PortalUsers
) t1
LEFT JOIN
(
SELECT p.ou, count(w.username) AS hits, count(distinct w.username) AS
visitors, pacifictime
FROM portalusers p LEFT JOIN w3cexlog w on
p.samaccountname=w.username
GROUP BY p.ou
) t2
ON (t1.OU = t2.OU )
AND t2.pacifictime BETWEEN '20060102' AND '20060112'
ORDER BY 2 DESC, 1
That might perform better|||On Sat, 14 Jan 2006 04:46:01 -0800, ML wrote:

>Another option is to leave the join unchanged and alter the where caluse li
ke
>this:
>...
>WHERE (w.pacifictime BETWEEN '20060102' AND '20060112' or w.pacifictime is
>null)
>...
Hi ML,
That would also include rows with w.pacifictime equal to NULL in the
base table (assuming the column is nullable); I don't think that this is
what Mark wants.
Hugo Kornelis, SQL Server MVP|||Since both methods yield same results the only argument would be performance
.
So, which one would perform better?
ML
http://milambda.blogspot.com/|||On Sun, 15 Jan 2006 14:09:01 -0800, ML wrote:

>Since both methods yield same results the only argument would be performanc
e.
>So, which one would perform better?
Hi ML,
Have you testeed them?
(I'll gladly admit I didn't - but due to the OR in your method, I'd be
surprised if it turns out to be the faster version).
Of course, they don't yield the same results if the column is nullible.
Hugo Kornelis, SQL Server MVP|||Beyond what was discussed about how if the pacifictime column was nullable
(you would want to check for the join columns being null) the argument is
style and symantics.
I would argue that it is very important to write readable queries that can
be understood by the next reader AND that make sense. Performance is
important, and if the less clear version peformed measurably better (if
anything it would likely be no better than equal, but sometimes there can be
reasons to do things that don't seem kosher for performance sake, but this
is unlikely to be one of those cases.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"ML" <ML@.discussions.microsoft.com> wrote in message
news:61118E3B-FAED-4124-86F5-26219564531A@.microsoft.com...
> Since both methods yield same results the only argument would be
> performance.
> So, which one would perform better?
>
> ML
> --
> http://milambda.blogspot.com/

outer join

Hello,

I am having problems with an outer join statement.
I have written a procedure that tests a table for missing and corrupt data and
to test my procedure, I take a table with 100% correct entries and corrupt them by hand. Then I test if my repaird data is looking like the correct data did.
To do the test, I copy the correct data into a temp table "copy", join it with the "repaired" table and check if any fields look different. The problem is, that i don't get the missing data. The statement is looking like this:

select o.*,'#',k.* from repaired o right outer join copy k on
(str(o.a) + 'A' + str(o.b) + 'A' + str(o.c) =str(k.a)+ 'A' + str(k.b) + 'A' + str(k.c) )
where
o.D<>k.D or
o.E<>k.E or
o.F<>k.F or
...

I have dont the concatenation because I thougt, that a join with 3 fields could be responsible for not finding the missing data in table "copy".
Before that it looked like:

... on (o.a=k.a and o.b=o.b and o.c = k.c) where ...

In table "copy" is a record missing that is in table "repaired".
Why is my statement not printingout that missing record?
Shouldn't be an outer join exactly what I have to use for finding missing data?

I anybody can help me, I would be very happy.

SvenIn table "copy" is a record missing that is in table "repaired".try LEFT OUTER JOIN instead ;)|||Your first join attempt: ... on (o.a=k.a and o.b=o.b and o.c = k.c) where ... joins o.b on itself. This sort of typo happens with the gratuitous use of table aliases.
Use a left outer join, as Rudy has suggested.
Try using the binary_checksum value rather than listing all your columns in the where clause:select repaired.*,
'#',
copy.*
from --repaired
(select binary_checksum(*) checkvalue,
repaired.*
from repaired) repaired
left outer join --copy
(select binary_checksum(*) checkvalue,
repaired.*
from copy) copy
on repaired.a = copy.a
and repaired.b = copy.b
and repaired.c = copy.c
where repaired.checkvalue <> copy.checkvalue

Out params

I heva a sproc that looks something like this:
CREATE PROCEDURE GetData
@.Id INT,
@.Param1 INT OUT,
@.Param2 INT OUT,
@.Param3 INT OUT,
@.Param4 INT OUT,
@.Param5 INT OUT
AS
SET @.Param1 = (SELECT Value1 FROM MyTable WHERE [Id] = @.Id)
SET @.Param2 = (SELECT Value2 FROM MyTable WHERE [Id] = @.Id)
SET @.Param3 = (SELECT Value3 FROM MyTable WHERE [Id] = @.Id)
SET @.Param4 = (SELECT Value4 FROM MyTable WHERE [Id] = @.Id)
SET @.Param5 = (SELECT Value5 FROM MyTable WHERE [Id] = @.Id)
Now this means five selects. How can I rewrite it so it will do only one
select? (I do want to use out params. Not return a recordset.)
--
Mikael EngdahlUse below construct:
SELECT @.parm1 = value1, @.parm2 = value2, ...
FROM MyTable
WHERE...
Note that above will not give an error if the SELECT return more than one row.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Mikael Engdahl" <mikael-l@.engdahl.no.spam.com> wrote in message
news:uUyK1POtDHA.1744@.TK2MSFTNGP12.phx.gbl...
> I heva a sproc that looks something like this:
> CREATE PROCEDURE GetData
> @.Id INT,
> @.Param1 INT OUT,
> @.Param2 INT OUT,
> @.Param3 INT OUT,
> @.Param4 INT OUT,
> @.Param5 INT OUT
> AS
> SET @.Param1 = (SELECT Value1 FROM MyTable WHERE [Id] = @.Id)
> SET @.Param2 = (SELECT Value2 FROM MyTable WHERE [Id] = @.Id)
> SET @.Param3 = (SELECT Value3 FROM MyTable WHERE [Id] = @.Id)
> SET @.Param4 = (SELECT Value4 FROM MyTable WHERE [Id] = @.Id)
> SET @.Param5 = (SELECT Value5 FROM MyTable WHERE [Id] = @.Id)
> Now this means five selects. How can I rewrite it so it will do only one
> select? (I do want to use out params. Not return a recordset.)
>
> --
> Mikael Engdahl
>

Out in email.

I am using extended stored procedure xp_sendmail to send the email now my
question is, is there any way in can write the out of following query into
one file and send it to through email? or output directly goes to email..
restore FILELISTONLY from disk = 'c:\fullpath.bak'
Thanks
Probably a few ways but a couple off the top of my head -
Execute the command using osql and specify an output file in
the osql command.
Execute the command within a job - for the job step doing
the restore filelistonly, go to the advanced tab and specify
an output file.
You can then send the output file as an attachment using
xp_sendmail.
-Sue
On Mon, 30 Oct 2006 08:20:28 -0500, "Rogers"
<naissani@.hotmail.com> wrote:

>I am using extended stored procedure xp_sendmail to send the email now my
>question is, is there any way in can write the out of following query into
>one file and send it to through email? or output directly goes to email..
>restore FILELISTONLY from disk = 'c:\fullpath.bak'
>Thanks
>
|||Thanks Sue...
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:0ladk2d0g0n0gm69qv3ot2tgq5fagd7vkr@.4ax.com...
> Probably a few ways but a couple off the top of my head -
> Execute the command using osql and specify an output file in
> the osql command.
> Execute the command within a job - for the job step doing
> the restore filelistonly, go to the advanced tab and specify
> an output file.
> You can then send the output file as an attachment using
> xp_sendmail.
> -Sue
> On Mon, 30 Oct 2006 08:20:28 -0500, "Rogers"
> <naissani@.hotmail.com> wrote:
>

OUT and OUTPUT - Stored procedure parameters.

Hi All,

The following is a code snippit. My main interests are the OUT and OUTPUT parameter keywords. One returns a single value, and the other seemingly a resultset. OUTPUT returns a single value, however OUT seems to return a list of values. Could I please get this confirmed?

Also, I cannot see how the value being returned by OUT is being iterated...

Any help on the obove two matters is appreciated.

Thank You

Chris

BEGIN SNIPPET-

--The following example creates the Production.usp_GetList

--stored procedure, which returns a list of products that have

--prices that do not exceed a specified amount.

USE AdventureWorks;

GO

IF OBJECT_ID ( 'Production.uspGetList', 'P' ) IS NOT NULL

DROP PROCEDURE Production.uspGetList;

GO

CREATE PROCEDURE Production.uspGetList @.Product varchar(40)

, @.MaxPrice money

, @.ComparePrice money OUTPUT

, @.ListPrice money OUT

AS

SELECT p.[Name] AS Product, p.ListPrice AS 'List Price'

FROM Production.Product AS p

JOIN Production.ProductSubcategory AS s

ON p.ProductSubcategoryID = s.ProductSubcategoryID

WHERE s.[Name] LIKE @.Product AND p.ListPrice < @.MaxPrice;

-- Populate the output variable @.ListPprice.

SET @.ListPrice = (SELECT MAX(p.ListPrice)

FROM Production.Product AS p

JOIN Production.ProductSubcategory AS s

ON p.ProductSubcategoryID = s.ProductSubcategoryID

WHERE s.[Name] LIKE @.Product AND p.ListPrice < @.MaxPrice);

-- Populate the output variable @.compareprice.

SET @.ComparePrice = @.MaxPrice;

GO

USE

DECLARE @.ComparePrice money, @.Cost money

EXECUTE Production.uspGetList '%Bikes%', 700,

@.ComparePrice OUT,

@.Cost OUTPUT

IF @.Cost <= @.ComparePrice

BEGIN

PRINT 'These products can be purchased for less than

$'+RTRIM(CAST(@.ComparePrice AS varchar(20)))+'.'

END

ELSE

PRINT 'The prices for all products in this category exceed

$'+ RTRIM(CAST(@.ComparePrice AS varchar(20)))+'.'

-

Partial Result Set

-

--Product List Price

-

--Road-750 Black, 58 539.99

--Mountain-500 Silver, 40 564.99

--Mountain-500 Silver, 42 564.99

--...

--Road-750 Black, 48 539.99

--Road-750 Black, 52 539.99

--

--(14 row(s) affected)

--

--These items can be purchased for less than $700.00.

Well, OUT and OUTPUT keywords are synonyms, just as INT and INTEGER are.|||

Really?

AAAAAAAARGH it's all clear now. I can't see why in the example they mix and match however though - it just brings confusion into the equation.

No point in answering the other part of my Q: I 'm sure the example iterates throught the execution of the SP, but doesn't show it.

Thank you Sergey.

osql and carc.:

I run a script with Osql and got no error but the accent caracter (french message) are modified.

the script is
CREATE PROCEDURE dbo.AccentTest
AS
Print(' ')
GO
and come
CREATE PROCEDURE dbo.AccentTest
AS
Print('Θ α Φ ε ∩ ∙')
GO
:o
This is very bad! Can someone can help me?
Any suggestion?
PLease....

osql.exe -S MyServer -U username-P pwd-d TestDB -n -o "C:\Dev\osql_test.log" -i "C:\Dev\AccentTest.sql" :What's the default language of the database? I don't have a lot of experience here, but I would start there. Also take a look at varchar and nvarchar.

[Edit: I should note that I have a default install of SQL Server 2000 (SP4). I created the proc as shown above and executed it and it printed the characters correctly.]

Regards,

hmscott

os_oa* run exe written in vb6

I need to launch an exe application written in VB6 from stored procedure or
trigger. I know I have to use os_oa* but I cannot find any document to help
me build one. Many examples that I found have something to do with com but I
only have exe. I know this is not the way to go but I was told to do it. Can
any one help me by pointing me in the right directions? I know nothing about
this so I am learning from scratch for os_oa*.
The exe was developed to run silent in the background with no user
interface.Search for xp_cmdshell in BOL, it may be useful.
Francesco Anti
"UGH" <nospam@.noSPam.com> wrote in message
news:eRxL9tFaFHA.1368@.tk2msftngp13.phx.gbl...
>I need to launch an exe application written in VB6 from stored procedure or
>trigger. I know I have to use os_oa* but I cannot find any document to help
>me build one. Many examples that I found have something to do with com but
>I only have exe. I know this is not the way to go but I was told to do it.
>Can any one help me by pointing me in the right directions? I know nothing
>about this so I am learning from scratch for os_oa*.
>
> The exe was developed to run silent in the background with no user
> interface.
>|||Do you mean sp_oa* ' If so, here is everything you need:
1. Compile your VB6 class into a DLL. You better have good error handling in
it otherwise you risk crashing your SQL Server.
2. Place that DLL into the WIN32 directory on your SQL Server, and REGISTER
it on your SQL Server by using RegSvr32.exe
3. Call methods of your VB6 class/dll from any stored procedure, using
something like the following (some of the names have been changed to protect
the innocent):
BEGIN
blah blah blah
DECLARE @.Object int -- holds a reference to your object instantiated
from the vb6 class.
SET @.MethodToCall = 'CalculatePayment(' + @.RatePercent + ', ' +
@.RateIncrease + ', ' + @.Months + ', ' + @.Fees + ')'
--Instantiate an instance of our DataConversion class and put it's
reference in @.object
EXEC sp_OACreate 'Your_VB_Class.DataConversion', @.object OUT
--Run the CalculatePayment method of our DataConversion class - and
place it's output into @.Return
EXEC sp_OAMethod @.object, @.MethodToCall, @.return OUT
-- Destroy the instance of our DataConversion class now that we're
done with it.
EXEC sp_OADestroy @.object
blah blah blah
END
4. Any time you need to upgrade your VB6 DLL, be sure to Unregister the old
one (using RegSvr32.exe... -U), then replace the old DLL with the new one,
and then register the new one like in step 2 above. No need to restart your
SQL Server.
That's it - really quite straight-forward, actually.
-HTH
"UGH" <nospam@.noSPam.com> wrote in message
news:eRxL9tFaFHA.1368@.tk2msftngp13.phx.gbl...
>I need to launch an exe application written in VB6 from stored procedure or
>trigger. I know I have to use os_oa* but I cannot find any document to help
>me build one. Many examples that I found have something to do with com but
>I only have exe. I know this is not the way to go but I was told to do it.
>Can any one help me by pointing me in the right directions? I know nothing
>about this so I am learning from scratch for os_oa*.
>
> The exe was developed to run silent in the background with no user
> interface.
>|||Hi,
Use the following :
EXEC xp_cmdshell 'your vb program.exe'
you want to grant the right to execute xp_cmdshell to the SQL login
LimitedUser.
You'll need an NT account to execute the program. Here's the script:
use master
go
xp_sqlagent_proxy_account N'SET'
, N'<mydomain>'
, N'<ntuser>'
, N'<ntuser's password>'
go
-- retrieve the proxy account to check that it's correct.
xp_sqlagent_proxy_account N'GET'
go
-- grant database access in master
sp_grantdbaccess 'LimitedUser'
go
grant exec on xp_cmdshell to LimitedUser
go
Thanks,
Tarek ghazali
"UGH" <nospam@.noSPam.com> wrote in message
news:eRxL9tFaFHA.1368@.tk2msftngp13.phx.gbl...
>I need to launch an exe application written in VB6 from stored procedure or
>trigger. I know I have to use os_oa* but I cannot find any document to help
>me build one. Many examples that I found have something to do with com but
>I only have exe. I know this is not the way to go but I was told to do it.
>Can any one help me by pointing me in the right directions? I know nothing
>about this so I am learning from scratch for os_oa*.
>
> The exe was developed to run silent in the background with no user
> interface.
>