I am trying to create a stored procedure which will allow multiple inserts to happen on one table.
I cant find any information on this.
Please can someone help?.
Thanks
JagK
Hi JagK,
You would call multiple times to stored procedure.
Good Coding!
Javier Luna
http://guydotnetxmlwebservices.blogspot.com/
I just need a bit of advice as to where to start tackling a problem, if
thats possible - thanks very much.
I need a single stored procedure to make several inserts into my msde
database. There will be two arguments to the stored proc. The first
is a title argument which needs to be inserted into the first table
after which the autonumbered primary key is captured with @.@.identity.
The second argument is a delimited list of foreign keys which need to
be inserted into the second table along with the new key from the first
statement. This table is a link table with two columns - both foreign
keys - ie its the link table in a many to many relationship.
My problems is that as far as I know I can't use arrays in sql server
cause it doesn't support them. And this has come about because I don't
know how many rows need to be inserted into the link table. But there
will always be at least one.
I know I need to do this in a loop, but how do I split up the the
second argument so that I can?
Thanks,
MarkOn 27 Jun 2005 08:22:10 -0700, Mark wrote:
>Hi everyone,
>I just need a bit of advice as to where to start tackling a problem, if
>thats possible - thanks very much.
>I need a single stored procedure to make several inserts into my msde
>database. There will be two arguments to the stored proc. The first
>is a title argument which needs to be inserted into the first table
>after which the autonumbered primary key is captured with @.@.identity.
>The second argument is a delimited list of foreign keys which need to
>be inserted into the second table along with the new key from the first
>statement. This table is a link table with two columns - both foreign
>keys - ie its the link table in a many to many relationship.
>My problems is that as far as I know I can't use arrays in sql server
>cause it doesn't support them. And this has come about because I don't
>know how many rows need to be inserted into the link table. But there
>will always be at least one.
>I know I need to do this in a loop, but how do I split up the the
>second argument so that I can?
>Thanks,
>Mark
Hi Mark,
Check out this site for a wealth of possible solutions:
http://www.sommarskog.se/arrays-in-sql.html
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||>> I just need a bit of advice as to where to start tackling a problem, if that is possible - thanks very much. <<
You need to go back to foundations. You missed the major points of
RDBMS and are trying to write a 1950's file system in SQL.
1) Autonumbering is totally non-relational and cannot be a key by
definition. This is foundations, not fancy stuff. A key is a subset
of attributes that makes a row unique within a table; it has to do with
the data model and not the current state of the hardware on which the
data is stored.
2) An INSERT INTO statement works on one and only one base table.
3) There are only scalar value parameters; there are no lists, arrays,
etc. There are a bunch of kludges where you write a parser in T-SQL,
if you do not care about maintaining or porting your code.
4) There is no such term as "link table" -- link is a term from
navigational databases and assembly language. It is a many-to-many
relationship.
5) We do not like to write procedural code, so you should avoid loops.
6) You do not insert keys into a table; you insert rows. WHOLE rows.
You probably have more (non-key) columns in the second table to fill
in.
7) Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
Your code should look something like this, assuming a vanilla invoice
schema:
BEGIN
INSERT INTO Invoices (invoice_nbr, ..)
VALUES (@.new_invoice_nbr, .. );
INSERT INTO InvoiceDetails (invoice_nbr, sku..)
SELECT @.new_invoice_nbr, sku..
FROM WorkingTable;
END;
Invoice_nbr should have a CHECK() constraint to validate it, of course.|||This is the version of split array return table from sommarskog. I use this
to pass in arrays of keys (space separated). It's more efficient than
populating a temporary table with keys programmatically every time you need
to join (as Celko seems to suggest). Although in general Celko is correct
from a purest point of view, I do believe that a small function like this
allowing you to pass and split arrays in a stored procedure has more utility
than it does downsides. For example, my client needs to periodically check
a set of rows to see if they have been changed. These records are in no
particular order (whichever rows the user happens to be viewing). Instead
of writing each one to a working table one by one and then executing an SP
to check their timestamps, I pass in an array of keys, split it and join
with the split table to return the update state.
However, in your example, perhaps a working table would be a better idea. I
would only advise using array splitting algorithms server-side if they are
just autonumber unique keys, rather than whole rows of information. It's
just a quickish method for fetching arbitrary rows from your tables.
CREATE FUNCTION dbo.func_Split_Array_Return_Table (@.list NTEXT)
RETURNS @.Table TABLE ( listpos INT IDENTITY(1, 1) NOT NULL, number INT NOT
NULL) AS
BEGIN
DECLARE @.pos INT, @.textpos INT, @.chunklen SMALLINT, @.str NVARCHAR(4000),
@.tmpstr NVARCHAR(4000), @.leftover NVARCHAR(4000)
SET @.textpos = 1
SET @.leftover = ''
WHILE @.textpos <= datalength(@.list) / 2
BEGIN
SET @.chunklen = 4000 - datalength(@.leftover) / 2
SET @.tmpstr = ltrim(@.leftover + substring(@.list, @.textpos,
@.chunklen))
SET @.textpos = @.textpos + @.chunklen
SET @.pos = charindex(' ', @.tmpstr)
WHILE @.pos > 0
BEGIN
SET @.str = substring(@.tmpstr, 1, @.pos - 1)
INSERT @.Table (number)
VALUES
(convert(int, @.str))
SET @.tmpstr = ltrim(substring(@.tmpstr, @.pos + 1, len(@.tmpstr)))
SET @.pos = charindex(' ', @.tmpstr)
END
SET @.leftover = @.tmpstr
END
IF ltrim(rtrim(@.leftover)) <> ''
INSERT @.Table (number)
VALUES
(convert(int, @.leftover))
RETURN
END
"Mark" <mjmather@.gmail.com> wrote in message
news:1119885730.339475.84280@.g47g2000cwa.googlegro ups.com...
> Hi everyone,
> I just need a bit of advice as to where to start tackling a problem, if
> thats possible - thanks very much.
> I need a single stored procedure to make several inserts into my msde
> database. There will be two arguments to the stored proc. The first
> is a title argument which needs to be inserted into the first table
> after which the autonumbered primary key is captured with @.@.identity.
> The second argument is a delimited list of foreign keys which need to
> be inserted into the second table along with the new key from the first
> statement. This table is a link table with two columns - both foreign
> keys - ie its the link table in a many to many relationship.
> My problems is that as far as I know I can't use arrays in sql server
> cause it doesn't support them. And this has come about because I don't
> know how many rows need to be inserted into the link table. But there
> will always be at least one.
> I know I need to do this in a loop, but how do I split up the the
> second argument so that I can?
> Thanks,
> Marksql
Hello,
I am looking at writing a SP without much success which enables multiple filtering on one field. Something like below:
Input field: Product Description
So if the user enters: "Large Drill" OR "Drill Large" the same resultset will be returned.
SELECT * FROM products WHERE products.prod_desc contains both "Large" AND "Drill"
I guess there'll need to be a nested Select and loop to parse the space separated input field.
Any pointers would be appreciated.
Thank you
Lee
hi dear,
I answer the same question on MSDN forum....you can reach this via this link
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1448983&SiteID=1
Thank You
Best Regards,
Muhammad Akhtar Shiekh
|||Thank you very much Muhammad
Problem solved.
I have a job with a single step that executes a stored procedure that performs the following steps:
1. Checks for the existance of a file A in a folder A
2. If it exists,
a. executes the cmdshell to run a DTS package to drop a table, recreate it and load the data in the file A to table X
b. runs other stored procedures that use the data in table X to create other tables Y and Z
c. executes the cndshell to remove and rename the file A from Folder A into Folder B
What I'd like to do is use this same stored procedure if possible, but create a job or another store procedure that would loop thru and process multiple files in Folder A instead of just one.
Any suggestions would be greatly appreciated
My post in this thread should point you in a workable direction.
have a stored procedure with a transaction that creates a contract for
a customer in a Contract table. After the contract record is created, I need
to create the contract items/products with their properties (price,
notes, etc), which belong in a linked table, so that they're created
all within the same transaction (the contract, and the products), so
that if either the contract or any of the items cannot be inserted into
their correct tables, it will rollback and undo the creation of the
contract. Now, I understand transactions and how they work, but my
trouble is that I'm not sure if I can use a dynamic insert
statement to create the contract items/products, within the same
transaction where the contract is created.
I'm new to sp_executesql, which seems to be the recommended method for dynamic sql in a stored proc, but if I understand it correctly, I'd have to call a stored proc for each item i wanted to insert something into the ContractItem table with a dynamic insert statement. Is there any way to have multiple dynamically created insert statements in one stored procedure? Help! I hope I explained my problem well enough. thanks in advance!
You can execute sp_executesql as many times as you like in a stored procedure. Simply begin a transaction at the start of the proc then either rollback or commit the transaction when appropriate.
Incidentally, is there a particular reason that you are choosing to use dynamic SQL over defining stored procedures that parameters can be passed into?
Chris
|||Chris Howarth wrote:
You can execute sp_executesql as many times as you like in a stored procedure. Simply begin a transaction at the start of the proc then either rollback or commit the transaction when appropriate.
I understand this, I guess my problem really is this: the number of rows I will have to insert into the ContractItem table will vary each time this stored procedure to create a Contract and it's ContractItems is called. So how do I pass that (variable amount of) data into the stored prodedure to create the dynamic insert statements?
Incidentally, is there a particular reason that you are choosing to use dynamic SQL over defining stored procedures that parameters can be passed into?
Not really, I'm still sort of new to stored procedures though, if that is any kind of an excuse :) If this would be a better way to do it, then my plan should probably change slightly...
|||How you choose to do this very much depends on your application's architecture.
If you have a web server or application server that is executing your stored procedures then it would probably make sense to create a single INSERT stored proc (designed to insert one row at a time) per table and then get your web/app server to execute the stored procedure once per row, passing in the parameters as required. Any iteration that needs to take place can then be performed by the web/app server.
You can start a transaction at the connection level, so as long as your web/app server uses the same connection to execute all stored procs that are part of the 'business transaction' then all of the changes will be rolled back should an error be raised.
Does this fit your scenario?
Chris
|||Chris,This does fit my scenario... a transaction at the connection level sound just like what I need to get this to work properly. Now I just have to go dig up how to do that. Thank you so much for your help!
Alter Procedure "mySPName"
@.UniqueID int
AS
set nocount on
set xact_abort off
DELETE FROM tblNameOne
WHERE
(tblNameOne.UniqueID = @.UniqueID)
DELETE FROM tblNameTwo
WHERE
(tblNameTwo.UniqueID = @.UniqueID)
Is it a good idea to run multiple detele statements within one SP?
thanks,
lqOn 8 Apr 2004 12:30:55 -0700, Lauren Quantrell wrote:
>Just wondering if this is good form:
Looks fine to me, except I'd prefer set xact_abort on. But that's a
general comment, your situation mught demand this option to be off.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks,
I am having a situation were the SQL server experiences every now and
then excessive blocking errors, sometimes around the time of execution
of this type of stored procedure, sometimes around the time of an SP
where I'm running multiple INSERT queries with one SP. I'm trying to
identify where the problem may be.
I'm wondering if you could tell me what the use of GO or RETURN is and
if I need them in this sort of SP?
lq
Hugo Kornelis <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message news:<v1bb70td57mtifd8le40ot4kv9gp118as9@.4ax.com>...
> On 8 Apr 2004 12:30:55 -0700, Lauren Quantrell wrote:
> >Just wondering if this is good form:
> Looks fine to me, except I'd prefer set xact_abort on. But that's a
> general comment, your situation mught demand this option to be off.
> Best, Hugo|||On 9 Apr 2004 06:42:55 -0700, Lauren Quantrell wrote:
>Thanks,
>I am having a situation were the SQL server experiences every now and
>then excessive blocking errors, sometimes around the time of execution
>of this type of stored procedure, sometimes around the time of an SP
>where I'm running multiple INSERT queries with one SP. I'm trying to
>identify where the problem may be.
I don't know much about blocking. The only thing I know is that you
can use sp_who to identify which process all other processes are
waiting for. The only action I have ever taken in these situations was
to either kill the blocking process or tell the complaining users that
this process was too important to postpone until the evening.
If you find a procedure like this to be the cause of blocking, there
are probably ways to improve this. However, I don't know how to do
that. Maybe you should ask a new question, making sure that "blocking"
is in the subject line. Also, note that there are a lot of groups
devoted to SQL Server in the microsoft.public.sqlserver hierarchy. A
question about blocking could go in either .programming or .server.
>I'm wondering if you could tell me what the use of GO or RETURN is and
>if I need them in this sort of SP?
RETURN is used to exit immediately from a stored procedure or trigger.
Check Books Online for more detailed description and examples. Use it
if you detect a situation where the remaining statements in the
procedure should not be executed.
GO means "end of batch". It is intercepted by Query Analyzer (as well
as OSQL, ISQL and probably other tools as well) and prompts them to
send everything to the server. Therefor, you can't put GO inside a
procedure. Example:
Create procedure Testit
as
select * from sysobjects
go
select * from sysfiles
go
Execute this, and all rows in sysfiles will be listed. Next, execute
"sp_helptext Testit" and you'll see that only the select from
sysobjects made it into the procedure. The other select was sent as a
seperate batch and therefor executed immediately.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||If i am not wrong, i think the GO statment is used for Batch Termination.
Some of the SQL Statments don't allow some Commands to be executed along
with DDL. So the word GO can be used to tell the SQL Server that one batch
finished and the other batch is ready. The return statement can be used when
checking for error codes and returning an error code(from a procedure) to
the appropriate procedure/SQL.
"Lauren Quantrell" <laurenquantrell@.hotmail.com> wrote in message
news:47e5bd72.0404090542.db112d9@.posting.google.co m...
> Thanks,
> I am having a situation were the SQL server experiences every now and
> then excessive blocking errors, sometimes around the time of execution
> of this type of stored procedure, sometimes around the time of an SP
> where I'm running multiple INSERT queries with one SP. I'm trying to
> identify where the problem may be.
> I'm wondering if you could tell me what the use of GO or RETURN is and
> if I need them in this sort of SP?
> lq
>
> Hugo Kornelis <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:<v1bb70td57mtifd8le40ot4kv9gp118as9@.4ax.com>...
> > On 8 Apr 2004 12:30:55 -0700, Lauren Quantrell wrote:
> > >Just wondering if this is good form:
> > Looks fine to me, except I'd prefer set xact_abort on. But that's a
> > general comment, your situation mught demand this option to be off.
> > Best, Hugo|||Have you looked at triggers? We've got an idiot vendor (who should
be gone by the end of the year) who implemented their referential
integrity with triggers, rather than real foreign keys.
We make up for that with an excessive use of cursors and nolock
hints, but if you have the option of actually fixing the trigger,
it would be better.
Bill
Lauren Quantrell wrote:
> Thanks,
> I am having a situation were the SQL server experiences every now and
> then excessive blocking errors, sometimes around the time of execution
> of this type of stored procedure, sometimes around the time of an SP
> where I'm running multiple INSERT queries with one SP. I'm trying to
> identify where the problem may be.
> I'm wondering if you could tell me what the use of GO or RETURN is and
> if I need them in this sort of SP?
> lq
>
> Hugo Kornelis <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message news:<v1bb70td57mtifd8le40ot4kv9gp118as9@.4ax.com>...
>>On 8 Apr 2004 12:30:55 -0700, Lauren Quantrell wrote:
>>
>>
>>>Just wondering if this is good form:
>>
>>Looks fine to me, except I'd prefer set xact_abort on. But that's a
>>general comment, your situation mught demand this option to be off.
>>
>>Best, Hugo
Is it possible to pass multiple datatables to one report (within one dataset)?
My stored procedure would have two or more select statements.
Only one resultset is supported.
You could add a parameter to the stored procedure (or write a wrapper). The parameter determines which resultset is returned. You would then define multiple datasets in the RS report and call the stored procedure with different parameter values.
-- Robert
|||Thanks, I guess I need to research multiple datasets in the report. I imagine there is a custom assembly involved?
|||Using multiple datasets in a report has nothing to do with custom assemblies.
Multiple datasets just means you need to have multiple data regions (list, table, matrix, chart) in the report to show the data.
-- Robert
