sp_executesql problem

Question

sp_executesql problem

a2zwd

SSCrazy

Points: 2356
More actions
September 6, 2011 at 7:44 am

#244271

Hi
I have a stored procedure. With in SP, I am looping through a local temp table which has another SP name in one column.
I need to execute that SP in the loop.
My code:
SELECT @id = ISNULL(MAX(ID),1) FROM @BondInvoiceRules
WHILE (@Counter < @id)
BEGIN
SELECT @RuleId = RuleId, @RuleProcedure = RuleProcedure FROM @BondInvoiceRules WHERE ID = @Counter
SET @SqlQry = N'EXEC ' + @RuleProcedure + ' ' + CAST(@TransactionId AS VARCHAR(5)) + ' ,' + CAST(@TestRunId AS VARCHAR(5)) + ' ,' + CAST(@RuleId AS VARCHAR(5)) + ' ,' + CAST(@PartnerId AS VARCHAR(5))
print(@SqlQry)
--EXEC sp_executesql @SqlQry
SET @Counter += 1
END
When I execute the main SP, control coming to while loop but not executing the print statement(or exec).
If I run second time, everything is running smooth.
why is it so?
Please reply

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

tough to diagnose without the whole, testable code...

there's a table variable that's missing, and the declaration and assignment of the @Counter is missing...it does get assigned the value of 1 before the code you pasted, right?

only guessing, but i'm thinking that running the code block once initializes some data that was required in the first place, so it's available on the second pass, but not the first.

can you paste the entire code block?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

a2zwd SSCrazy Points: 2356 More actions · Answer 2

Hi

Thanks for the reply. This is the actual code

CREATE PROCEDURE usp_AutoApprovals

@TransactionIdINT,

@PartnerIdINT

AS

BEGIN

DECLARE @TestRunId INT

DECLARE @InvoiceRules TABLE(ID INT IDENTITY(1,1), RuleId INT, RuleName VARCHAR(100), RuleProcedure VARCHAR(200))

DECLARE @EventData NVARCHAR(MAX)

INSERT INTO @InvoiceRules(RuleId, RuleName, RuleProcedure)

SELECTar.RuleId,

ar.RuleName,

ar.RuleProcedure

FROM Rules ar

-- Log into Rules.ApprovalTestRun

SELECT @TestRunId = MAX(TestRunId)+1 FROM Rules.ApprovalTestRun

--Loop through all rules

DECLARE @id INT = 0, @Counter INT = 1, @SqlQry NVARCHAR(250) = ''

DECLARE @RuleId INT, @RuleProcedure VARCHAR(200)

SELECT @id = ISNULL(MAX(ID),1) FROM @InvoiceRules

WHILE (@Counter < @id)

BEGIN

SELECT @RuleId = RuleId, @RuleProcedure = RuleProcedure FROM @InvoiceRules WHERE ID = @Counter

SET @SqlQry = N'EXEC ' + @RuleProcedure + ' ' + CAST(@TransactionId AS VARCHAR(5)) + ' ,' + CAST(@TestRunId AS VARCHAR(5)) + ' ,' + CAST(@RuleId AS VARCHAR(5)) + ' ,' + CAST(@PartnerId AS VARCHAR(5))

print(@SqlQry)

--EXEC sp_executesql @SqlQry

SET @Counter += 1

END

a2zwd SSCrazy Points: 2356 More actions · Answer 3

Hi

Thanks for the reply. I solved it. Its due to NULL value in @TestRunId