A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.
I'm just teaching myself how to do stored procedures in SQL Server, and how to interact between Delphi 6 Enterprise and SQL Server 2005. I have a lot of dBASE-type DB experience including StreamlineSQL with the Advantage Database, but I have never written a stored procedure in TransactSQL until today.
I have created a client dataset in Delphi and users use that to choose one or more records. When the users click "finish" a series of records need to be created in the SQL Server database on the server, each with the ID of a chosen record from the client dataset. Example:
X Swimming Pool
The user chose to go to the swimming pool and sauna, so two "chosen activities" records need to be created in my database. Remember, the "source" table above only exists in memory on the client computer so I can't use it as part of an INSERT statement in a stored procedure on the server.
Here is my question: what is the best way to pass this series of IDs to the stored procedure so they can be added as separate records in a table?
I could create a pseudo-array structure using a string parameter to my stored procedure, but that's a lot of work.
Is there a simpler way?
Never mind. I did a Google search and discovered the use of XML. Wow. How cool.
I built an XML structure from the data, then passed it to my stored procedure as a varchar parameter, then used the sp_xml_preparedocument and OpenXML features of SQL Server to insert it into my tables.
"Oh no! You must be joking..."
As a SQL Server newbie I can't recognize what you are disparaging. Would you please elaborate? If I'm doing something stupid I'd like to know now.
I'm using Delphi 6, which was the last version prior to their .Net-compatible versions. So with D6 I can't use that function. However, last night after the 'never mind' post I spent time to develop XML strings to feed into my stored procedures, and that works fantastic.
I guess I spoke too soon.
I thought there was an easier way to do this,
but I can't remember... (I was thinking about something along the lines of Bulk Copy)
To save my skin I send you this link:
But that's not very pretty, and more along the lines of your initial array idea.
So your solution is fine with me :)
By the way:
You are never doing anything stupid.
A (working) solution to a problem can never be stupid:
It's just that some solutions can be less efficient than others ;)
Not Steve Mcconnell
Monday, June 26, 2006
Can't you also just concatenate multiple sql statements together with a semicolon betewen them?
insert into1 (fields) values (values);insert into1 (fields) values (values);insert into1 (fields) values (values);insert into1 (fields) values (values);insert into1 (fields) values (values);insert into1 (fields) values (values);
and execute it?
Bulk Copy is good idea. Not a hugh MS-SQL expert but in other RDBMS you can use prepared statements with a bunch of data you want INSERTED.
You prepare the statement, then upload the data, and let all the processing take place server side.
Oracle, Progress, DB2, and PostgreSQL all do this. I wonder if this is of any help?
Thursday, June 29, 2006
If you go the multiple SQL queries seperated by a semi-colon route then take a look at stuffing them in a transaction.
Sunday, July 02, 2006
This topic is archived. No further replies will be accepted.Other recent topics
Powered by FogBugz