Can stored procedures (names) be stored in a table, and still be executed properly

Question

0.00/5 (No votes)

See more:

I'm trying to create a table with some data, including the name of the stored procedure that I'm supposed to run... Is it possible to execute a procedure using a text containing its name (I'm assuming that there are always 2 parameters... so it doesn't matter...)

Posted 24-Oct-11 1:03am

ShacharK

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Muhammad Shahid Farooq · Answer 1 · 2011-10-24T01:17:00

--SP Creation
CREATE PROCEDURE Test
@param1 varchar(10),
@param2 varchar(10)
AS
BEGIN
Select Column1, column2 ... From Table1
END
GO

--Simple SP execution
exec Test '1', '2'

Declare @SPwithParameter varchar(1000)
Set @SPwithParameter = 'Test'

--SP execution from text
execute @SPwithParameter '1', '2'

Om Prakash Pant · Answer 2 · 2011-10-24T01:17:00

Solution 2

Yes

SQL

--Assuming that you have the following SP:
create procedure CallTest
as
begin
select sellerName, 'US' as 'Country' from sellers
end
go

--Table to store SP name:
Create table sp_data (iid int, sp_name varchar(50));

--insert sp name
insert into sp_data  values (1, 'CallTest');

--get name from db:
declare @sp_name varchar(50)
select @sp_name = sp_name from sp_data

--Execute the SP
exec (@sp_name);

Posted 24-Oct-11 1:17am

Om Prakash Pant

Comments

ShacharK 24-Oct-11 8:18am

Great, and if I assume that data is variant, can it be converted automatically (or throwing an exception, I don't care...) to the input type of the destinated procedure?

@v1 and @v2 are variants that are supposed to be passed into the procedure...

I mean, exec (@sp_name) @v1 @v2

and the specified procedure is actually expecting integers...