Dynamic data entry with stored procedure

Posted on 2004-10-07
Last Modified: 2012-06-21
I want to impliment a dynamic stored procedure that inserts data to a specific table, without knowing any specifics of the table

Ie if I have tableA and tableB

TableA (item1int, item2 varchar(10), item3 float)
TableB (abc int, def float char, ghi char(10), jkl float)

I want to have a procedure that is called

exec sp_insert('TableA'   ,  'item1, item2'  , '123, abc')   or
exec sp_insert('TableB'   ,  'abc, ghi, def'  ,  '32,gfd,x')

I have been able to split the list of items and data into a table variable

@inserts table( itm varchar(20), dta varchar(20) )

but my main problem is that I want to cursor through this, and build a dynamic statement that updates the table with each data point.

solution 1 fails as the data string is not surrounded by single quotes

declare @cmd nvarchar(4000)
set @cmd=' update [' + @tbl + '] set [' + @itm + '] =  ' + @dta + ' where @headerid=1 '
exec sp_executesql @cmd
deallocate ...

solution 2 fails as I cannot reference a table or colum name with a variable

cursor ---
update @tbl set @itm = @dta where headerid=1

Any ideas
Question by:mj_cole
LVL 18

Assisted Solution

ShogunWade earned 20 total points
Comment Utility
I assume you have a good reason for wanting such a stored procedure and you understand all the pitfalls of what you are about to emark on, such as security and context issues surrounding the use of dynamic sql. etc.
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 20 total points
Comment Utility
I agree with ShogunWade that this is not really a good idea, despite any arguments you would have.

For the exercice's sake, you might try to implement it, by checking out which datatype the column(s) have, by querying syscolumns , so depending on the datatype you adjust the @sql as needed...


Assisted Solution

boblah earned 20 total points
Comment Utility
Hi mj_cole,

Simple solution is to specify the data types of the data values when constructing the parameters for the stored proc calls, so the call then looks like:

exec sp_insert('TableA'   ,  'item1, item2'  , '123, ''abc''')   or
exec sp_insert('TableB'   ,  'abc, ghi, def'  ,  '32,''gfd'',''x''')

Otherwise, a very complicated way of doing this would be to look up the data types of the columns in the sys tables (not supported by ms) so you know whether to put quotes round the values or not.

But I would echo the question "are you sure you want to do it this way?"

LVL 17

Accepted Solution

BillAn1 earned 400 total points
Comment Utility
for solution1 , all you need to do is to put in the single quotes (escaped by more single quotes!!)
as there is no issue with having single quotes around numeric values, so you can do it for all datattypes :

declare @cmd nvarchar(4000)
set @cmd=' update [' + @tbl + '] set [' + @itm + '] =  ''' + @dta + ''' where @headerid=1 '
exec sp_executesql @cmd
deallocate ...

You may also need to be wary of single quotes in any strings passed in - if there is the likelyhood of this happening , you will need to escape these too, by replacing any single single quote with 2 single quotes i.e.
set @cmd=' update [' + @tbl + '] set [' + @itm + '] =  ''' + replace(@dta, '''','''''') + ''' where @headerid=1 '
(they're all single quotes there)
LVL 26

Assisted Solution

Hilaire earned 40 total points
Comment Utility
Here's a solution whithout cursor
that generates insert statement on the fly (no use to update in a loop)
the hard-coded 't_dummy' can be changed to a relevant table name

declare @inserts table( itm varchar(20), dta varchar(20))

insert @inserts values('a', '12')
insert @inserts values('b', 'foo')
insert @inserts values('c', '2004/10/10')
-- first check column existence to make sure the insert statement will parse
declare @table_name varchar(200)
declare @sql1 varchar(4000), @sql2 varchar(4000)
set @table_name = 't_dummy'
if exists(
      select 1
      from @inserts a
      left outer join information_schema.columns b on a.itm = b.column_name and b.table_name = @table_name
      having count(*) = sum(case when b.column_name is null then 0 else 1 end)
   select @sql1 = coalesce(@sql1 + ', ', 'insert into ' + @table_name + '(') + column_name,
      @sql2 = coalesce(@sql2 + ', ', ') values(') + 'cast(''' + dta + ''' as ' + data_type + isnull('(' + cast(character_maximum_length as varchar(10)) + '))', ')')
      from @inserts a inner join information_schema.columns b on a.itm = b.column_name and b.table_name = @table_name
   --print @sql1 + @sql2 + ')'  --uncomment this line to see generated sql
   exec(@sql1 + @sql2 + ')')   -- comment out this line to debug



Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
Introduced in Microsoft SQL Server 2005, the Copy Database Wizard ( is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now