Dynamic data entry with stored procedure

Posted on 2004-10-07
Medium Priority
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 18

Assisted Solution

ShogunWade earned 80 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 80 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 80 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 1600 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 160 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

Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

765 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