Stored Procedure Syntax -- sql server (alternate)

Bruce Loving
5/14/2004 2:44:43 PM

simple add after the select

if @@rowcount = 0
select '000'

On Thu, 13 May 2004 20:34:37 +1000, "Jarrod Morrison"
[quoted text, click to view]

Joe Celko
5/15/2004 5:16:30 PM

If you'd post DDL and some specs, you can almost certainly write this as
one compiled SELECT statement, instead of this pile of procedural code
and dynamic SQL.

I hope that the VARCHAR(50) datatypes are the results of actual research
and planning. A lot of ACCESS users and newbies use that automatically
and screw up their data integrity. Since @user_name is never used, why
is it a parameter?

Writing parsing loops like this is a really bad coding technique. You
can find a lots of kludges (including one of mine -- Hey, I can write
crappy code, too!) for this in the FAQ for the newsgroup. But the real
answer should look something like this:

SELECT location_id
FROM Locations
WHERE group_id
IN (SELECT machine_name FROM MachineList)
UNION ALL
SELECT location_id
FROM Custlocations
WHERE machine_name
IN (SELECT machine_name FROM MachineList);

I am not sure why Locations and CustLocations are logically different,
but I will assume that they are. Likewise, what is the logical
difference in a group_id and a machine_name? If machines are grouped in
some way, then there might be data design problems.

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***

John Bell
5/15/2004 6:32:20 PM

Hi

Using Joe's SQL then I think you require something like:

SELECT location_id
FROM Locations
WHERE group_id
IN (SELECT machine_name FROM MachineList)
UNION ALL
SELECT location_id
FROM Custlocations
WHERE machine_name
IN (SELECT machine_name FROM MachineList)
UNION ALL
SELECT '000'
FROM MachineList
WHERE machine_name
NOT IN (SELECT machine_name FROM Custlocations
UNION ALL
SELECT group_id FROM Locations )

or maybe:

SELECT ISNULL(l.location_id , '000' )
FROM MachineList m LEFT JOIN
( SELECT location_id, machine_name
FROM Custlocations
UNION ALL
SELECT location_id, group_id
FROM Locations ) l ON l.machine_name = m.machine_name

John

[quoted text, click to view]

Jarrod Morrison
5/15/2004 10:05:41 PM

Hi Bruce

Thanks for the reply, this sort of does what i wanted, it does return the
000 but it returns it as a second recordset, so i get a blank recordset and
then another one with the 000. The vb program which interfaces with this
procedure returns an error as before because it doesnt see any data as being
returned and returns a BOF or EOF error. If i run the procedure in query
analyzer it returns

LocationID
-Blank-
(No Column Name)
000

Is there a way to make the 000 appear under LocationID ?

Thanks again

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server (alternate)

sql server (alternate) : Stored Procedure Syntax