Improve company productivity with a Business Account.Sign Up

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 602
  • Last Modified:

Dynamic data entry with stored procedure

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
5 Solutions
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.
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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...

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?"

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)
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


Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now