Styjun

What clothes would flying-people wear?

What do I do with a party that is much stronger than their level?

What would the United Kingdom's "optimal" Brexit deal look like?

Next command output on the same line? Bash script

Assuring luggage isn't lost with short layover

What is more environmentally friendly? An A320 or a car?

Would people understand me speaking German all over Europe?

How to improve king safety

Why is it considered acid rain with pH <5.6?

What force enables us to walk? Friction or normal reaction?

Surviving a planet collision?

What is the reason for cards stating "Until end of turn, you don't lose this mana as steps and phases end"?

How can chaotic entities be prevented from spreading beyond their domain?

Why was the LRV's speed gauge displaying metric units?

Exploiting the delay when a festival ticket is scanned

Golden Guardian removed before death related trigger

How can I die in Goat Simulator?

To find islands of 1 and 0 in matrix

Why did House of Representatives need to condemn Trumps Tweets?

Did Vladimir Lenin have a cat?

Employer stores plain text personal data in a 'data warehouse'

Should I intervene when a colleague in a different department makes students run laps as part of their grade?

How do I find the FamilyGUID of an exsting database

How to store my pliers and wire cutters on my desk?

Stored procedure transaction with table as input executes so many times until it reaches transaction limit

Drop all the tables, stored procedures, triggers, constraints and all the dependencies in one sql statementInsert results of a stored procedure into a temporary tableMaintain transaction on Linked server inside a stored procedure which uses OpenRowSet command to read data from Excel fileSuppress transaction in stored procedureHow many stored procedure calls per transaction?GRANT EXECUTE to all stored proceduresMySQL : transaction within a stored procedureExecute a Stored Procedure with a table as inputTransaction table stored procedureAccept input parameter using function in T-SQL

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

0

I'm working on transaction exercise. I have several tables as shown on the screenshot here:

Also I have custom datatype declared:

create type TagList as table
(
 TagName varchar(255)
)

Now, I just tried to pass this table as variable to my stored procedure to test if it works, however when I execute following transaction it gets executed so many times until it reaches the transaction limit

ALTER PROCEDURE NewBlogPost
 @headline VARCHAR(255),
 @content VARCHAR(MAX),
 @userId INT,
 @categoryId INT,
 @tagId INT,
 @tags AS dbo.TagList READONLY
AS
BEGIN TRY
 BEGIN TRANSACTION
 DECLARE @postId INT

 SELECT @postId = (SELECT TOP 1 PostId
 FROM Posts
 ORDER BY PostId DESC) + 1;

 INSERT INTO Posts (PostId, Headline, Content, UserId, CategoryId, StateId, PostedDate, LastEdit)
 VALUES (@postId, @headline, @content, @userId, @categoryId, 1, GETDATE(), GETDATE())

 INSERT INTO TagPost (PostId, TagId)
 VALUES (@postId, @tagId)

 COMMIT TRANSACTION
 END TRY
 BEGIN CATCH
 SELECT
 ERROR_NUMBER() AS ErrorNumber,
 ERROR_SEVERITY() AS ErrorSeverity,
 ERROR_STATE() AS ErrorState,
 ERROR_PROCEDURE() AS ErrorProcedure,
 ERROR_LINE() AS ErrorLine,
 ERROR_MESSAGE() AS ErrorMessage

 ROLLBACK TRANSACTION
 END CATCH

DECLARE @TagTable TagList

INSERT INTO @TagTable(TagName)
VALUES ('Computers'), ('MobilePhones'), ('Pesos')

EXEC NewBlogPost @headline = 'Framework called VUE breaks new record', @content = 'New framework had broken world record in daily download', @userId = 1, @categoryId = 1, @tagId = 1, @tags = @TagTable

Any idea why am I executing this transaction so many times?

sql transactions

share|improve this question

edited Mar 26 at 20:34

marc_s

599k135135 gold badges11491149 silver badges12841284 bronze badges

asked Mar 26 at 20:29

Lukáš VáclavekLukáš Václavek

8311 silver badge88 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Which dbms are you using? (That code is product specific.)
  
  – jarlh
  Mar 26 at 20:30
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  DataGrip, but I got same results executing this in the MSSQL Studio
  
  – Lukáš Václavek
  Mar 26 at 20:34
  

  
  
  
  

add a comment | 

0

I'm working on transaction exercise. I have several tables as shown on the screenshot here:

Also I have custom datatype declared:

create type TagList as table
(
 TagName varchar(255)
)

Now, I just tried to pass this table as variable to my stored procedure to test if it works, however when I execute following transaction it gets executed so many times until it reaches the transaction limit

ALTER PROCEDURE NewBlogPost
 @headline VARCHAR(255),
 @content VARCHAR(MAX),
 @userId INT,
 @categoryId INT,
 @tagId INT,
 @tags AS dbo.TagList READONLY
AS
BEGIN TRY
 BEGIN TRANSACTION
 DECLARE @postId INT

 SELECT @postId = (SELECT TOP 1 PostId
 FROM Posts
 ORDER BY PostId DESC) + 1;

 INSERT INTO Posts (PostId, Headline, Content, UserId, CategoryId, StateId, PostedDate, LastEdit)
 VALUES (@postId, @headline, @content, @userId, @categoryId, 1, GETDATE(), GETDATE())

 INSERT INTO TagPost (PostId, TagId)
 VALUES (@postId, @tagId)

 COMMIT TRANSACTION
 END TRY
 BEGIN CATCH
 SELECT
 ERROR_NUMBER() AS ErrorNumber,
 ERROR_SEVERITY() AS ErrorSeverity,
 ERROR_STATE() AS ErrorState,
 ERROR_PROCEDURE() AS ErrorProcedure,
 ERROR_LINE() AS ErrorLine,
 ERROR_MESSAGE() AS ErrorMessage

 ROLLBACK TRANSACTION
 END CATCH

DECLARE @TagTable TagList

INSERT INTO @TagTable(TagName)
VALUES ('Computers'), ('MobilePhones'), ('Pesos')

EXEC NewBlogPost @headline = 'Framework called VUE breaks new record', @content = 'New framework had broken world record in daily download', @userId = 1, @categoryId = 1, @tagId = 1, @tags = @TagTable

Any idea why am I executing this transaction so many times?

sql transactions

share|improve this question

edited Mar 26 at 20:34

marc_s

599k135135 gold badges11491149 silver badges12841284 bronze badges

asked Mar 26 at 20:29

Lukáš VáclavekLukáš Václavek

8311 silver badge88 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Which dbms are you using? (That code is product specific.)
  
  – jarlh
  Mar 26 at 20:30
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  DataGrip, but I got same results executing this in the MSSQL Studio
  
  – Lukáš Václavek
  Mar 26 at 20:34
  

  
  
  
  

add a comment | 

I'm working on transaction exercise. I have several tables as shown on the screenshot here:

Also I have custom datatype declared:

create type TagList as table
(
 TagName varchar(255)
)

Now, I just tried to pass this table as variable to my stored procedure to test if it works, however when I execute following transaction it gets executed so many times until it reaches the transaction limit

ALTER PROCEDURE NewBlogPost
 @headline VARCHAR(255),
 @content VARCHAR(MAX),
 @userId INT,
 @categoryId INT,
 @tagId INT,
 @tags AS dbo.TagList READONLY
AS
BEGIN TRY
 BEGIN TRANSACTION
 DECLARE @postId INT

 SELECT @postId = (SELECT TOP 1 PostId
 FROM Posts
 ORDER BY PostId DESC) + 1;

 INSERT INTO Posts (PostId, Headline, Content, UserId, CategoryId, StateId, PostedDate, LastEdit)
 VALUES (@postId, @headline, @content, @userId, @categoryId, 1, GETDATE(), GETDATE())

 INSERT INTO TagPost (PostId, TagId)
 VALUES (@postId, @tagId)

 COMMIT TRANSACTION
 END TRY
 BEGIN CATCH
 SELECT
 ERROR_NUMBER() AS ErrorNumber,
 ERROR_SEVERITY() AS ErrorSeverity,
 ERROR_STATE() AS ErrorState,
 ERROR_PROCEDURE() AS ErrorProcedure,
 ERROR_LINE() AS ErrorLine,
 ERROR_MESSAGE() AS ErrorMessage

 ROLLBACK TRANSACTION
 END CATCH

DECLARE @TagTable TagList

INSERT INTO @TagTable(TagName)
VALUES ('Computers'), ('MobilePhones'), ('Pesos')

EXEC NewBlogPost @headline = 'Framework called VUE breaks new record', @content = 'New framework had broken world record in daily download', @userId = 1, @categoryId = 1, @tagId = 1, @tags = @TagTable

Any idea why am I executing this transaction so many times?

sql transactions

share|improve this question

edited Mar 26 at 20:34

marc_s

599k135135 gold badges11491149 silver badges12841284 bronze badges

asked Mar 26 at 20:29

Lukáš VáclavekLukáš Václavek

8311 silver badge88 bronze badges

create type TagList as table
(
 TagName varchar(255)
)

ALTER PROCEDURE NewBlogPost
 @headline VARCHAR(255),
 @content VARCHAR(MAX),
 @userId INT,
 @categoryId INT,
 @tagId INT,
 @tags AS dbo.TagList READONLY
AS
BEGIN TRY
 BEGIN TRANSACTION
 DECLARE @postId INT

 SELECT @postId = (SELECT TOP 1 PostId
 FROM Posts
 ORDER BY PostId DESC) + 1;

 INSERT INTO Posts (PostId, Headline, Content, UserId, CategoryId, StateId, PostedDate, LastEdit)
 VALUES (@postId, @headline, @content, @userId, @categoryId, 1, GETDATE(), GETDATE())

 INSERT INTO TagPost (PostId, TagId)
 VALUES (@postId, @tagId)

 COMMIT TRANSACTION
 END TRY
 BEGIN CATCH
 SELECT
 ERROR_NUMBER() AS ErrorNumber,
 ERROR_SEVERITY() AS ErrorSeverity,
 ERROR_STATE() AS ErrorState,
 ERROR_PROCEDURE() AS ErrorProcedure,
 ERROR_LINE() AS ErrorLine,
 ERROR_MESSAGE() AS ErrorMessage

 ROLLBACK TRANSACTION
 END CATCH

DECLARE @TagTable TagList

INSERT INTO @TagTable(TagName)
VALUES ('Computers'), ('MobilePhones'), ('Pesos')

EXEC NewBlogPost @headline = 'Framework called VUE breaks new record', @content = 'New framework had broken world record in daily download', @userId = 1, @categoryId = 1, @tagId = 1, @tags = @TagTable

share|improve this question

edited Mar 26 at 20:34

marc_s

599k135135 gold badges11491149 silver badges12841284 bronze badges

asked Mar 26 at 20:29

Lukáš VáclavekLukáš Václavek

8311 silver badge88 bronze badges

share|improve this question

edited Mar 26 at 20:34

marc_s

599k135135 gold badges11491149 silver badges12841284 bronze badges

asked Mar 26 at 20:29

Lukáš VáclavekLukáš Václavek

8311 silver badge88 bronze badges

edited Mar 26 at 20:34

marc_s

599k135135 gold badges11491149 silver badges12841284 bronze badges

edited Mar 26 at 20:34

marc_s

599k135135 gold badges11491149 silver badges12841284 bronze badges

asked Mar 26 at 20:29

Lukáš VáclavekLukáš Václavek

8311 silver badge88 bronze badges

asked Mar 26 at 20:29

Lukáš VáclavekLukáš Václavek

8311 silver badge88 bronze badges

1 Answer
1

active

oldest

votes

0

Alright, I have figured this out, it has to do nothing with Transaction, I have accidentally put exec statement into the transaction, therefore I have created an infinite loop.

share|improve this answer

answered Mar 26 at 20:39

Lukáš VáclavekLukáš Václavek

8311 silver badge88 bronze badges

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
);



);

draft saved

draft discarded

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

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55365729%2fstored-procedure-transaction-with-table-as-input-executes-so-many-times-until-it%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

0

Alright, I have figured this out, it has to do nothing with Transaction, I have accidentally put exec statement into the transaction, therefore I have created an infinite loop.

share|improve this answer

answered Mar 26 at 20:39

Lukáš VáclavekLukáš Václavek

8311 silver badge88 bronze badges

add a comment | 

0

Alright, I have figured this out, it has to do nothing with Transaction, I have accidentally put exec statement into the transaction, therefore I have created an infinite loop.

share|improve this answer

answered Mar 26 at 20:39

Lukáš VáclavekLukáš Václavek

8311 silver badge88 bronze badges

add a comment | 

Alright, I have figured this out, it has to do nothing with Transaction, I have accidentally put exec statement into the transaction, therefore I have created an infinite loop.

share|improve this answer

answered Mar 26 at 20:39

Lukáš VáclavekLukáš Václavek

8311 silver badge88 bronze badges

share|improve this answer

answered Mar 26 at 20:39

Lukáš VáclavekLukáš Václavek

8311 silver badge88 bronze badges

answered Mar 26 at 20:39

Lukáš VáclavekLukáš Václavek

8311 silver badge88 bronze badges

answered Mar 26 at 20:39

Lukáš VáclavekLukáš Václavek

8311 silver badge88 bronze badges