June 15, 2004 at 3:53 pm
I am having a problem with
DECLARE @strSDE_VALID_IND as VARCHAR(5)
DECLARE @strBlank as VARCHAR(5)
in this stored procedure below:
CREATE PROCEDURE [DBO].[sp_BR_Notmapped_BPMN]
@dbname AS VARCHAR(255)
AS
DECLARE @sql as VARCHAR(2000)
DECLARE @strFilter as Varchar(500)
DECLARE @strSDE_VALID_IND as VARCHAR(5)
DECLARE @strBlank as VARCHAR(5)
SET @strFilter = ''
SET @strSDE_VALID_IND = 'F'
SET @strBlank = ''
SELECT @sql = 'SELECT
DEF.DEF_NM as [Entity Name],
DEF.DEF_DES_TX as [Entity Description],
DEF.DEF_TYPE_CD as [Definition Type],
DEF.DOM_CD as [Domain Code],
SDE_TIMELINESS.SDE_DATA_SENSITIVITY_CD AS [IA_Attribute_Type_Name]
FROM
[' + @dbname + '].DBO.DEF AS DEF
INNER JOIN [' + @dbname + '].DBO. SDE_TIMELINESS AS SDE_TIMELINESS
on DEF.SDE_TIMELINESS_CD = SDE_TIMELINESS.SDE_TIMELINESS_CD
WHERE
SDE_TIMELINESS.VALID_IND = "' + @strSDE_VALID_IND + '"
OR DEF.SDE_TIMELINESS_CD IS NULL
UNION
SELECT
DEF.DEF_NM as [Entity Name],
DEF.DEF_DES_TX as [Entity Description],
DEF.DEF_TYPE_CD as [Definition Type],
DEF.DOM_CD as [Domain Code],
SDE_DATA_SENSITIVITY.SDE_DATA_SENSITIVITY_CD AS [IA_Attribute_Type_Name]
FROM
[' + @dbname + '].DBO.DEF AS DEF
INNER JOIN [' + @dbname + '].DBO.SDE_DATA_SENSITIVITY AS SDE_DATA_SENSITIVITY
on DEF.SDE_DATA_SENSITIVITY_CD = SDE_DATA_SENSITIVITY.SDE_DATA_SENSITIVITY_CD
WHERE
SDE_DATA_SENSITIVITY.VALID_IND = ' + @strSDE_VALID_IND + '
OR DEF.SDE_DATA_SENSITIVITY_CD IS NULL
UNION
SELECT
DEF.DEF_NM as [Entity Name],
DEF.DEF_DES_TX as [Entity Description],
DEF.DEF_TYPE_CD as [Definition Type],
DEF.DOM_CD as [Domain Code],
SDE_AVAILABILITY.SDE_AVAILABILITY_CD AS [IA_Attribute_Type_Name]
FROM
[' + @dbname + '].DBO.DEF AS DEF
INNER JOIN [' + @dbname + '].DBO.SDE_AVAILABILITY AS SDE_AVAILABILITY
on DEF.SDE_AVAILABILITY_CD = SDE_AVAILABILITY.SDE_AVAILABILITY_CD
WHERE
SDE_AVAILABILITY.VALID_IND = ' + @strSDE_VALID_IND + '
OR DEF.SDE_AVAILABILITY_CD IS NULL
UNION
SELECT
DEF.DEF_NM as [Entity Name],
DEF.DEF_DES_TX as [Entity Description],
DEF.DEF_TYPE_CD as [Definition Type],
DEF.DOM_CD as [Domain Code],
SDE_INTEGRITY_CHECKS.SDE_INTEGRITY_CHECKS_CD AS [IA_Attribute_Type_Name]
FROM
[' + @dbname + '].DBO.DEF AS DEF
INNER JOIN [' + @dbname + '].DBO.SDE_INTEGRITY_CHECKS AS SDE_INTEGRITY_CHECKS
on DEF.SDE_INTEGRITY_CHECKS_CD = SDE_INTEGRITY_CHECKS.SDE_INTEGRITY_CHECKS_CD
WHERE
SDE_INTEGRITY_CHECKS.VALID_IND = ' + @strSDE_VALID_IND + '
OR DEF.SDE_INTEGRITY_CHECKS_CD IS NULL
UNION
SELECT
DEF.DEF_NM as [Entity Name],
DEF.DEF_DES_TX as [Entity Description],
DEF.DEF_TYPE_CD as [Definition Type],
DEF.DOM_CD as [Domain Code],
SDE_CONFIDENTIALITY.SDE_CONFIDENTIALITY_CD AS [IA_Attribute_Type_Name]
FROM
[' + @dbname + '].DBO.DEF AS DEF
INNER JOIN [' + @dbname + '].DBO.SDE_CONFIDENTIALITY AS SDE_CONFIDENTIALITY
on DEF.SDE_CONFIDENTIALITY_CD = SDE_CONFIDENTIALITY.SDE_CONFIDENTIALITY_CD
WHERE
SDE_CONFIDENTIALITY.VALID_IND = ' + @strSDE_VALID_IND + '
OR DEF.SDE_CONFIDENTIALITY_CD IS NULL
ORDER BY IA_Attribute_Type_Name, SDE_Name ASC
'
print @sql
EXEC (@sql)
GO
I want to set them so that when this stored procedure is run, it produces:
..............
INNER JOIN [BEA_Ency_2.0.7].DBO. SDE_TIMELINESS AS SDE_TIMELINESS
on DEF.SDE_TIMELINESS_CD = SDE_TIMELINESS.SDE_TIMELINESS_CD
WHERE
SDE_TIMELINESS.VALID_IND = 'F'
OR DEF.SDE_TIMELINESS_CD IS NULL
UNION
....................
Right now, I am getting
INNER JOIN [BEA_Ency_2.0.7].DBO. SDE_TIMELINESS AS SDE_TIMELINESS
on DEF.SDE_TIMELINESS_CD = SDE_TIMELINESS.SDE_TIMELINESS_CD
WHERE
SDE_TIMELINESS.VALID_IND = F
OR DEF.SDE_TIMELINESS_CD IS NULL
UNION
June 16, 2004 at 7:38 am
Michael,
Try using the literal value of the single-quote special character, CHAR(39), and concatenate this to the rest of your code.
Eddie
"If you don't take the time to do it right, when are you going to find the time to do it over?"
June 25, 2004 at 7:19 am
Hello Michael,
you should code your query this way:
FROM
[' + @dbname + '].DBO.DEF AS DEF
INNER JOIN [' + @dbname + '].DBO.SDE_CONFIDENTIALITY AS SDE_CONFIDENTIALITY
on DEF.SDE_CONFIDENTIALITY_CD = SDE_CONFIDENTIALITY.SDE_CONFIDENTIALITY_CD
WHERE
SDE_CONFIDENTIALITY.VALID_IND = ''' + @strSDE_VALID_IND + '''
OR DEF.SDE_CONFIDENTIALITY_CD IS NULL
ORDER BY IA_Attribute_Type_Name, SDE_Name ASC
'
Wish you luck
Alexander
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply