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
ID: 12249104
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 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 20 total points
ID: 12249284
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
ID: 12249300
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
ID: 12249454
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
ID: 12249570
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

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

726 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