the ' character - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

the ' character

Author

Topic

GavinD1977
Yak Posting Veteran

83 Posts

Posted - 2006-11-16 : 04:43:59

Hello all.

Ok this is going to be a little hard to describe but here goes

I have a table within which one column is used to store a SQL select statement as a string. This means that the whole select statement has to be enclosed within two ' characters. The problem is that the SQL statement itself contains these characters. The specific SQL statement i wish to save as a string is:

select
'<A HREF=DisplayOnlyDiscipline?SESSION_ID=' + :SESSION_ID +
'&DISP_REF=' + cast(DISP_REF as varchar) +
'&EDIT_REF=' + :EDIT_REF + '>' + cast(DISP_REF as varchar) + '</A>' as Reference,
V.DESCRIPTION as Discipline_Stage,
DISP_DATE as Date
from
DISCPLIN D
left outer join
V_DISP V on (V.CODE = D.DISP_CODE)
where
EMPLOY_REF = :EDIT_REF
order by
DISP_DATE DESC

Can anyone suggest how i get round this?

thanks for reading peeps

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-16 : 04:46:51

1) What is ":SESSION_ID" ?
2) What is ":EDIT_REF" ?

Are they column names in table DISCPLIN or V_DISP?

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-16 : 04:50:24

[code]select '<A HREF=DisplayOnlyDiscipline?SESSION_ID=' + CAST(xxx.SESSION_ID AS varchar)
+ '&DISP_REF=' + cast(xxx.DISP_REF as varchar)
+ '&EDIT_REF=' + cast(xxx.EDIT_REF as varchar)
+ '>' + cast(xxx.DISP_REF as varchar) + '</A>' as Reference,
V.DESCRIPTION as Discipline_Stage,
xxx.DISP_DATE as Date
from DISCPLIN D
left join V_DISP V on V.CODE = D.DISP_CODE
where xxx.EMPLOY_REF = xxx.EDIT_REF
order by xxx.DISP_DATE DESC[/code]If EMPLOY_REF and/or EDIT_REF is in table V_DISP, change to this[code]select '<A HREF=DisplayOnlyDiscipline?SESSION_ID=' + CAST(xxx.SESSION_ID AS varchar)
+ '&DISP_REF=' + cast(xxx.DISP_REF as varchar)
+ '&EDIT_REF=' + cast(xxx.EDIT_REF as varchar)
+ '>' + cast(xxx.DISP_REF as varchar) + '</A>' as Reference,
V.DESCRIPTION as Discipline_Stage,
xxx.DISP_DATE as Date
from DISCPLIN D
left join V_DISP V on V.CODE = D.DISP_CODE AND xxx.EMPLOY_REF = xxx.EDIT_REF
order by xxx.DISP_DATE DESC[/code]ALSO CHANGE ALL xxx. TABLE PREFIXES TO THE REAL ONES.

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-11-16 : 05:08:08

You need to double the single quotes before saving it


declare @sql varchar(2000)

select @sql='
        ''<A HREF=DisplayOnlyDiscipline?SESSION_ID='' + :SESSION_ID +
        ''&DISP_REF='' + cast(DISP_REF as varchar) +
        ''&EDIT_REF='' + :EDIT_REF + ''>'' + cast(DISP_REF as varchar) + ''</A>'' as Reference,
        V.DESCRIPTION as Discipline_Stage,
        DISP_DATE as Date from DISCPLIN D
left outer join V_DISP V on (V.CODE = D.DISP_CODE)
        where EMPLOY_REF = :EDIT_REF
order by DISP_DATE DESC'

print @sql

Madhivanan

Failing to plan is Planning to fail

GavinD1977
Yak Posting Veteran

83 Posts

Posted - 2006-11-16 : 06:18:49

Brilliant guys..........works perfectly. Thanks for your help. Much appreciated.

Peso.....the :SESSION_ID and :EDIT_REF are values pulled from an HTML page.

thanks again peeps.