1

This is all within a TSQL stored procedure. I have a variable that contains the name of a stored procedure I want to execute, up until now this method has been used only with stored procedures that do no take arguments. I'm having troulbe fitting a proc that does take an argument into the same mold.

In my main driver procedure I have a line EXECUTE @process_name and @process_name is normally something like 'database..procedure' with no arugments. How do I fit the name of a stored proc and its arguments into one string so that when I call the line EXECUTE @process_name it will execute the procedure with arguments?

I can't change this execute line at all, I really need to fit the proc name and arguments into the single string @process_name. I realize it may not be the best practice.

Thanks for any help!

intA
  • 2,513
  • 12
  • 41
  • 66

1 Answers1

0

You put the same thing in @process_name as though you were typing it out like so:

--Create your Stored Procedure
CREATE PROCEDURE dbo.YourStoredProc
    @FirstVariable VARCHAR(30),
    @SecondVariable VARCHAR(30)
AS
BEGIN
    SELECT @FirstVariable,@SecondVariable
END
GO


DECLARE @process_name VARCHAR(MAX);
--List the name of your stored procedure you want to execute
    --Followed by variables and their values
SET @process_name = 'dbo.YourStoredProc @firstvariable = ''Hello'', @SecondVariable = ''World!'''

EXEC (@process_name)
Stephan
  • 5,891
  • 1
  • 16
  • 24
  • This doesn't seem to work. It tells me `Could not find stored procedure 'YourStoredProc @firstvariable = ''Hello'', @secondVariable = ''World!'''` It is looking for a procedure with that exact name, variables included. – intA Mar 09 '15 at 15:46
  • Are you sure the procedure was created? And that you're in the same database? Also did you try running the create statement by itself first and then running the rest of the code? – Stephan Mar 09 '15 at 15:55
  • Yes. I created a procedure called "testProc_AFI" that takes one variable called @dsi that is an integer. `EXEC testProc_AFI '3'` works fine. But when I try `SELECT @process = 'testProc_AFI @dsi = ''1''' EXEC @process ` It says `Could not find stored procedure 'testProc_AFI '1''.` – intA Mar 09 '15 at 15:59
  • Okay I managed to get the same error so I added the schema name "dbo" and it worked for me. I updated my answer to show you what I mean. – Stephan Mar 09 '15 at 16:06
  • Did you keep the the parentheses around the @process_name in the last line? Also you can test your stored procedure if you find it in Object Explorer under YourDatabase>Programmability>StoredProcedures>YourStoredProc – Stephan Mar 09 '15 at 17:11
  • No, though I just added them and it worked. The only issue though is that in my driver proc when I actually implement this the line is like `EXECUTE @return_status = @process` where @return_status is just an integer that will contain the return code of the procedure. In this context your solution doesn't seem to work. – intA Mar 09 '15 at 17:16
  • Can you do this? **SET @return_status = @process; EXEC (@return_status)** – Stephan Mar 09 '15 at 17:51
  • No, the @return_status variable is an INT. I can't put a varchar inside it – intA Mar 09 '15 at 18:17
  • Okay I'm not really sure what you're going for. Could you add more information about your problem like a sample of how you've previously executed a stored proc also explain what each variable is like what @ process and @ process_name are. – Stephan Mar 09 '15 at 21:45