troubleshooting Question

Insert variable into Openquery through Stored Procedure

Avatar of kdeutsch
kdeutschFlag for United States of America asked on
Microsoft SQL Server 2008Microsoft SQL ServerOracle Database
5 Comments1 Solution721 ViewsLast Modified:
I am moving a select query becuase It takes some time to populate to my sql server but I can't seem to get the variable working in the openquery.  Tried all the following. Some times I get and error trying to execute  and I change till it will execute but nothing is returned.  I am using people I know have records and if i put in there SSN a record pops up

'' & @SSn & ''
'" & @SSN & "'
''@SSN''
''+@SSN+''
'' + @SSN + ''
ALTER PROC [dbo].[sp_DeersInfo] (@SSn as int)
AS

	Select * from OPENQUERY(SIDP, 'Select NAME_ind, 
											 to_char(pp.DOB, ''mm/dd/yyyy'') DOB, 
											 substr(st.GR_ABBR_CODE, 1,3) Rank, 
											 st.UPC, 
											 CASE WHEN st.ATCH_CODE = ''A'' then at.UPC else '''' end AUPC, 
											 s.LST_UPD_DT, 
											 s.CRD_END_DT, s.DNA_CD, 
											 s.MRTL_STAT_CD, 
											 dd.DEPN_TYPE, 
											 CASE WHEN dd.FAMILY is null then '''' else dd.FAMILY END FAMILY 
										from PERSON_TBL pp LEFT JOIN 
											 SVC_TBL st on st.SSN_SM = pp.SSN_SM LEFT JOIN 
											 SVC_ATCHMT_TBL at on at.ASG_SEQ_NBR = st.ASG_SEQ_NBR LEFT JOIN 
											 SPONSORS s on s.SPN_PN_ID = pp.SSN_SM LEFT OUTER JOIN 
											 (Select d.SPN_PN_ID, d.PNA_RSN_CD, DECODE(d.PNA_RSN_CD,''AB'',''Child'',''AF'',''Child'',''AI'',''Child'',
												''BB'',''Child'',''CA'',''Child'',''AA'',''SPOUSE'','''') DEPN_TYPE, 
											 CASE WHEN d.PN_1ST_NM is null then '''' else d.PN_1ST_NM || '' - '' || ''DOB - '' || d.PN_BRTH_DT || '' 
											 ('' || d.PN_SEX_CD || '')'' END FAMILY from DEPENDANTS d Where d.PN_TYP_CD = ''D'' AND
											 d.PNA_ERSN_CD = ''U'' AND d.PN_DTH_CD = ''N'' AND (d.PNA_RSN_CD in (''AB'',''AF'',''AI'',''BB'',''CA'') or 
											 d.PNA_RSN_CD = ''AA'')) dd ON s.SPN_PN_ID = dd.SPN_PN_ID Where s.SPN_PN_ID = ''@SSn''
											 Order by dd.PNA_RSN_CD')

Open in new window

ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros