Question on writing Stored procedure??? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server 2005 Forums

Transact-SQL (2005)

Question on writing Stored procedure???

Author

Topic

Nav5222
Starting Member

1 Post

Posted - 2011-02-03 : 18:55:20

Hello Folks,
Am a beginner in Tsql and I have a requirement to write a stored procedure and am all confused how to start and unable to make a headway.
Usually i have this query which i run every month and get the data into Excel which after i just use the Sql Server Import and Export wizard using BIDS dump the Excel data into SQL server Table. The query is nothing but retreiving the clientcode and employergroup for the previous month from the summary table in Oracle.
I have requirement to automate this thing of manually running the Import and Export Wizard for every client every month which takes lot of time.

So basically i have to do is to create a Stored procedure in sql server to get the data from oracle using LINKED SERVER. Can anyone please help me out on how to start?? The query is below.

select '801' report_no, -- i have to include the logic for different set of reports

null CLIENT_ID,

r.payor_key CLIENT_CODE,

null CLIENT_NAME,

r.payor_key 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, '/', ' ') employer_group_code,

translate(substr(group_name,
instr(group_name, '-') + 1,
length(group_name)),
'/\',
' ') employer_group_name,

'{R08_SUMMARY.PAYOR_KEY} = ' || '"' || r.payor_key || '"' ||

' and {R08_SUMMARY.GROUP_NAME} = "' || r.group_name || '"' selform,

from summary r

where r.month_id = '201012' --i hav to include the logic to get the data every month i have to change this month

and r.payor_key IN ('HM') -- i have to include the logic to create this for various clients such as HM,BM,CM etc

and r.print_flag = 'Y'

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-03 : 20:22:51

something like this might get you started:

CREATE PROCEDURE AS pGetReport 
	(@ReportNo varchar(10),
	 @monthid varchar(6)
	 @payorkey varchar(2))


SELECT 
		@ReportNo AS report_no, -- i have to include the logic for different set of reports
		null AS CLIENT_ID,
		r.payor_key AS CLIENT_CODE,
		null AS CLIENT_NAME,
		r.payor_key AS legacy_client_code,
		null AS major_client_name,
		null  AS major_client_code,
		null AS end_user_id,
		null AS [user_name],
		null AS employer_group_id,
		replace(r.group_ref_key, '/', ' ') AS employer_group_code,
		
		REPLACE(substring(group_name,charindex('-')+1,len(group_Name)-charindex('-')),'/\',' ') AS employer_group_name,

		'{R08_SUMMARY.PAYOR_KEY} =' + char(34) + r.payor_key +char(34) +
		' and {R08_SUMMARY.GROUP_NAME} =' + char(34) + r.group_name + char(34)  as selform,
FROM summary r
WHERE 
	r.month_id = @monthid --i hav to include the logic to get the data every month i have to change this month
	and r.payor_key IN (@payorkey) -- i have to include the logic to create this for various clients such as HM,BM,CM etc
	and r.print_flag = 'Y' 

GO

--sample call

EXEC pGetReport '801','201012','HM'

Poor planning on your part does not constitute an emergency on my part.

Remo522
Starting Member

3 Posts

Posted - 2011-02-04 : 09:53:38

HI There. Thanks for getting back. Am trying to get the data from Oracle. So woudnt we use something like Open Query.Also i have another question. I want to insert this data into a temp table. How can i acheive that?

CREATE PROCEDURE AS pGetReport
(@ReportNo varchar(10),
@monthid varchar(6)
@payorkey varchar(2))

Select * from OpenQuery(EOM,'
SELECT
@ReportNo AS report_no, -- i have to include the logic for different set of reports
null AS CLIENT_ID,
r.payor_key AS CLIENT_CODE,
null AS CLIENT_NAME,
r.payor_key AS legacy_client_code,
null AS major_client_name,
null AS major_client_code,
null AS end_user_id,
null AS [user_name],
null AS employer_group_id,
replace(r.group_ref_key, '/', ' ') AS employer_group_code,

REPLACE(substring(group_name,charindex('-')+1,len(group_Name)-charindex('-')),'/\',' ') AS employer_group_name,

'{R08_SUMMARY.PAYOR_KEY} =' + char(34) + r.payor_key +char(34) +
' and {R08_SUMMARY.GROUP_NAME} =' + char(34) + r.group_name + char(34) as selform,
FROM summary r
WHERE
r.month_id = @monthid --i hav to include the logic to get the data every month i have to change this month
and r.payor_key IN (@payorkey) -- i have to include the logic to create this for various clients such as HM,BM,CM etc
and r.print_flag = 'Y' ')

GO

Is this correct way of using Linked Servers?

Remo522
Starting Member

3 Posts

Posted - 2011-02-04 : 11:05:38

I tried to execute it but i get the following error

OLE DB provider "OraOLEDB.Oracle" for linked server "EOM" returned message "ORA-00936: missing expression"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources