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 2005 Forums
 Transact-SQL (2005)
 Insert without knowing foreignkey id ahead of time

Author  Topic 

tuesdaysGreen
Starting Member

5 Posts

Posted - 2009-05-06 : 01:42:03
Hi there,

I'm fairly new to sql so please forgive me if this is a stupid question.

My situation is that I have two tables where one is referring to the other via a foreign key. I want my .net application to be able to insert into the referring table with only one stored procedure call and without explicitly specifying the foreign key in the call to the sproc.

For example, here's a portion of my tables:

Files_Tbl
Id int (primary key)
LocationId int (foreign key)
FullFileName varchar(50)

Locations_Tbl
Id int (primary key)
LocationName varchar(50)


The only way I know how to insert a new File entry, is to have my .net application make two calls to the database.
1. Query the Locations_Tbl for an existing id.
2. Use the LocationId from step 1 to insert a new File entry in the Files_Tbl.

However, I want to be able to do this in a single stored procedure call. In other word, my .net application should only be required to provide the FullFileName and the LocationName. This means that the stored proc will need to be able to do steps 1 and 2 from above. Is this possible to do in SQL?

Thanks in advance!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-06 : 02:05:17
Of course.

CREATE PROCEDURE dbo.uspMyFirstStoredProcedure
(
@LocationName VARCHAR(50),
@FullFileName VARCHAR(50)
)
AS

SET NOCOUNT ON

DECLARE @LocationID INT

SELECT @LocationID = LocationID FROM Locations_Tbl WHERE LocationName = @LocationName

IF @LocationID IS NULL
BEGIN
INSERT Locations_Tbl (LocationName) VALUES (@LocationName)
SET @LocationID = SCOPE_IDENTITY()
END

INSERT Files_Tbl (LocationID, FullFileName) VALUES (@LocationID, @FullFileName)
GO




E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

tuesdaysGreen
Starting Member

5 Posts

Posted - 2009-05-06 : 10:24:42
Wow, that's so simple and it works like a charm. I guess it helps to know that you can store variables in the select statement huh. :P

Thanks for the quick response Peso!
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-05-06 : 10:34:55
Assuming that column LocationName is unique and LocationName is already in Locations_Tbl, then you can do the insert in one statement:
insert into Files_Tbl
(
LocationID,
FullFileName
)
select
a.LocationID,
@FullFileName
from
Locations_Tbl a
where
a.LocationName = @LocationName





CODO ERGO SUM
Go to Top of Page

tuesdaysGreen
Starting Member

5 Posts

Posted - 2009-05-06 : 11:47:33
Ah I see. I also did not know that you could specify a variable in the "select" portion of a select statement. I always thought that you could only specify the columns that you wanted to pull out the of the table you're querying against. These are both very good posts for me to learn from!

I ended up going with Peso's suggestion though since he somehow figured out that what I really wanted was to have my procedure create the Location if it didn't already exist. I didn't even add that requirement in my original post so I don't know how he knew what I wanted... Scary...

In any case, thank you both for your great suggestions!

Go to Top of Page

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 2009-05-06 : 13:38:38
The better "enterprise" solution would have been to design a n-tier application and lazy-load/create the "location" as a property of the "file" entity.


public class File
{
public int Id {get; internal set; }
private int LocationId {get; internal set;}
private string FullFileName {get; set;}

public FileLocation Location
{
get
{
if( _location == null )
_location = LocationFactory.GetForFile(this);
return _location;
}
internal set { _location = value; }
}
}
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-05-06 : 13:51:14
quote:
The better "enterprise" solution would have been to design a n-tier application and lazy-load/create the "location" as a property of the "file" entity.
I guess it's better to use a cruise ship or aircraft carrier to go fishing in your local pond too.

And what if they need to support another framework/platform besides .Net? The stored procedure does the job and abstracts the database call and makes it consistent to all callers. It can also be changed as needed without requiring any changes to the code that calls it.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-06 : 13:52:56
quote:
Originally posted by whitefang

The better "enterprise" solution would have been to design a n-tier application and lazy-load/create the "location" as a property of the "file" entity.

