How to load in data and insert data together with a stored procedure
I need to load in data first and then insert data into the same table.
I use a stored procedure to load in the data (I'm using SQL Server):
@employeeid int,
@thequestion varchar (220) output
)
as
begin
begin transaction
select @thequestion = thequestion
from question q
join contentment c on q.questionid= c.questionid
where c.employeeid = @employeeid
if @@ERROR <> 0
begin
rollback
raiserror ('You don't have question to answer', 16, 1)
return
end
commit
end
Then the user can add data to the table contentment. He only can add a score and a comment
Contentment table:
employeeid,
questionid,
date,
score,
comment
I using this stored procedure:
(@score int,
@comment varchar(50),
@date date
)
as
begin
begin transaction
insert into contentment (date, score, comment)
values (@date, @score, @comment)
if @@ERROR <> 0
begin
rollback
raiserror ('-some error-', 16, 1)
return
end
commit
end
The problem is that in the second stored procedure questionid and employeeid should not be inserted, it is already inserted, to link a employeeid to a questionid. But when I want to add a score and comment to this, I get an error that questionid and employeeid need to be inserted (otherwise it has value NULL). Also the problem is that my second stored procedure doesn't know which question belongs to the questionid/employeeid. I hope someone understand this, I know it is a bit weird.
sql-server tsql edited Nov 12 '18 at 12:44
marc_s
572k12811051252
asked Nov 12 '18 at 12:23
DutchFatBoysDutchFatBoys
487
add a comment |
I need to load in data first and then insert data into the same table.
I use a stored procedure to load in the data (I'm using SQL Server):
@employeeid int,
@thequestion varchar (220) output
)
as
begin
begin transaction
select @thequestion = thequestion
from question q
join contentment c on q.questionid= c.questionid
where c.employeeid = @employeeid
if @@ERROR <> 0
begin
rollback
raiserror ('You don't have question to answer', 16, 1)
return
end
commit
end
Then the user can add data to the table contentment. He only can add a score and a comment
Contentment table:
employeeid,
questionid,
date,
score,
comment
I using this stored procedure:
(@score int,
@comment varchar(50),
@date date
)
as
begin
begin transaction
insert into contentment (date, score, comment)
values (@date, @score, @comment)
if @@ERROR <> 0
begin
rollback
raiserror ('-some error-', 16, 1)
return
end
commit
end
The problem is that in the second stored procedure questionid and employeeid should not be inserted, it is already inserted, to link a employeeid to a questionid. But when I want to add a score and comment to this, I get an error that questionid and employeeid need to be inserted (otherwise it has value NULL). Also the problem is that my second stored procedure doesn't know which question belongs to the questionid/employeeid. I hope someone understand this, I know it is a bit weird.
sql-server tsql edited Nov 12 '18 at 12:44
marc_s
572k12811051252
asked Nov 12 '18 at 12:23
DutchFatBoysDutchFatBoys
487
Are these SPs being called from code? (If so, tag a language.) In my experience, theres.no point putting transactions around atomic statements (which have implicit transactions) - transactions can be used in the calling code to group different units of work. Raising errors is similar - the calling code can determine what happened, and react accordingly.
– Richardissimo
Nov 16 '18 at 6:05
add a comment |
I need to load in data first and then insert data into the same table.
I use a stored procedure to load in the data (I'm using SQL Server):
@employeeid int,
@thequestion varchar (220) output
)
as
begin
begin transaction
select @thequestion = thequestion
from question q
join contentment c on q.questionid= c.questionid
where c.employeeid = @employeeid
if @@ERROR <> 0
begin
rollback
raiserror ('You don't have question to answer', 16, 1)
return
end
commit
end
Then the user can add data to the table contentment. He only can add a score and a comment
Contentment table:
employeeid,
questionid,
date,
score,
comment
I using this stored procedure:
(@score int,
@comment varchar(50),
@date date
)
as
begin
begin transaction
insert into contentment (date, score, comment)
values (@date, @score, @comment)
if @@ERROR <> 0
begin
rollback
raiserror ('-some error-', 16, 1)
return
end
commit
end
The problem is that in the second stored procedure questionid and employeeid should not be inserted, it is already inserted, to link a employeeid to a questionid. But when I want to add a score and comment to this, I get an error that questionid and employeeid need to be inserted (otherwise it has value NULL). Also the problem is that my second stored procedure doesn't know which question belongs to the questionid/employeeid. I hope someone understand this, I know it is a bit weird.
sql-server tsql edited Nov 12 '18 at 12:44
marc_s
572k12811051252
asked Nov 12 '18 at 12:23
DutchFatBoysDutchFatBoys
487
I need to load in data first and then insert data into the same table.
I use a stored procedure to load in the data (I'm using SQL Server):
@employeeid int,
@thequestion varchar (220) output
)
as
begin
begin transaction
select @thequestion = thequestion
from question q
join contentment c on q.questionid= c.questionid
where c.employeeid = @employeeid
if @@ERROR <> 0
begin
rollback
raiserror ('You don't have question to answer', 16, 1)
return
end
commit
end
Then the user can add data to the table contentment. He only can add a score and a comment
Contentment table:
employeeid,
questionid,
date,
score,
comment
I using this stored procedure:
(@score int,
@comment varchar(50),
@date date
)
as
begin
begin transaction
insert into contentment (date, score, comment)
values (@date, @score, @comment)
if @@ERROR <> 0
begin
rollback
raiserror ('-some error-', 16, 1)
return
end
commit
end
The problem is that in the second stored procedure questionid and employeeid should not be inserted, it is already inserted, to link a employeeid to a questionid. But when I want to add a score and comment to this, I get an error that questionid and employeeid need to be inserted (otherwise it has value NULL). Also the problem is that my second stored procedure doesn't know which question belongs to the questionid/employeeid. I hope someone understand this, I know it is a bit weird.
sql-server tsql sql-server tsql edited Nov 12 '18 at 12:44
marc_s
572k12811051252
asked Nov 12 '18 at 12:23
DutchFatBoysDutchFatBoys
487
edited Nov 12 '18 at 12:44
marc_s
572k12811051252
asked Nov 12 '18 at 12:23
DutchFatBoysDutchFatBoys
487
edited Nov 12 '18 at 12:44
marc_s
572k12811051252
edited Nov 12 '18 at 12:44
marc_s
572k12811051252
edited Nov 12 '18 at 12:44
marc_s
572k12811051252
572k12811051252
asked Nov 12 '18 at 12:23
DutchFatBoysDutchFatBoys
487
asked Nov 12 '18 at 12:23
DutchFatBoysDutchFatBoys
487
asked Nov 12 '18 at 12:23
DutchFatBoysDutchFatBoys
487
487
Are these SPs being called from code? (If so, tag a language.) In my experience, theres.no point putting transactions around atomic statements (which have implicit transactions) - transactions can be used in the calling code to group different units of work. Raising errors is similar - the calling code can determine what happened, and react accordingly.
– Richardissimo
Nov 16 '18 at 6:05
add a comment |
Are these SPs being called from code? (If so, tag a language.) In my experience, theres.no point putting transactions around atomic statements (which have implicit transactions) - transactions can be used in the calling code to group different units of work. Raising errors is similar - the calling code can determine what happened, and react accordingly.
– Richardissimo
Nov 16 '18 at 6:05
Are these SPs being called from code? (If so, tag a language.) In my experience, theres.no point putting transactions around atomic statements (which have implicit transactions) - transactions can be used in the calling code to group different units of work. Raising errors is similar - the calling code can determine what happened, and react accordingly.
– Richardissimo
Nov 16 '18 at 6:05
Are these SPs being called from code? (If so, tag a language.) In my experience, theres.no point putting transactions around atomic statements (which have implicit transactions) - transactions can be used in the calling code to group different units of work. Raising errors is similar - the calling code can determine what happened, and react accordingly.
– Richardissimo
Nov 16 '18 at 6:05
add a comment |
1 Answer
1
active
oldest
votes
Sorry I was busy travelling.
I tried creating 2nd procedure as follows.
Here I have created employeeid and questionid as 2 variables which we will passs while executing this stored procedure:
create procedure Proc2
(
@employeeid int
,@questionid int
,@score int
,@comment varchar(50)
,@date date
)
as
begin
begin transaction
insert into contentment (date, score, comment)
select @date = date, @score = score, @comment = comment
where employeeid = @employeeid and questionid = @questionid
if @@ERROR <> 0
begin
rollback
raiserror ('-some error-', 16, 1)
return
end
end transaction
commit
end
answered Nov 12 '18 at 12:41
Pratik SomaiyaPratik Somaiya
1085
I don't understand what you mean?
– DutchFatBoys
Nov 12 '18 at 16:13
What I meant was you can have a function/stored procedure which takes the questionid/employeeid as input parameters. These input parameters can then be passed in the insert statement in WHERE clause, thereby avoiding NULL values for questionid/employeeid, also these 2 things will not be inserted again rather they will be used as conditions.
– Pratik Somaiya
Nov 13 '18 at 8:34
Could you maybe show me how you will do this? Thanks.
– DutchFatBoys
Nov 13 '18 at 9:55
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53262152%2fhow-to-load-in-data-and-insert-data-together-with-a-stored-procedure%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Sorry I was busy travelling.
I tried creating 2nd procedure as follows.
Here I have created employeeid and questionid as 2 variables which we will passs while executing this stored procedure:
create procedure Proc2
(
@employeeid int
,@questionid int
,@score int
,@comment varchar(50)
,@date date
)
as
begin
begin transaction
insert into contentment (date, score, comment)
select @date = date, @score = score, @comment = comment
where employeeid = @employeeid and questionid = @questionid
if @@ERROR <> 0
begin
rollback
raiserror ('-some error-', 16, 1)
return
end
end transaction
commit
end
answered Nov 12 '18 at 12:41
Pratik SomaiyaPratik Somaiya
1085
I don't understand what you mean?
– DutchFatBoys
Nov 12 '18 at 16:13
What I meant was you can have a function/stored procedure which takes the questionid/employeeid as input parameters. These input parameters can then be passed in the insert statement in WHERE clause, thereby avoiding NULL values for questionid/employeeid, also these 2 things will not be inserted again rather they will be used as conditions.
– Pratik Somaiya
Nov 13 '18 at 8:34
Could you maybe show me how you will do this? Thanks.
– DutchFatBoys
Nov 13 '18 at 9:55
add a comment |
Sorry I was busy travelling.
I tried creating 2nd procedure as follows.
Here I have created employeeid and questionid as 2 variables which we will passs while executing this stored procedure:
create procedure Proc2
(
@employeeid int
,@questionid int
,@score int
,@comment varchar(50)
,@date date
)
as
begin
begin transaction
insert into contentment (date, score, comment)
select @date = date, @score = score, @comment = comment
where employeeid = @employeeid and questionid = @questionid
if @@ERROR <> 0
begin
rollback
raiserror ('-some error-', 16, 1)
return
end
end transaction
commit
end
answered Nov 12 '18 at 12:41
Pratik SomaiyaPratik Somaiya
1085
I don't understand what you mean?
– DutchFatBoys
Nov 12 '18 at 16:13
What I meant was you can have a function/stored procedure which takes the questionid/employeeid as input parameters. These input parameters can then be passed in the insert statement in WHERE clause, thereby avoiding NULL values for questionid/employeeid, also these 2 things will not be inserted again rather they will be used as conditions.
– Pratik Somaiya
Nov 13 '18 at 8:34
Could you maybe show me how you will do this? Thanks.
– DutchFatBoys
Nov 13 '18 at 9:55
add a comment |
Sorry I was busy travelling.
I tried creating 2nd procedure as follows.
Here I have created employeeid and questionid as 2 variables which we will passs while executing this stored procedure:
create procedure Proc2
(
@employeeid int
,@questionid int
,@score int
,@comment varchar(50)
,@date date
)
as
begin
begin transaction
insert into contentment (date, score, comment)
select @date = date, @score = score, @comment = comment
where employeeid = @employeeid and questionid = @questionid
if @@ERROR <> 0
begin
rollback
raiserror ('-some error-', 16, 1)
return
end
end transaction
commit
end
answered Nov 12 '18 at 12:41
Pratik SomaiyaPratik Somaiya
1085
Sorry I was busy travelling.
I tried creating 2nd procedure as follows.
Here I have created employeeid and questionid as 2 variables which we will passs while executing this stored procedure:
create procedure Proc2
(
@employeeid int
,@questionid int
,@score int
,@comment varchar(50)
,@date date
)
as
begin
begin transaction
insert into contentment (date, score, comment)
select @date = date, @score = score, @comment = comment
where employeeid = @employeeid and questionid = @questionid
if @@ERROR <> 0
begin
rollback
raiserror ('-some error-', 16, 1)
return
end
end transaction
commit
end
answered Nov 12 '18 at 12:41
Pratik SomaiyaPratik Somaiya
1085
edited Nov 16 '18 at 5:14
answered Nov 12 '18 at 12:41
Pratik SomaiyaPratik Somaiya
1085
answered Nov 12 '18 at 12:41
Pratik SomaiyaPratik Somaiya
1085
answered Nov 12 '18 at 12:41
Pratik SomaiyaPratik Somaiya
1085
1085
I don't understand what you mean?
– DutchFatBoys
Nov 12 '18 at 16:13
What I meant was you can have a function/stored procedure which takes the questionid/employeeid as input parameters. These input parameters can then be passed in the insert statement in WHERE clause, thereby avoiding NULL values for questionid/employeeid, also these 2 things will not be inserted again rather they will be used as conditions.
– Pratik Somaiya
Nov 13 '18 at 8:34
Could you maybe show me how you will do this? Thanks.
– DutchFatBoys
Nov 13 '18 at 9:55
add a comment |
I don't understand what you mean?
– DutchFatBoys
Nov 12 '18 at 16:13
What I meant was you can have a function/stored procedure which takes the questionid/employeeid as input parameters. These input parameters can then be passed in the insert statement in WHERE clause, thereby avoiding NULL values for questionid/employeeid, also these 2 things will not be inserted again rather they will be used as conditions.
– Pratik Somaiya
Nov 13 '18 at 8:34
Could you maybe show me how you will do this? Thanks.
– DutchFatBoys
Nov 13 '18 at 9:55
I don't understand what you mean?
– DutchFatBoys
Nov 12 '18 at 16:13
I don't understand what you mean?
– DutchFatBoys
Nov 12 '18 at 16:13
What I meant was you can have a function/stored procedure which takes the questionid/employeeid as input parameters. These input parameters can then be passed in the insert statement in WHERE clause, thereby avoiding NULL values for questionid/employeeid, also these 2 things will not be inserted again rather they will be used as conditions.
– Pratik Somaiya
Nov 13 '18 at 8:34
What I meant was you can have a function/stored procedure which takes the questionid/employeeid as input parameters. These input parameters can then be passed in the insert statement in WHERE clause, thereby avoiding NULL values for questionid/employeeid, also these 2 things will not be inserted again rather they will be used as conditions.
– Pratik Somaiya
Nov 13 '18 at 8:34
Could you maybe show me how you will do this? Thanks.
– DutchFatBoys
Nov 13 '18 at 9:55
Could you maybe show me how you will do this? Thanks.
– DutchFatBoys
Nov 13 '18 at 9:55
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53262152%2fhow-to-load-in-data-and-insert-data-together-with-a-stored-procedure%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Are these SPs being called from code? (If so, tag a language.) In my experience, theres.no point putting transactions around atomic statements (which have implicit transactions) - transactions can be used in the calling code to group different units of work. Raising errors is similar - the calling code can determine what happened, and react accordingly.
– Richardissimo
Nov 16 '18 at 6:05