I tried to make a dynamic SQL query in procedure but it is not working on some of procedure

강 진수 1 Reputation point
2023-01-03T06:21:18.703+00:00

I made a SQL like below but if statement is not working normally when sp_executesql is used in if statement
In addition, it is occurred on specific procedure only.
It is working on the other procedure.
So I deleted the procedure and remade it to delete caches but no change.
I deleted dynamic SQL and it is working now.
Could anyone check this is a bug or not?
ex1) not working.
SET @FLAG = true
IF @FLAG -- this returns false even thought I set the flag with true above
BEGIN
SET @alenzi = ''
sp_executesql @alenzi
END
ELSE
BEGIN
EXEC PROCEDURE
END
ex2) working.
SET @FLAG = true
IF @FLAG -- this returns false even thought I set the flag with true above
BEGIN
EXEC PROCEDURE1
END
ELSE
BEGIN
EXEC PROCEDURE2
END

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
14,437 questions
{count} votes

1 answer

Sort by: Most helpful
  1. LiHongMSFT-4306 30,591 Reputation points
    2023-01-03T08:26:37.547+00:00

    Hi @강 진수
    It might be that you lost 'EXECUTE' before the sp_executesql in your code.
    275602-image.png

    Best regards,
    LiHong


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.