Link to home
Start Free TrialLog in
Avatar of kdeutsch
kdeutschFlag for United States of America

asked on

Insert variable into Openquery through Stored Procedure

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
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of kdeutsch

ASKER

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

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)

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