Insert variable into Openquery through Stored Procedure

kdeutsch
kdeutsch used Ask the Experts™
on
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

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Billing Engineer
Most Valuable Expert 2014
Top Expert 2009
Commented:
you cannot use variables in openquery.
the only way to get it working is to put the whole query into dynamic sql query, like:

...
declare @sql varchar(max)
set @sql = ' select ... from openquery( .. , ''select ... from  ... where ssn = ''''' + @ssn + '''''  and ... '' ) '
exec (@sql)

Open in new window


and yes, there are 5 single quotes.
kdeutsch

Author

Commented:
angelIII:

Ok, I made the switch but now I get a converstion error on my page and it lists out the sql code.

Exception Details: System.Data.SqlClient.SqlException: Conversion failed when converting the varchar value ' Select * from OPENQUERY(SIDPERS, 'Select NAME_ind, 

ALTER PROC [dbo].[sp_DeersInfo] (@SSn as int)
AS

Declare @Sql varchar(max)

set @Sql = ' Select * from OPENQUERY(SIDPERS, ''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 
											 SVCM_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'')'
											 
	Exec(@sql)

Open in new window

kdeutsch

Author

Commented:
Ok,
took care of error above it was not liking the SSn as a integer but then this error pops up.

Incorrect syntax near 'mm'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near 'mm'.

Source Error:


Line 22:
Line 23:         da.SelectCommand = cmd
Line 24:         da.Fill(ds)

 
kdeutsch

Author

Commented:
ok,
never mind I see I needed to go back and put in more tick marks, its working now.  Thanks for the help.
kdeutsch

Author

Commented:
thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial