Solved

a single SQL proc for crud with one xml param?

Posted on 2010-08-12
12
473 Views
Last Modified: 2012-05-10
i was looking at making a bunch of procs to do simple CRUD operations in the DB and wanted to look into using XML as a param instead of separate variables so that i can have the UI just crank out these for various tables and maybe pass the table name to the proc along with the values

can anyone post some sample code on how to do this for lets say an employee table with empID, empName, age fields.
0
Comment
Question by:realcoding
[X]
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
  • 5
  • 3
  • 2
  • +1
12 Comments
 
LVL 60

Accepted Solution

by:
chapmandew earned 400 total points
ID: 33423527
Great idea, actually....you'll need to parse the xml, but other than that, you should be all good


create proc myproc
(      
      @x xml
)
as
begin
      insert into tablename(fld1, fld2)
      select c.value('@fld1', 'int'),
      c.value('@fld2', 'varchar(50)')
      from @x.nodes('/params') t(c)
end
go

declare @x xml
set @x = '<params fld1 = "3" fld2 = "timchapman"></params>'

exec myproc @x
0
 

Author Comment

by:realcoding
ID: 33423878
im trying to do all crud in one proc like this...

the sample works well but failed when i made the update to pull 1 more param for operation
create proc myCRUDproc
( @x xml)
as
begin

declare @operation varchar(50)
set @operation = select c.value('@operation','varchar(50)') from @x.nodes('/params') t(c)

IF @operation = 'ins'
BEGIN
	print 'inserting data'
END      

IF @operation = 'del'
BEGIN
	print 'deleting data'
END    

IF @operation = 'upd'
BEGIN
	print 'updating data'
END    	

declare @x xml
set @x = '<params operation="ins" SCID="3" Name="mike" Description="xml testing"></params>'
exec myproc @x

Open in new window

0
 

Author Comment

by:realcoding
ID: 33424321
i got it working just fails on the check to see if the xml is empty or doesn't exist so i do a select. how do i check for this... see code for where i do a select please.
create proc crudMilestone
( @x xml = '')
as

IF @x = ''
BEGIN
	select * from StatusCodeGroup2
END

declare @operation varchar(50)
set @operation = (select c.value('@operation','varchar(50)') from @x.nodes('/params') t(c))

IF @operation = 'ins'
BEGIN
	print 'inserting data'
END      

IF @operation = 'del'
BEGIN
	print 'deleting data'
END    

IF @operation = 'upd'
BEGIN
	print 'updating data'
END    	


declare @x xml
set @x = '<params operation="ins" SCID="3" Name="mike" Description="xml testing"></params>'
exec crudMilestone @x

Open in new window

0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 60

Expert Comment

by:chapmandew
ID: 33424688
you just want to check to see if the input param is null?
0
 

Author Comment

by:realcoding
ID: 33424707
null or empty, yes.
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 33424717
will you always supply a param?
0
 

Author Comment

by:realcoding
ID: 33424753
not always, i want to handle all cases... who knows what those nutty devs will be doing.
0
 

Author Comment

by:realcoding
ID: 33424763
i am using this for now but dont know if its good or not...

IF(@x IS NULL or cast(@x as varchar(max)) ='')
Begin
...
End
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 33425960
You cannot pass a "" to an Xml it is not a well-formed document, so there is no need to check for that.  Just NULLs (and personally I would not even check for that, what is the point, eitehr way the client is going to get an error).
0
 
LVL 58

Assisted Solution

by:cyberkiwi
cyberkiwi earned 100 total points
ID: 33426263
anthony,

give this a try

------------------

create proc testbadxml @xml xml as
select n.c.query('.') from @xml.nodes('/') n(c)
GO

declare @xml xml
set @xml = ''
exec testbadxml @xml
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33426273

create proc crudMilestone
( @x xml = null)      --- << I changed, strictly speaking '' is not valid xml
as

IF nullif(convert(varchar(max),@x),'') is null
BEGIN
	select * from StatusCodeGroup2
END
......

Open in new window

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 33426323
You are right.  I stand corrected.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MS SQL Sever Import/export problem 7 47
IF SQL Query 12 29
Options for Linking SQL tables to Access 2013 9 38
SSRS Page Header from Group Data 2 24
Many times as a report developer I've been asked to display normalized data such as three rows with values Jack, Joe, and Bob as a single comma-separated string such as 'Jack, Joe, Bob', and vice versa.  Here's how to do it. 
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…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

749 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