Q:
Creating stored procedures to execute tables at runtime on a SQL Server
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?
"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?
ASKED:
Sep 17 2008 7:21 PM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
A:
RATE THIS ANSWER
0
Click to Vote:
0
Click to Vote:
- 0
0
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.
