How to pass table name dynamically for stored procedure
-
Hi All, I have two tables like Casual_Employee_Details, Permanent_Employee_Table and the columns in both tables are same. I have a stored procedure as follows CREATE PROCEDURE getEmpId ( @Eid int, @EName varchar(50) ) AS insert into Casual_Employee_Details(n_EmpId,c_EmpName)values(@EmpId,@EmpName) GO Now i want to pass table name also dynamically how can it be done. help me out with code if possible.. So that if if i execute it should as follows Execute dbo.getEmpId 'tableName',Eid,'Ename' Thanks, Ashok
ashok
-
Hi All, I have two tables like Casual_Employee_Details, Permanent_Employee_Table and the columns in both tables are same. I have a stored procedure as follows CREATE PROCEDURE getEmpId ( @Eid int, @EName varchar(50) ) AS insert into Casual_Employee_Details(n_EmpId,c_EmpName)values(@EmpId,@EmpName) GO Now i want to pass table name also dynamically how can it be done. help me out with code if possible.. So that if if i execute it should as follows Execute dbo.getEmpId 'tableName',Eid,'Ename' Thanks, Ashok
ashok
you have to concatenate the whole sql string to a variable like
set @sql = 'insert into ' + QUOTENAME(@tablename)
+ ' (n_EmpId,c_EmpName)values('
+ convert(varchar(50), @EmpId)
+ ',' + QUOTENAME(@EmpName, '''') + ')'
exec (@sql)However it's not typically a good idea to use dynamic sql this way. It may lead to several problems. Modification, added QUOTENAME to prevent injections.
The need to optimize rises from a bad design.My articles[^]
-
you have to concatenate the whole sql string to a variable like
set @sql = 'insert into ' + QUOTENAME(@tablename)
+ ' (n_EmpId,c_EmpName)values('
+ convert(varchar(50), @EmpId)
+ ',' + QUOTENAME(@EmpName, '''') + ')'
exec (@sql)However it's not typically a good idea to use dynamic sql this way. It may lead to several problems. Modification, added QUOTENAME to prevent injections.
The need to optimize rises from a bad design.My articles[^]
Mika Wendelius wrote:
However it's not typically a good idea to use dynamic sql this way
It is posslble to do some verification before you start the concatenation. For instance. you can look up INFORMATION_SCHEMA.TABLES to ensure that the a table with the given name exists. If it doesn't you can exit the sproc. You can also pass parameters to dynamic SQL by using sp_executesql[^] instead of
EXEC. That way you are not concatenating potentially dangerous data into the SQL String, you're passing it as a parameter as normal.* Developer Day Scotland 2 - Free community conference * The Blog of Colin Angus Mackay
Vogon Building and Loan advise that your planet is at risk if you do not keep up repayments on any mortgage secured upon it. Please remember that the force of gravity can go up as well as down.
-
Mika Wendelius wrote:
However it's not typically a good idea to use dynamic sql this way
It is posslble to do some verification before you start the concatenation. For instance. you can look up INFORMATION_SCHEMA.TABLES to ensure that the a table with the given name exists. If it doesn't you can exit the sproc. You can also pass parameters to dynamic SQL by using sp_executesql[^] instead of
EXEC. That way you are not concatenating potentially dangerous data into the SQL String, you're passing it as a parameter as normal.* Developer Day Scotland 2 - Free community conference * The Blog of Colin Angus Mackay
Vogon Building and Loan advise that your planet is at risk if you do not keep up repayments on any mortgage secured upon it. Please remember that the force of gravity can go up as well as down.
You're right, there are numerous ways to perform checks and prevent injections etc. but since I wasn't sure why the OP wanted to use dynamic sql for the insert (it didn't quite make sense since for example columns were hardcoded) I thought it would be wise if he thinks about it once more. If dynamic handling for the table name is still required, at least it's thoroughly considered. I just noticed that I forgot to use QUOTENAME. Think I should add it to the answer. Another way is to use sp_executesql as you mentioned.
The need to optimize rises from a bad design.My articles[^]
-
You're right, there are numerous ways to perform checks and prevent injections etc. but since I wasn't sure why the OP wanted to use dynamic sql for the insert (it didn't quite make sense since for example columns were hardcoded) I thought it would be wise if he thinks about it once more. If dynamic handling for the table name is still required, at least it's thoroughly considered. I just noticed that I forgot to use QUOTENAME. Think I should add it to the answer. Another way is to use sp_executesql as you mentioned.
The need to optimize rises from a bad design.My articles[^]
-
Hi all, Can anyone explain other possible mechanisms to handle this scenario in detail. Any references could be helpful.. thanks, Ashok
ashok
ashok_rgm wrote:
Can anyone explain other possible mechanisms to handle this scenario in detail
As Colin and I wrote you can use either
sp_executesqlorexecto execute a dynamic sql statement. Both will do as long as you prevent yourself from sql injections. In your original post you described that you want to pass the table name as a parameter and use it in your sql statement. However your column names were hardcoded in the statement so it makes me wonder, why do you want to change the table name. First impression is that you have several tables with the same structure. If that's correct then again, why?The need to optimize rises from a bad design.My articles[^]
-
ashok_rgm wrote:
Can anyone explain other possible mechanisms to handle this scenario in detail
As Colin and I wrote you can use either
sp_executesqlorexecto execute a dynamic sql statement. Both will do as long as you prevent yourself from sql injections. In your original post you described that you want to pass the table name as a parameter and use it in your sql statement. However your column names were hardcoded in the statement so it makes me wonder, why do you want to change the table name. First impression is that you have several tables with the same structure. If that's correct then again, why?The need to optimize rises from a bad design.My articles[^]
My issue is something like this i have a table Master_Info with columns(RecId(PK),Emp_Name,DOB) where RecId is primary key and increments by 1 for each entry. RecId Emp_Name DOB 1 abc 01/01/2009 2 xyz 02/02/2009 Now for each Employee I have separate table to track hi daily details Employee_Details_(RecId) with columns (Working_Date,No_of_Hours) So for user "abc" the table name will be Employee_Details_1,for "xyz" it will be Employee_Details_2 Now I want to insert the records for particular user in Employee_Details_(RecId) table using the stored procedure. What are the suggested ways to do this task? Thanks, Ashok
ashok
-
My issue is something like this i have a table Master_Info with columns(RecId(PK),Emp_Name,DOB) where RecId is primary key and increments by 1 for each entry. RecId Emp_Name DOB 1 abc 01/01/2009 2 xyz 02/02/2009 Now for each Employee I have separate table to track hi daily details Employee_Details_(RecId) with columns (Working_Date,No_of_Hours) So for user "abc" the table name will be Employee_Details_1,for "xyz" it will be Employee_Details_2 Now I want to insert the records for particular user in Employee_Details_(RecId) table using the stored procedure. What are the suggested ways to do this task? Thanks, Ashok
ashok
ashok_rgm wrote:
Now for each Employee I have separate table to track hi daily details Employee_Details
Is there really a very good reason to do it this way? Normally you would have a table structures like:
Master_Info
- RecId (primary key)
- Emp_Name
- DOB
Employee_Details
- ID (primary key)
- RecId (foreign key to Master_Info)
- Working_Date
- No_of_Hours
So in Employee detail you store details for all employees and the RecId column tells which employee is referenced.
The need to optimize rises from a bad design.My articles[^]