We help IT Professionals succeed at work.
Get Started

Insert variable into Openquery through Stored Procedure

719 Views
Last Modified: 2012-05-11
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

Comment
Watch Question
Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
This problem has been solved!
Unlock 1 Answer and 5 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE