Database

4-Nov-04 20:29

As Christian said, why do you want to do this?

Surely it would be easier to write
SELECT Name, Email FROM Users WHERE Gendre = 0 AND Name='Danielle'

Of course it is a very useful thing if you want to perform inner joins on subqueries, but I found recently that the query optimiser can sometimes get itself in a bit of a fankle over that if the subquery operates on too much data: The Stored Procedure runs how fast?[^]

Re: Nsted SELECT statements

Den2Fly4-Nov-04 21:25

Den2Fly

4-Nov-04 21:25

Hello Colin
Thanks for your note, you are right about that query but that was just an example. ( not a good one I think Smile | :)

) and again you are right my real project is some INNER JOINs that I have found this a good way to achive the result

---
"Art happens when you least expect it."

Re: Nsted SELECT statements

Christian Graus4-Nov-04 22:19

Christian Graus

4-Nov-04 22:19

Yeah, joins are generally the answer rather than what you were doing before Smile | :)

Christian

I have several lifelong friends that are New Yorkers but I have always gravitated toward the weirdo's. - Richard Stringer

Call multiple stored procs in the same query

Luis Alonso Ramos4-Nov-04 7:12

Luis Alonso Ramos

4-Nov-04 7:12

Hello,

I want to call two stored procedures at the same time, so the results are stored in two tables in the same DataSet. Both stored procedures have one parameter with the same name and takes the same value.

I'm using C#. How would I do it?

Thanks,

-- LuisR

Luis Alonso Ramos
Intelectix - Chihuahua, Mexico

Not much here: My CP Blog!

Re: Call multiple stored procs in the same query

Christian Graus4-Nov-04 12:30

Christian Graus

4-Nov-04 12:30

Write a SP that calls the other two.

Christian

I have several lifelong friends that are New Yorkers but I have always gravitated toward the weirdo's. - Richard Stringer

JOIN with optional field

Luis Alonso Ramos4-Nov-04 6:33

Luis Alonso Ramos

4-Nov-04 6:33

Hello,

I'm building a stored procedure (for MSDE) in the Access designer. I have a Sales table, and a Packages table (for a car wash). Each sale can have an exterior package and a interior package, each field containing the ID of the record in the Packages table.

I want my stored procedure to return the Name for both the interior and exterior packages. The problem is, if one of the fields has a 0 (no package sold, for example only exterior wash), my stored procedure returns an empty resultset.

I would like it to return that field empty (or NULL) and still return the full row (there's other info on the Sales table like Date and customer ID.)

In case it's important, this is my query (from Access SQL view):

ALTER PROCEDURE dbo.GetSaleInfo
(@ID_sale int)
AS SELECT     dbo.Sales.Date, dbo.Sales.Time, dbo.Vehicles.Make + ' ' + dbo.Vehicles.Model AS CarModel, dbo.Packages.Name AS ExteriorPackage, 
                      Packages_1.Name AS InteriorPackage, dbo.Promotions.Name AS Promotion
FROM         dbo.Sales INNER JOIN
                      dbo.Vehicles ON dbo.Sales.ID_vehicle = dbo.Vehicles.ID_vehicle INNER JOIN
                      dbo.Packages ON dbo.Sales.ExteriorPackage = dbo.Packages.ID_package INNER JOIN
                      dbo.Promotions ON dbo.Sales.ID_promotion = dbo.Promotions.ID_promotion INNER JOIN
                      dbo.Packages Packages_1 ON dbo.Sales.InteriorPackage = Packages_1.ID_package
WHERE     (dbo.Sales.ID_sale = @ID_sale)

Any ideas?

-- LuisR

Luis Alonso Ramos
Intelectix - Chihuahua, Mexico

Not much here: My CP Blog!

Re: JOIN with optional field

Edbert P4-Nov-04 11:08

Edbert P

4-Nov-04 11:08

I think the simple answer is to use LEFT JOIN instead of INNER JOIN.
Since you want to still return data from dbo.Sales no matter whether you have Exterior package/Interior package or not, you need to specify
Sales LEFT JOIN Packages.

Here's an example of how you should write it:

ALTER PROCEDURE dbo.GetSaleInfo
(@ID_sale int)
AS SELECT     dbo.Sales.Date, dbo.Sales.Time, dbo.Vehicles.Make + ' ' + dbo.Vehicles.Model AS CarModel, dbo.Packages.Name AS ExteriorPackage, 
                      Packages_1.Name AS InteriorPackage, dbo.Promotions.Name AS Promotion
FROM         dbo.Sales INNER JOIN
                      dbo.Vehicles ON dbo.Sales.ID_vehicle = dbo.Vehicles.ID_vehicle LEFT JOIN
                      dbo.Packages ON dbo.Sales.ExteriorPackage = dbo.Packages.ID_package INNER JOIN
                      dbo.Promotions ON dbo.Sales.ID_promotion = dbo.Promotions.ID_promotion LEFT JOIN
                      dbo.Packages Packages_1 ON dbo.Sales.InteriorPackage = Packages_1.ID_package
WHERE     (dbo.Sales.ID_sale = @ID_sale)

I hope that solves your problem Smile | :)

Edbert P.
Sydney, Australia.

Re: JOIN with optional field

Luis Alonso Ramos4-Nov-04 14:05

Luis Alonso Ramos

4-Nov-04 14:05

Edbert P. wrote:
I hope that solves your problem

Yes it did!! Thank you! Smile | :)

-- LuisR

Luis Alonso Ramos
Intelectix - Chihuahua, Mexico

Not much here: My CP Blog!

Find out if a table name already exists

veronique4-Nov-04 5:32

veronique

4-Nov-04 5:32

Hi all!

Does anyone know if it is possible to find out if a table name already exists using SQL? and if yes what is the code?

Thanks in advance!

vero Eek! | :eek:

Re: Find out if a table name already exists

Colin Angus Mackay4-Nov-04 6:59

4-Nov-04 6:59

veronique wrote:
Does anyone know if it is possible to find out if a table name already exists using SQL?

Yes.

veronique wrote:
and if yes what is the code?

IF EXISTS(SELECT * FROM sysobjects WHERE xtype='U' AND [name]=@name_of_table)

Re: Find out if a table name already exists

veronique4-Nov-04 22:38

veronique

4-Nov-04 22:38

thank you Colin! Smile | :)

vero

how to obtain in a fecht a dir result?

Anonymous3-Nov-04 23:56

Anonymous

3-Nov-04 23:56

HI,

how could i get all the files in a directory, and list it one per one?

I need to insert the all files included in a directory, i know how to insert in database ( with textcopy ) but i dont know how to have an array with all names of files

thanks

Stored Procedure

Its due when3-Nov-04 17:52

Its due when

3-Nov-04 17:52

Hi,

Im new to Stored Procedures and was wondering if there was answer to my question. I have created about 5 stored procedures in SQL Enterprise Manager under "Stored Procedure". One of the parameters is sp_name. Is there a way I can populate a list of the stored procedure names in a table without manually doing so. The idea being is that if the stored_procedures are updated - the table will reflect the changes without having to be manually corrected. Thanking you in advance.

Colin Angus Mackay4-Nov-04 0:42

4-Nov-04 0:42

Try:
select name from dbo.sysobjects where OBJECTPROPERTY(id, N'IsProcedure') = 1

Grimolfr5-Nov-04 9:43

Grimolfr

5-Nov-04 9:43

I think it's safer to use the (documented) INFORMATION_SCHEMA views:

SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES

Grim

(aka Toby)

MCDBA, MCSD, MCP+SB

Need a Second Life?[^]

SELECT * FROM user WHERE clue IS NOT NULL
GO

(0 row(s) affected)

Colin Angus Mackay5-Nov-04 10:44

5-Nov-04 10:44

Grimolfr wrote:
I think it's safer to use the (documented) INFORMATION_SCHEMA views

Or the equally documented sysobjects. http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sys-o_4zll.asp[^]

And quite frankly, after using the INFORMATION_SCHEMA views earlier this year... Or should I say attempt to use them. I threw them out and refuse to use them until Microsoft come up with something that is actually useful. Have you actually looked at the source for the views - What a load of complete rubbish!

For example, this is part of the SELECT clause from the ROUTINES view:

SPECIFIC_CATALOG            = db_name(),
SPECIFIC_SCHEMA             = user_name(o.uid),
SPECIFIC_NAME               = o.name,
ROUTINE_CATALOG             = db_name(),
ROUTINE_SCHEMA              = user_name(o.uid),
ROUTINE_NAME                = o.name,

Why have SPECIFIC_CATALOG and ROUTINE_CATALOG? They are the same thing.
Why have SPECIFIC_SCHEMA and ROUTINE_SCHEMA? They are also the same thing.
Why have SPECIFIC_NAME and ROUTINE_NAME? It is just wasting bandwidth and processing time repeating the same data again and again.

Grimolfr5-Nov-04 15:17

Grimolfr

5-Nov-04 15:17

Because they may not be the same thing in, say, SQL Server 2005 (don't know, haven't bothered to fool with it yet), or the first service pack for SQL2k5.

When I tried to go to your documentation link, I got:

Page Cannot Be Found

We apologize for the inconvenience, but the page you are seeking cannot be found in this location.

My original point being that the system tables are subject to completely change their function, schema, usage, etc from one service pack to the next. The schema views aren't.

Grim

(aka Toby)

MCDBA, MCSD, MCP+SB

Need a Second Life?[^]

SELECT * FROM user WHERE clue IS NOT NULL
GO

(0 row(s) affected)

Colin Angus Mackay5-Nov-04 20:16

5-Nov-04 20:16

Grimolfr wrote:
When I tried to go to your documentation link, I got:
Page Cannot Be Found

Don't worry, you can find it in the SQL Server books online that come with SQL Server. In the index type sysobjects.

Grimolfr wrote:
My original point being that the system tables are subject to completely change their function, schema, usage, etc from one service pack to the next.

That's fine if you can put up with the limitations of the views. But I found them to be very restrictive and they do not return full information.

To Get the Id from the database which are not deleted

Its due when7-Nov-04 17:22

Its due when

7-Nov-04 17:22

"Try:
select name from dbo.sysobjects where OBJECTPROPERTY(id, N'IsProcedure') = 1 "

Worked like a treat - thanks for your help and all the associated input

Irsh3-Nov-04 2:14

Irsh

3-Nov-04 2:14

hello, Can any body help me as how the return the ID of the row which is not deleted by a stored procedure.

Description:
I have a Table1 which is being referenced by another Table2, am sending the ID of Table1 to delete the row, due to Referential intergrity the row is not getting deleted, i want to display the id which is not getting deleted to the user in the front end how do i do this pleaseee help in this regard.

Advance Thanx.
Irsh

Adding or Substracting time spans from a DateTime SQL value

Den2Fly2-Nov-04 23:32

Den2Fly

2-Nov-04 23:32

Hello every buddy,
How can I add for example some minutes to a datetime type value and get result again as a datetime?

Thank you so much,
- Den

---
"Art happens when you least expect it."

Re: Adding or Substracting time spans from a DateTime SQL value

Colin Angus Mackay3-Nov-04 0:09

3-Nov-04 0:09

This example takes the current time and adds 5 minutes to it:

DATEADD(minute, 5, getdate())

The first arg is the date part (minute, hour, year, month, etc.)
The second arg is the amount you want to add (or for negative values, subtract)
The third argument is the source datetime.