will not sort?? - 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

SQL Server 2005 Forums

Transact-SQL (2005)

will not sort??

Author

Topic

gotafly
Yak Posting Veteran

54 Posts

Posted - 2007-09-18 : 14:05:56

I know this is ugly, but I can not get it to sort. Any ideas?

BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @SQL char(8000)
DECLARE @SQLA char(8000)
DECLARE @SQL1 nvarchar(4000)
DECLARE @SQL2 nvarchar(4000)
DECLARE @SQL3 nvarchar(4000)
DECLARE @SQL4 nvarchar(4000)
DECLARE @whereClause nvarchar(4000)
DECLARE @sortBy nvarchar(4000)
set @whereClause = '';
set @sortBy = 'COUNTYNAME';

SET @SQL1 = 'SELECT DISTINCT CAST(survey_rel_personalInfo.individualSurveyID AS varchar(20)) + ''|1'' AS ID, survey.surveyName AS PERSONNAME,
CountyLevel.levelName AS COUNTYNAME, MunicipalityLevel.levelName AS MUNICIPALITYNAME, Tsurvey.SURVEYTYPEID,
''Individual'' AS SURVEYTYPENAME, survey.lastUpdate AS LASTUPDATE, personalinfo.latitude, personalinfo.longitude,
address.line1 AS ADDRESS, name.first, name.last, name.middle, nameSuffix.suffix
FROM personalInfo LEFT OUTER JOIN
address ON personalInfo.addressID = address.addressID LEFT OUTER JOIN
[level] AS CountyLevel ON personalInfo.countyID = CountyLevel.levelID LEFT OUTER JOIN
[level] AS MunicipalityLevel INNER JOIN
userRights ON MunicipalityLevel.levelID = userRights.levelID ON personalInfo.municipalityID = MunicipalityLevel.levelID LEFT OUTER JOIN
survey_rel_personalInfo ON personalInfo.personalInfoID = survey_rel_personalInfo.personalInfoID LEFT OUTER JOIN
individualSurvey AS survey ON survey_rel_personalInfo.individualSurveyID = survey.individualSurveyID
LEFT OUTER JOIN survey_rel_condition ON survey_rel_personalInfo.individualSurveyID = survey_rel_condition.individualSurveyID
LEFT OUTER JOIN survey_rel_medical_equipment ON survey_rel_personalInfo.individualSurveyID = survey_rel_medical_equipment.individualSurveyID
LEFT OUTER JOIN name ON personalInfo.nameID = name.nameid LEFT OUTER JOIN nameSuffix ON name.namesuffixid = namesuffix.namesuffixid,
(SELECT surveyTypeID FROM surveyType WHERE (surveyTypeID = 1)) AS Tsurvey
WHERE (userRights.accessLevel > 0) AND (countyID <> 0) AND (survey.surveyName IS NOT NULL) AND (municipalityID IS NOT NULL) '

SET @SQL2 = 'SELECT DISTINCT
CAST(survey_rel_personalInfo.individualSurveyID AS varchar(20)) + ''|1'' AS ID, survey.surveyName AS PERSONNAME,
CountyLevel.levelName AS COUNTYNAME, '''' AS MUNICIPALITYNAME, Tsurvey.surveyTypeID, ''Individual'' AS SURVEYTYPENAME,
survey.lastUpdate AS LASTUPDATE, personalinfo.latitude, personalinfo.longitude, address.line1 AS ADDRESS, name.first, name.last, name.middle, nameSuffix.suffix
FROM personalInfo LEFT OUTER JOIN
address ON personalInfo.addressID = address.addressID LEFT OUTER JOIN
[level] AS CountyLevel ON personalInfo.countyID = CountyLevel.levelID LEFT OUTER JOIN
[level] AS MunicipalityLevel INNER JOIN
userRights ON MunicipalityLevel.levelID = userRights.levelID ON personalInfo.municipalityID = MunicipalityLevel.levelID LEFT OUTER JOIN
survey_rel_personalInfo ON personalInfo.personalInfoID = survey_rel_personalInfo.personalInfoID LEFT OUTER JOIN
IndividualSurvey AS survey ON survey_rel_personalInfo.individualSurveyID = survey.individualSurveyID
LEFT OUTER JOIN survey_rel_condition ON survey_rel_personalInfo.individualSurveyID = survey_rel_condition.individualSurveyID
LEFT OUTER JOIN survey_rel_medical_equipment ON survey_rel_personalInfo.individualSurveyID = survey_rel_medical_equipment.individualSurveyID
LEFT OUTER JOIN name ON personalInfo.nameID = name.nameid LEFT OUTER JOIN nameSuffix ON name.namesuffixid = namesuffix.namesuffixid,
(SELECT surveyTypeID FROM surveyType WHERE (surveyTypeID = 1)) AS Tsurvey
WHERE (userRights.accessLevel > 0) AND (countyID <> 0) AND (survey.surveyName IS NOT NULL) AND (municipalityID IS NULL)'

SET @SQL3 = 'SELECT DISTINCT
CAST(survey.facilitySurveyID AS varchar(50)) + ''|2'' AS ID, survey.facilitySurveyName AS PERSONNAME, [level].levelName AS COUNTYNAME,
'''' AS MUNICIPALITYNAME, Tsurvey.surveyTypeID, ''Facility'' AS SURVEYTYPENAME, survey.lastUpdate AS LASTUPDATE,
survey.latitude, survey.longitude, address.line1 AS ADDRESS,
name.first, name.last, name.middle, nameSuffix.suffix
FROM facilitySurvey AS survey LEFT OUTER JOIN
name LEFT OUTER JOIN
nameSuffix ON name.nameSuffixID = nameSuffix.nameSuffixID ON survey.nameID = name.nameID LEFT OUTER JOIN
address ON survey.addressID = address.addressID LEFT OUTER JOIN
[level] ON survey.countyID = [level].levelID LEFT OUTER JOIN
userRights ON [level].levelID = userRights.levelID CROSS JOIN
(SELECT surveyTypeID
FROM surveyType
WHERE (surveyTypeID = 2)) AS Tsurvey
WHERE (userRights.accessLevel > 0) AND (survey.countyID <> 0) AND (survey.facilitySurveyName IS NOT NULL) AND (survey.municipalityID IS NULL) '

SET @SQL4 = 'SELECT DISTINCT
CAST(survey.facilitySurveyID AS varchar(50)) + ''|2'' AS ID, survey.facilitySurveyName AS PERSONNAME, [level].levelName AS COUNTYNAME,
level_1.levelName AS MUNICIPALITYNAME, Tsurvey.surveyTypeID, ''Facility'' AS SURVEYTYPENAME, survey.lastUpdate AS LASTUPDATE,
survey.latitude, survey.longitude, address.line1 AS ADDRESS, name.first, name.last, name.middle, nameSuffix.suffix
FROM facilitySurvey AS survey LEFT OUTER JOIN
name LEFT OUTER JOIN
nameSuffix ON name.nameSuffixID = nameSuffix.nameSuffixID ON survey.nameID = name.nameID LEFT OUTER JOIN
address ON survey.addressID = address.addressID LEFT OUTER JOIN
[level] ON survey.countyID = [level].levelID LEFT OUTER JOIN
[level] AS level_1 ON survey.municipalityID = level_1.levelID LEFT OUTER JOIN
userRights ON level_1.levelID = userRights.levelID CROSS JOIN
(SELECT surveyTypeID
FROM surveyType
WHERE (surveyTypeID = 2)) AS Tsurvey
WHERE (userRights.accessLevel > 0) AND (survey.countyID <> 0) AND (survey.facilitySurveyName IS NOT NULL) AND (survey.municipalityID IS NOT NULL) '

if @whereClause <> ''
if @whereClause like '%condition%' OR @whereClause like '%equipment%'
BEGIN
SET @SQL1 = @SQL1 + @whereClause + ' UNION '
SET @SQL2 = @SQL2 + @whereClause
END
ELSE
BEGIN
SET @SQL1 = @SQL1 + @whereClause + ' UNION '
SET @SQL2 = @SQL2 + @whereClause + ' UNION '
SET @SQLA = @SQL3 + @whereClause + ' UNION ' + @SQL4 + @whereClause
END
ELSE
BEGIN
SET @SQL1 = @SQL1 + ' UNION '
SET @SQL2 = @SQL2 + ' UNION '
SET @SQLA = @SQL3 + ' UNION ' + @SQL4
END

if @sortBy <> ''
BEGIN
SET @SQLA = @SQLA + ' ORDER BY ' + @sortBy
END
ELSE
BEGIN
SET @SQLA = @SQLA + ' ORDER BY PERSONNAME'
END
END
EXEC(@SQL1 + @SQL2 + @SQLA)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-18 : 14:16:28

Rule #1

Always PRINT the statement, copy the output to SSMS/QA and runt the query there.
If it works, you can execute it.
If it is not working you can correct the problem in the pasted query until it works and make equal corrections to the code building the dynamic statement.

E 12°55'05.25"
N 56°04'39.16"

X002548
Not Just a Number

15586 Posts

Posted - 2007-09-18 : 14:17:59

Why is this dynamic SQL?

Because of the Sort?

Create a view then use a case statement on the ORDER BY

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-18 : 14:22:23

Also compare @SQL1 with @SQL2, they are identical until the statements "(municipalityID IS NOT NULL)" and "(municipalityID IS NULL)".
Likewise are @SQL3 and @SQL3 equal to same statements "survey.municipalityID IS NOT NULL" and "survey.municipalityID IS NULL".

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-18 : 14:26:35

I am amazed you can run this query at all!

LEFT JOIN	nameSuffix ON name.namesuffixid = namesuffix.namesuffixid,
(SELECT surveyTypeID FROM surveyType WHERE (surveyTypeID = 1)) AS Tsurvey

E 12°55'05.25"
N 56°04'39.16"

X002548
Not Just a Number

15586 Posts

Posted - 2007-09-18 : 14:30:22

quote:
Originally posted by Peso

Also compare @SQL1 with @SQL2, they are identical until the statements "(municipalityID IS NOT NULL)" and "(municipalityID IS NULL)".
Likewise are @SQL3 and @SQL3 equal to same statements "survey.municipalityID IS NOT NULL" and "survey.municipalityID IS NULL".

E 12°55'05.25"
N 56°04'39.16"

Which means you can lose 2 of them and remove those statements altogether

And yes, it is ugly

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources