Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 489
  • Last Modified:

a single SQL proc for crud with one xml param?

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
realcoding
Asked:
realcoding
  • 5
  • 3
  • 2
  • +1
2 Solutions
 
chapmandewCommented:
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
 
realcodingAuthor Commented:
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
 
realcodingAuthor Commented:
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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
chapmandewCommented:
you just want to check to see if the input param is null?
0
 
realcodingAuthor Commented:
null or empty, yes.
0
 
chapmandewCommented:
will you always supply a param?
0
 
realcodingAuthor Commented:
not always, i want to handle all cases... who knows what those nutty devs will be doing.
0
 
realcodingAuthor Commented:
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
 
Anthony PerkinsCommented:
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
 
cyberkiwiCommented:
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
 
cyberkiwiCommented:

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
 
Anthony PerkinsCommented:
You are right.  I stand corrected.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

  • 5
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now