CODEWITHSUNDEEP
sqlstored-procedures
Rizwan
Rizwan

Reputation: 109

How I can use a single stored procedure for data insertion in multiple tables in SQL Server 2008?

Suppose I have many tables in my database. Every time I will insert data in any table. So, can I use a single stored procedure for all my data insertion in every table, instead of writing one stored procedure for each table?

In this scenario, each time I will pass table name and parameters dynamically to the stored procedure. If yes, can anyone give some basic idea how to perform this? If any extra information is required, please ask.

Thanks and regards, Rizwan Gazi.

Upvotes: 0

Views: 1216

Answers (2)

Vinnie
Vinnie

Reputation: 3929

You could work with dynamic SQL and build the insert statement on the fly. THIS IS NOT RECOMMENDED but it should solve the problem you're asking about.

(I haven't run this code, but you can see what is being accomplished here with building the insert string and then executing it)

In this procedure, you pass in the table name, columns and values you care about and fire it off in a row based operation. With some minor tweaks you would be able to make it set based as well.

create procedure dbo.TableInsert
@tablename varchar(100)
, @columnlist varchar(max)
, @valueslist varchar(max)
as
begin
declare @sql varchar(max)
set @sql = 
'insert into ' + @tablename
+ '(' + @columnlist + ')'
+ ' VALUES (' + @valueslist + ')'

print(@sql)

sp_executesql (@sql)

end
go

Execution would look something like this:

exec dbo.TableInsert
@tablename = 'TestTable'
, @columnlist = 'col1, col2, col3'
, @valuelist = '1,2,3'

Text insert would be a little trickier in this version since you have to wrap it around in single quotes.

exec dbo.TableInsert
@tablename = 'TestTable'
, @columnlist = 'col1, col2, col3'
, @valuelist = '''1'',''2'',''3'''

Upvotes: 1

anon
anon

Reputation:

You could do something using dynamic SQL to build a query and then run it using: EXEC SP_EXECUTESQL(@SQL) (Assuming MS SQL Server)

Not sure I'd recommend it though and will probably be a total nightmare to test and maintain. Having different sprocs would be easier to test and maintain going forward and would perform better as the different sprocs would have separate query plans.

If you are working in code you could use a ORM to deal with basic CRUD stuff.

Upvotes: 0

Related Questions