SP Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SP Help

Author

Topic

steven.liberman1
Starting Member

15 Posts

Posted - 2011-02-28 : 10:39:09

select template_id, template_name, template_content, template_group, template_section,
delete_ind, application
from version5_ABCM..slxHRScreenTemplates
where template_group = 'ADMIN' or template_group = 'Corp Benefits' or template_group = 'Data Proc' or template_group = 'Smartlinx'

select template_id, template_name, template_content, template_group, template_section,
delete_ind, application
from version500..slxHRScreenTemplates
where template_group = 'ADMIN' or template_group = 'Mgr' or template_group = 'Payroll' or template_group = 'HR'

How would I replace the information in version500 with the information from version5_ABCM and write a stored procedure in dynamic SQL? Based on the queries listed above.

Also, keep in mind that template_content column has XML files, so we would have to preserve the XML.

mikgri
Starting Member

39 Posts

Posted - 2011-03-01 : 16:20:18

You can use something like this:

create procedure procedure_name
@DatabaseName varchar(128)='',
@template1 varchar(10)='',
@template2 varchar(10)='',
@template3 varchar(10)=''
as

SET NOCOUNT ON;

declare @sql nvarchar(max), @maxvertiszonesetid int
declare @expression varchar(400), @expression1 varchar(100), @expression2 varchar(100),
@expression3 varchar(100)

if len(@template1)>0
set @expression1=' and template_group='''+@template1+''''

if len(@template2)>0
set @expression2=' and template_group='''+@template2+''''

if len(@template3)>0
set @expression3=' and template_group='''+@template3+''''

select @expression='where template_group=''ADMIN'''+@expression1+@expression2+@expression3

set @sql='use [%db];
select template_id, template_name, template_content, template_group, template_section,
delete_ind, application
from slxHRScreenTemplates '+@expression
set @sql=replace(@sql, '%db', @DatabaseName)
execute sp_executesql @sql

--I didn't pass "Admin" as a parameter because it is same for both queries.
Don't have data to check how it works.

steven.liberman1
Starting Member

15 Posts

Posted - 2011-03-02 : 14:20:39

Basically I need a dynamic sql stored procedure for this, because I need to replace everything based on template_group correspondence.

So Admin field will remain admin
Manager Field will be replaced by Corp Benefits
Payroll will be replaced by Data Proc
HR will be replaced by smartlinx.

But I need to preserve XML.

steven.liberman1
Starting Member

15 Posts

Posted - 2011-03-07 : 09:54:19

create procedure WEB_HR_TemplateUpdate {
@template_group varchar(1000)
}
as

-- select * from version5_ABCM..slxhrscreentemplates

-- select * from version500.slxhrscreentemplates

declare @template_id int
declare @template_name varchar(10)
declare @template_content XML
declare @template_section varchar(20)
declare @delete_ind int
declare @application varchar(10)

Begin

If version500..slxhrcreentemplates.template_group in ('ADMIN','Manager','Payroll','HR')
set @template_id = (select template_id from version5_ABCM..slxhrscreentemplates where template_group = 'Admin' and
template_group = 'Corp Benefits' and template_group = 'Data Proc' and template_group = 'Smartlinx')

If version500..slxhrcreentemplates.template_group in ('ADMIN','Manager','Payroll','HR')
set @template_name = (select template_name from version5_ABCM..slxhrscreentemplates where template_group = 'Admin' and
template_group = 'Corp Benefits' and template_group = 'Data Proc' and template_group = 'Smartlinx')

This is what I have so far

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources