WITH RECOMPILE in stored procedure after setting parameters-CodePudding

I want to use the WITH RECOMPILE in a stored procedure after the declared parameter are filled with values. Where do I have to put it? I want to do something like this:

CREATE PROCEDURE sp_dosomething 
    @AdrID INT = '32'
AS
    SELECT @AdrID = SELECT MAX(ID) 
                    FROM Address
                    WITH RECOMPILE

    SELECT Streetname FROM Workadress WHERE ID = @AdrID
    SELECT Streetname FROM Homeadress WHERE ID = @AdrID
GO

So I want to set the parameter and then recompile the execution plan for the rest of the stored procedure every time it is executed.

What would be the right way to do it?

Please help me.

CodePudding user response：

This should work:

CREATE PROCEDURE sp_dosomething_sub @AdrID INT
WITH RECOMPILE
AS
    Select Streetname from Workadress where ID = @AdrID
    Select Streetname from Homeadress where ID = @AdrID
GO

CREATE PROCEDURE sp_dosomething @AdrID INT = '32'
AS
    select @AdrID = Select max(ID) FROM Address
    EXEC sp_dosomething_sub @ArdID
GO

Note that I am not saying that this is necessarily a good idea, I'm just saying that it should do what you want. It would take some pretty odd (or complex) circumstances for this to be a desirable approach.

CodePudding user response：

I always say that everything is possible as long as you have the resources (which includes time, money, knowledge and more). This case is not different. In order to use this format, all that you need to do is to design and develop your own language, since this format is not a valid Transact SQL format.

Your question is like asking how can I calculate 1 1 and get 3. The answer is to build your own math, since is not fit for the common math language.

If I understand your needs (and not request) correctly then you can gain what you need by executing a stored procedure from another stored procedure.

You can write procedure X which configure the parameters and this procedure X will execute procedure Y which is configured using WITH RECOMPILE