Nicely written, but really doesn't show anything to get OP solve his problem.


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 2009-05-06 : 14:04:18
quote:
Originally posted by robvolk

quote:
The better "enterprise" solution would have been to design a n-tier application and lazy-load/create the "location" as a property of the "file" entity.
I guess it's better to use a cruise ship or aircraft carrier to go fishing in your local pond too.

And what if they need to support another framework/platform besides .Net? The stored procedure does the job and abstracts the database call and makes it consistent to all callers. It can also be changed as needed without requiring any changes to the code that calls it.



No, you still don't understand the meaning of n-tier apps. If they needed to use another framework/platform, you would add another interface layer. Lot's of .NET and Java apps work side-by-side.
Go to Top of Page

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 2009-05-06 : 14:08:44
quote:
Originally posted by Peso

quote:
Originally posted by whitefang

The better "enterprise" solution would have been to design a n-tier application and lazy-load/create the "location" as a property of the "file" entity.

Nicely written, but really doesn't show anything to get OP solve his problem.


E 12°55'05.63"
N 56°04'39.26"




It isn't solvable when there is a fundamental design problem in his application. How does he know the location of the file offhand BUT not the foreign key????
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-05-06 : 14:43:38
quote:
No, you still don't understand the meaning of n-tier apps.
I understand them all too well, I work with and against them every day. The fact is that n-tier is not the be-all end-all solution to enterprise applications. "Enterprise" is not the be-all end-all either.
quote:
If they needed to use another framework/platform, you would add another interface layer
This is exactly the main problem/weakness with enterprise applications, they always "add another layer". Wait until you have 8, 10 or more layers on your systems. I understand that's not the "right way" to do it, but it all too often is the way it gets used. Everyone has to write a new layer to access the same data the same way, just like you suggested; no one ever wants to use an established procedure.
quote:
It isn't solvable when there is a fundamental design problem in his application.
Um, it IS solved, using Peter's stored procedure, and there's no fundamental design problem. Such a technique has been used long before .Net was around, it works fine.
quote:
How does he know the location of the file offhand BUT not the foreign key????
Exactly what is your class code doing that's fundamentally different? Except it's further away from the data, and again, only works for .Net classes. You'd have to duplicate the same class for Java or another language, unless you wrap this class into a web service (yet another layer) that everyone calls.
Go to Top of Page

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 2009-05-06 : 14:57:49
quote:
Originally posted by robvolk

I understand them all too well, I work with and against them every day. The fact is that n-tier is not the be-all end-all solution to enterprise applications. "Enterprise" is not the be-all end-all either.



n-tier is the only solution to all enterprise applications.


quote:
Originally posted by robvolk
This is exactly the main problem/weakness with enterprise applications, they always "add another layer". Wait until you have 8, 10 or more layers on your systems. I understand that's not the "right way" to do it, but it all too often is the way it gets used. Everyone has to write a new layer to access the same data the same way, just like you suggested; no one ever wants to use an established procedure.


This tells me you have no idea what n-tier is. You got your standard 3 layer design and then you got a 4th specialized layer for "interfacing" with other platforms through web services.
Nobody has to write a new layer to access the data. You get full intelli-sense, reflection, refactoring, documentation and more.


quote:
Originally posted by robvolk
Um, it IS solved, using Peter's stored procedure, and there's no fundamental design problem. Such a technique has been used long before .Net was around, it works fine.


The solution is a hack....a workaround (and not even a good one).

quote:
Originally posted by robvolk
Exactly what is your class code doing that's fundamentally different? Except it's further away from the data, and again, only works for .Net classes. You'd have to duplicate the same class for Java or another language, unless you wrap this class into a web service (yet another layer) that everyone calls.



What happens if the OP deletes a file location? How can the OP enter a file before the file location? What if the file location is physically deleted? You got pascal code everywhere. Also, now you need extra stored procedures for all of these.

Notice the accessor methods on the Location property. Also in the factory classes, it verifies the location exists before persistence of a file entity. It verifies that the file physically exists etc.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-06 : 16:02:09
quote:
Originally posted by tuesdaysGreen

Thanks for the quick response Peso!
You're welcome and thank you for the feedback.



E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-06 : 16:04:13
quote:
Originally posted by tuesdaysGreen

I ended up going with Peso's suggestion though since he somehow figured out that what I really wanted was to have my procedure create the Location if it didn't already exist. I didn't even add that requirement in my original post so I don't know how he knew what I wanted... Scary...
Not really. A few years of real-world experiencies and some year of mentoring/tutoring helps
Good luck!


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-06 : 16:08:42
quote:
Originally posted by whitefang

How does he know the location of the file offhand BUT not the foreign key????
Ever heard of FTP?
This is actually a very commonly used scenario when letting more players into the application.
I have met this scenario lots of times before. The company that runs the application make a deal with a new supplier of information and wants only one call to the database; the location and the full filename.

Very easy to understand. Large enterprises have to deal with these scenarios all the time.



E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

tuesdaysGreen
Starting Member

5 Posts

Posted - 2009-05-06 : 17:21:26
Haha, holy crap, my simple post turned into a war zone!

I'm not going to try and argue with any of the guru's here but just so you guys know, I'm just building a simple Silverlight web application that allows me to organize all the files I have in hundreds of cd's I have laying around my house. It's purely a learning experience for me with no intention of ever becoming an enterprise application.

I like the idea of doing most of the work in SQL stored procedures not only because it is a great way to abstract and provide a consistent approach to talking to my database, but also because I really just suck at SQL and want to get better at it. So please don't anyone take my simple application too seriously.
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-05-06 : 20:17:05
quote:
n-tier is the only solution to all enterprise applications.
HAHAHAHAHAHAHAHAHAHAHAHAHAHAHAHAHAHAHAHAHAHAHAHAHAHAHAHAHAHAHAHAHA HAHAHAHAHAHAHAHAHAHAHAHAHAHAHAHAHAHAHAHAHAHAHAHAHAHAHAHAHAHAHAHAHA
HAHAHAHAHAHAHAHAHAHAHAHAHAHAHAHAHAHAHAHAHAHAHAHAHAHAHAHAHAHAHA


Hooo, that's a good one. Thanks for that.
Go to Top of Page

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2009-05-07 : 11:09:12
quote:
Originally posted by whitefang

The better "enterprise" solution would have been to design a n-tier application and lazy-load/create the "location" as a property of the "file" entity.


An excellent strategy for overloading your network capacity.

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________
Go to Top of Page

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 2009-05-07 : 11:46:17
Since you guys have no real enterprise experience, I can tell you that the n-tier approach works faster.

For an example, say you have a million files in a million folders.

Procedure:
Read files and folders in root
For each file
- Check if location exist and if it does, get ID
- Insert file with ID

sql solution:
application reads 1 file at a time and sends to stored procedure 1 by 1 sequentially (the caveman technique)...this is exactly what the OP is doing. It's embarrassing that the "highly experienced enterprised" DBAs above posted a sequential solution for a set based language.

n-tier solution:
Read files and folders into an collection of entities
Extracts the "file locations" and build a batch sql statement (example, FileCollection.GetLocations())
Insert all the file locations, get back a result set with the ids (example, FileLocationFactory.Save(locations));
The file locations are now on the application side
Create a batch sql statement for all the files by the file locations (example, FileCollection.Save(files)). Because the file locations are on the app side, its lightning fast and done in 2 SQL batch statements. This is the proper enterprise way of doing things.
Go to Top of Page

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 2009-05-07 : 11:53:36
quote:
Originally posted by blindman
An excellent strategy for overloading your network capacity.



Yes, because the OP said the application server and sql server are the same machine so it has to go through some magical network. Also, not everyone uses routers/switches from the 1950s.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-07 : 12:07:28
quote:
Originally posted by whitefang

Since you guys have no real enterprise experience, I can tell you that the n-tier approach works faster.
What? He only wants to store the catalog and filename.
Do a DIR command in a CMD window will give you the same result, by storing each line to the procedure. With the "ugly hack" above this is doable quite fast and you don't have to worry about which order the catalogs arrive.

The point is to NOT have to call the database TWICE for storing ONE file.

So other than that, how's spring in Moscow?


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page
  Previous Page&nsp;  Next Page

- Advertisement -