Inserting records and running a stored procedure at the same time?

Hi there..
The target table got these two 'must be set columns', one called DATASET, an
d
one called LXBENUMMER.
Now, when I want to insert data into this target table I need to fill those
two
columns with the new records I'm inserting. DATASET is easy as it'll always
be a
fixed value, however, LXBENUMMER must be set with a number that comes from a
stored procedure.
If I do an exec sp_xal_seqno 1, 'DAT' I get a number back, which must be put
in
LXBENUMMER with the new record.
An example-
insert tbl1
(dataset,lxbenummer,col1,col2,col3,etc)
select 'DAT',**value from stored procedure**, col1, col2, col3, etc
from tbl2
How do I go about this?
I doubt, therefore I might be.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.
Also, you do not know that a column is not anything like a field and
that a row is not a record. After 20+ years of doing SQL and charging
a lot of money for consulting work in correctly schemas, this is a
signal that you are really screwed up.|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1129947794.955269.92220@.g43g2000cwa.googlegroups.com

> Also, you do not know that a column is not anything like a field and
> that a row is not a record. After 20+ years of doing SQL and charging
Well everywhere I look, row and record is the same and is used interchangeab
le.
Why should I think different? I don't see where I wrote field and compared i
t
with a column; but well you do know better.

> a lot of money for consulting work in correctly schemas, this is a
> signal that you are really screwed up.
Dude, you need to take a break from time to time, instead of throwing dirt a
t
those to aren't "worthy" compared to you. I'd see your point better if you h
ad
kept it nicer.
I'm screwed up if I write nutty SQL? Man, if you think that, you /seriously/
need to take a break from anything relating to computers.
Anyway, I shall post again when I have a DDL ready.
--
I doubt, therefore I might be.|||Kim Noer (kn@.nospam.dk) writes:
> The target table got these two 'must be set columns', one called
> DATASET, and one called LXBENUMMER.
> Now, when I want to insert data into this target table I need to fill
> those two columns with the new records I'm inserting. DATASET is easy as
> it'll always be a fixed value, however, LXBENUMMER must be set with a
> number that comes from a stored procedure.
> If I do an exec sp_xal_seqno 1, 'DAT' I get a number back, which must be
> put in LXBENUMMER with the new record.
General comment: don't use sp_ as the leading charcters in the name of
your objects. This prefix is reserved for SQL Server, and SQL Server will
first look in master for these objects.

> An example-
> insert tbl1
> (dataset,lxbenummer,col1,col2,col3,etc)
> select 'DAT',**value from stored procedure**, col1, col2, col3, etc
> from tbl2
> How do I go about this?
There are number of options, of which some requires you to change
the procedure. I happen to have an article about this on my web site:
http://www.sommarskog.se/share_data.html.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||From your attitude i'd be suprised if anybody outside of education has hired
you in the past couple of years!
You need to bring your skills up-to-date with what business wants now, not
15 years ago.
Times have significantly changed and you seem to have been left behind.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1129947794.955269.92220@.g43g2000cwa.googlegroups.com...
> 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.
> Also, you do not know that a column is not anything like a field and
> that a row is not a record. After 20+ years of doing SQL and charging
> a lot of money for consulting work in correctly schemas, this is a
> signal that you are really screwed up.
>|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1129947794.955269.92220@.g43g2000cwa.googlegroups.com
> 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.
CREATE TABLE [tbl1] (
[DATASET] [varchar] (3) COLLATE SQL_Danish_Pref_CP1_CI_AS NOT NULL ,
[LXBENUMMER] [int] NOT NULL ,
[col1] [varchar] (30) COLLATE SQL_Danish_Pref_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [XALSEQ] (
[SEQID] [int] NOT NULL ,
[DATASET] [varchar] (3) COLLATE SQL_Danish_Pref_CP1_CI_AS NOT NULL ,
[SEQNO] [int] NOT NULL
) ON [PRIMARY]
GO
INSERT INTO XALSEQ
VALUES (0,'DAT',1000)
GO
INSERT INTO tbl1
VALUES ('DAT',1000,'somerandomtext')
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE sp_xal_seqno @.increment INT, @.dataset CHAR(3) AS
BEGIN TRAN
UPDATE XALSEQ SET SEQNO = SEQNO + @.increment
WHERE DATASET = @.dataset AND SEQID = 0
SELECT SEQNO - @.increment FROM XALSEQ
WHERE DATASET = @.dataset AND SEQID = 0
COMMIT TRAN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
I doubt, therefore I might be.|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns96F7D40BB5DE7Yazorman@.127.0.0.1

> General comment: don't use sp_ as the leading charcters in the name of
> your objects. This prefix is reserved for SQL Server, and SQL Server
> will first look in master for these objects.
Aye, I name all those I create differently, but alas, I did not get to chose
the
name of the stored procedure, this one comes from MBS themself.

> There are number of options, of which some requires you to change
> the procedure. I happen to have an article about this on my web site:
> http://www.sommarskog.se/share_data.html.
In my second post to Celko, I've included the structure of the tables, the S
P
and tiny amount of sample data, if that helps point me in the correct direct
ion
(I'm going to read up on your article anyway though).
Basically, the whole purpose is to make sure that every single row (or is it
record?) get their very own unique number. It would be exceedingly lovely if
any
solution to this particular problem could be made generic if possible.
Thanks in advance.
--
I doubt, therefore I might be.|||Kim Noer (kn@.nospam.dk) writes:
> Aye, I name all those I create differently, but alas, I did not get to
> chose the name of the stored procedure, this one comes from MBS
> themself.
MBS? That's some third-party software?

> Basically, the whole purpose is to make sure that every single row (or
> is it record?) get their very own unique number. It would be exceedingly
> lovely if any solution to this particular problem could be made generic
> if possible.
It would certainly be more convenient if it was an OUTPUT parameter
rather than a result ser. If you can't change the procedure, you will
have to use INSERT EXEC.
Besides this looks funny:
CREATE TABLE [XALSEQ] (
[SEQID] [int] NOT NULL ,
[DATASET] [varchar] (3) COLLATE SQL_Danish_Pref_CP1_CI_AS NOT NULL ,
[SEQNO] [int] NOT NULL
) ON [PRIMARY]
GO
It doesn't have a primary key?
If there are no indexes on the table, this means that SQL Server will
have to take a out a table lock to give you a sequence number, and thus
no other will be able to get a sequence number simultaneously.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog wrote:

> MBS? That's some third-party software?
Microsoft Business Solution, they bought a company named Navision, which
makes applications that primarily talks flat fileish. In my case the
application uses cursors, a /lot/ of cursors. They do plan to change this,
but that's 2+ years in the future atleast.
I'm trying to insert data from the "outside" of this application, which
means that I can use all kinds of RDBMS tricks.

> It would certainly be more convenient if it was an OUTPUT parameter
> rather than a result ser. If you can't change the procedure, you will
> have to use INSERT EXEC.
I'm sure can write a new, as long as I retain the functionality. Which one
of your methods would you use then?

> It doesn't have a primary key?
> If there are no indexes on the table, this means that SQL Server will
> have to take a out a table lock to give you a sequence number, and
> thus no other will be able to get a sequence number simultaneously.
Maybe they forgot, or the application might not be able to handle such a
situation.
Necessity is the plea for every infringement of human freedom. It is
the argument of tyrants; it is the creed of slaves. -- William Pitt,
1783|||Kim Noer (kn@.nospam.dk) writes:
> I'm sure can write a new, as long as I retain the functionality. Which one
> of your methods would you use then?
OUTPUT parameter.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp