Creating stored procedures to execute tables at runtime on a SQL Server

Tags:
SQL Server stored procedures
Stored Procedures
I’d like to execute different tables at runtime on my SQL Server by creating stored procedures. This is the syntax I am attempting to use:
create procedure sp1(colname varchar(400), tab_name IN varchar2) 
AS BEGIN select * from '@TABLENAME' where CompanyName='@COLUMNNAME' end
Is this syntax accurate, and if not, how could I create this stored procedure?

Answer Wiki

Thanks. We'll let you know when a new response is added.

AFAIK, passing table or column names as a parameter is not allowed in the context of a stored procedure, so I think dynamic SQL must be used, so the stored procedure will just be used to generate the dynamic SQL needed.

The stored procedure would look like this:

create procedure sp1
	@colname varchar(400), 
	@tab_name varchar(50) 
AS 
BEGIN
	DECLARE @sqlCommand varchar(1000);
	SET @sqlCommand = 'SELECT * FROM ' + @tab_name + ' WHERE CompanyName = ''' + @colname + ''''
	EXEC (@sqlCommand)
END

Note all the single quotes at the end. They are all needed.

You have to be very careful when using dynamic SQL as there are some big security issues with dynamic SQL. The inherited permissions which are usually in place when using stored procedures do not apply when using dynamic SQL. Because of this the account which runs the stored procedure will need SELECT rights to what ever tables the user passes in. This creates a security hole as if a hacker uses a SQL Injection attack against the front end they will have access to pull all the data from these tables.

It is much more secure to setup a separate procedure for each table that you need to select data from.

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following