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.
| Author |
Topic |
|
Nav522
Starting Member
27 Posts |
 Posted - 2011-02-05 : 14:01:29
|
| Hello Folks, I think i got the open query stored procedure working but i got struck on adding the logic to look for all the reportno and all payors. How would i achieve that. Any ideas??Now am executing for each payor such asexec '801', '20102' 'HM'ALTER PROCEDURE pGetReportData (@ReportNo varchar(10), @monthid varchar(20), @payorkey varchar(10)) AS declare @reportqry varchar(2000) IF EXISTS(SELECT 1 FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'AposGrpwise') AND type = (N'U')) DROP TABLE AposGrpwise set @reportqry = 'select * into AposGrpwise from openquery(EOM,''select ''''' + @ReportNo + ''''' as report_no, r.payor_key as CLIENT_CODE, null CLIENT_NAME, r.payor_key as legacy_client_code, null major_client_name, null major_client_code, null end_user_id, null user_name, null employer_group_id, replace(r.group_ref_key, ''''/'''', '''' '''') as employer_group_code, translate(substr(group_name, instr(group_name, ''''-'''') + 1, length(group_name)), ''''/\'''', '''' '''') as employer_group_name, null branch_group_id, null branch_group_code, null branch_group_name, ''''{R08_SUMMARY.PAYOR_KEY} = '''' || ''''"'''' || r.payor_key || ''''"'''' || '''' and {R08_SUMMARY.GROUP_NAME} = "'''' || r.group_name || ''''"'''' as selform, null path, null email, ''''A'''' status, ''''EMPGRPWISE CLIENT BO'''' burst_type, ''''PDF'''' burst_file_type, ''''-1'''' id, null bouser, ''''G'''' std_rpt_burst from r08_summary1 r where r.month_id = '''''+ @monthid + ''''' and r.payor_key = '''''+ @payorkey + ''''' and r.print_flag = ''''Y'''''')' print(@reportqry) |
|
|
Remo522
Starting Member
3 Posts |
Posted - 2011-02-08 : 09:23:00
|
| can anyone please help with this? |
 |
|
|
|
|
|
|
|
OpenQuery Stored procedure