Solved

Dynamic data entry with stored procedure

Posted on 2004-10-07
5
574 Views
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)
cursor...
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
deallocate


Any ideas
Marts
0
Comment
Question by:mj_cole
5 Comments
 
LVL 18

Assisted Solution

by:ShogunWade
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.
0
 
LVL 142

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

CHeers
0
 
LVL 6

Assisted Solution

by:boblah
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?"


Cheers!
0
 
LVL 17

Accepted Solution

by:
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)
cursor...
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)
0
 
LVL 26

Assisted Solution

by:Hilaire
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)
)
begin
   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
end


HTH

Hilaire
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

813 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

17 Experts available now in Live!

Get 1:1 Help Now