Mahesh Yadav
asked on
HOW TO REPLACE A WORD FROM THE END OF T SQL STRING
I am running a while loop in my sql query
and creating a sql string, which I am executing at the end.
I am adding Union All in the sql server inside the while loop.
now I want to remove the last Union all from the sql string before running it otherwise I am getting an error becasue after last Union all there is no sql statements because the while loop ends.
Can any one help me out here, how can I replace the last union all from my sql string.
and creating a sql string, which I am executing at the end.
I am adding Union All in the sql server inside the while loop.
now I want to remove the last Union all from the sql string before running it otherwise I am getting an error becasue after last Union all there is no sql statements because the while loop ends.
Can any one help me out here, how can I replace the last union all from my sql string.
By the way.
Set @Str = left(@str,len(@str)-9)
will give you the same results if you prefer.
Set @Str = left(@str,len(@str)-9)
will give you the same results if you prefer.
ASKER
here is my complete code
DECLARE @PropertyProfitField VARCHAR(255), @STRSQL NVARCHAR(4000)
DECLARE TemCursor CURSOR FOR
SELECT PropertyProfitField FROM PropertyProfitFields
WHERE PropertyProfitField NOT IN
('CloseInspectionDescripti on', 'PropertyProfitID', 'Active','PropertyProfitNu mber','Reh abMiscDesc ription',' CloseMiscD escription '
,'UpdatedOn','UpdatedBy',' SaleClosin gDate','Ac qPurchaseC losingDate ','AcqComm issionTo', 'RehabSupe rvisionFee To')
OPEN TemCursor
FETCH NEXT FROM TemCursor
INTO @PropertyProfitField
SET @STRSQL = ''
WHILE @@FETCH_STATUS = 0
BEGIN
SET @STRSQL = 'SELECT ' + @PropertyProfitField + ', PropertyProfit.' + @PropertyProfitField + ' as [Proposed] ' +
', (SELECT PropertyProfit.' + @PropertyProfitField + ' ' +
'FROM PropertyProfit INNER JOIN ' +
'PropertyProfit_PropertyPr ofitType ON PropertyProfit.PropertyPro fitID = PropertyProfit_PropertyPro fitType.Pr opertyProf itID ' +
'INNER JOIN PropertyProfitType ON PropertyProfit_PropertyPro fitType.Pr opertyProf itTypeID = PropertyProfitType.Propert yProfitTyp eID ' +
'WHERE PropertyProfit_PropertyPro fitType.Pr opertyID = 1607 ' +
'AND PropertyProfitType.Propert yProfitTyp e = ''ListingPrice'') as [ListingPrice] ' +
', (SELECT PropertyProfit.' + @PropertyProfitField + ' ' +
'FROM PropertyProfit INNER JOIN ' +
'PropertyProfit_PropertyPr ofitType ON PropertyProfit.PropertyPro fitID = PropertyProfit_PropertyPro fitType.Pr opertyProf itID ' +
'INNER JOIN PropertyProfitType ON PropertyProfit_PropertyPro fitType.Pr opertyProf itTypeID = PropertyProfitType.Propert yProfitTyp eID ' +
'WHERE PropertyProfit.Active = 1 AND PropertyProfit_PropertyPro fitType.Pr opertyID = 1607 ' +
'AND PropertyProfitType.Propert yProfitTyp e = ''ListingPrice'') as [CurrentAccepted] ' +
', (SELECT PropertyProfit.' + @PropertyProfitField + ' ' +
'FROM PropertyProfit INNER JOIN ' +
'PropertyProfit_PropertyPr ofitType ON PropertyProfit.PropertyPro fitID = PropertyProfit_PropertyPro fitType.Pr opertyProf itID ' +
'INNER JOIN PropertyProfitType ON PropertyProfit_PropertyPro fitType.Pr opertyProf itTypeID = PropertyProfitType.Propert yProfitTyp eID ' +
'WHERE PropertyProfit_PropertyPro fitType.Pr opertyID = 1607 ' +
'AND PropertyProfitType.Propert yProfitTyp e = ''ListingPrice'') as [Final] ' +
'FROM PropertyProfit INNER JOIN ' +
'PropertyProfit_PropertyPr ofitType ON PropertyProfit.PropertyPro fitID = PropertyProfit_PropertyPro fitType.Pr opertyProf itID ' +
'INNER JOIN PropertyProfitType ON PropertyProfit_PropertyPro fitType.Pr opertyProf itTypeID = PropertyProfitType.Propert yProfitTyp eID ' +
'WHERE PropertyProfit_PropertyPro fitType.Pr opertyID = 1607 ' +
'AND PropertyProfitType.Propert yProfitTyp e = ''Proposed'''
SET @STRSQL += ' UNION ALL '
FETCH NEXT FROM TemCursor
INTO @PropertyProfitField
END
CLOSE TemCursor
DEALLOCATE TemCursor
print left(@STRSQL,len(@STRSQL)- 9)
EXECUTE sp_executesql @STRSQL
DECLARE @PropertyProfitField VARCHAR(255), @STRSQL NVARCHAR(4000)
DECLARE TemCursor CURSOR FOR
SELECT PropertyProfitField FROM PropertyProfitFields
WHERE PropertyProfitField NOT IN
('CloseInspectionDescripti
,'UpdatedOn','UpdatedBy','
OPEN TemCursor
FETCH NEXT FROM TemCursor
INTO @PropertyProfitField
SET @STRSQL = ''
WHILE @@FETCH_STATUS = 0
BEGIN
SET @STRSQL = 'SELECT ' + @PropertyProfitField + ', PropertyProfit.' + @PropertyProfitField + ' as [Proposed] ' +
', (SELECT PropertyProfit.' + @PropertyProfitField + ' ' +
'FROM PropertyProfit INNER JOIN ' +
'PropertyProfit_PropertyPr
'INNER JOIN PropertyProfitType ON PropertyProfit_PropertyPro
'WHERE PropertyProfit_PropertyPro
'AND PropertyProfitType.Propert
', (SELECT PropertyProfit.' + @PropertyProfitField + ' ' +
'FROM PropertyProfit INNER JOIN ' +
'PropertyProfit_PropertyPr
'INNER JOIN PropertyProfitType ON PropertyProfit_PropertyPro
'WHERE PropertyProfit.Active = 1 AND PropertyProfit_PropertyPro
'AND PropertyProfitType.Propert
', (SELECT PropertyProfit.' + @PropertyProfitField + ' ' +
'FROM PropertyProfit INNER JOIN ' +
'PropertyProfit_PropertyPr
'INNER JOIN PropertyProfitType ON PropertyProfit_PropertyPro
'WHERE PropertyProfit_PropertyPro
'AND PropertyProfitType.Propert
'FROM PropertyProfit INNER JOIN ' +
'PropertyProfit_PropertyPr
'INNER JOIN PropertyProfitType ON PropertyProfit_PropertyPro
'WHERE PropertyProfit_PropertyPro
'AND PropertyProfitType.Propert
SET @STRSQL += ' UNION ALL '
FETCH NEXT FROM TemCursor
INTO @PropertyProfitField
END
CLOSE TemCursor
DEALLOCATE TemCursor
print left(@STRSQL,len(@STRSQL)-
EXECUTE sp_executesql @STRSQL
In This case you could just move the 'UNION ALL' until after the FETCH.
But there is a better solution. I will add in a minute
But there is a better solution. I will add in a minute
FETCH NEXT FROM TemCursor
INTO @PropertyProfitField
if @@FETCH_STATUS = 0
SET @STRSQL += ' UNION ALL '
Instead of using a cursor you can create the script in one select which will concatenate the results to one string.
SELECT @STRSQL = COALESCE(@STRSQL + ' UNION ALL ', '') + 'SELECT ' + PropertyProfitField + ', PropertyProfit.' + PropertyProfitField + ' as [Proposed] ' +
', (SELECT PropertyProfit.' + PropertyProfitField + ' ' +
'FROM PropertyProfit INNER JOIN ' +
'PropertyProfit_PropertyProfitType ON PropertyProfit.PropertyProfitID = PropertyProfit_PropertyProfitType.PropertyProfitID ' +
'INNER JOIN PropertyProfitType ON PropertyProfit_PropertyProfitType.PropertyProfitTypeID = PropertyProfitType.PropertyProfitTypeID ' +
'WHERE PropertyProfit_PropertyProfitType.PropertyID = 1607 ' +
'AND PropertyProfitType.PropertyProfitType = ''ListingPrice'') as [ListingPrice] ' +
', (SELECT PropertyProfit.' + PropertyProfitField + ' ' +
'FROM PropertyProfit INNER JOIN ' +
'PropertyProfit_PropertyProfitType ON PropertyProfit.PropertyProfitID = PropertyProfit_PropertyProfitType.PropertyProfitID ' +
'INNER JOIN PropertyProfitType ON PropertyProfit_PropertyProfitType.PropertyProfitTypeID = PropertyProfitType.PropertyProfitTypeID ' +
'WHERE PropertyProfit.Active = 1 AND PropertyProfit_PropertyProfitType.PropertyID = 1607 ' +
'AND PropertyProfitType.PropertyProfitType = ''ListingPrice'') as [CurrentAccepted] ' +
', (SELECT PropertyProfit.' + PropertyProfitField + ' ' +
'FROM PropertyProfit INNER JOIN ' +
'PropertyProfit_PropertyProfitType ON PropertyProfit.PropertyProfitID = PropertyProfit_PropertyProfitType.PropertyProfitID ' +
'INNER JOIN PropertyProfitType ON PropertyProfit_PropertyProfitType.PropertyProfitTypeID = PropertyProfitType.PropertyProfitTypeID ' +
'WHERE PropertyProfit_PropertyProfitType.PropertyID = 1607 ' +
'AND PropertyProfitType.PropertyProfitType = ''ListingPrice'') as [Final] ' +
'FROM PropertyProfit INNER JOIN ' +
'PropertyProfit_PropertyProfitType ON PropertyProfit.PropertyProfitID = PropertyProfit_PropertyProfitType.PropertyProfitID ' +
'INNER JOIN PropertyProfitType ON PropertyProfit_PropertyProfitType.PropertyProfitTypeID = PropertyProfitType.PropertyProfitTypeID ' +
'WHERE PropertyProfit_PropertyProfitType.PropertyID = 1607 ' +
'AND PropertyProfitType.PropertyProfitType = ''Proposed'''
FROM PropertyProfitFields
WHERE PropertyProfitField NOT IN
('CloseInspectionDescription', 'PropertyProfitID', 'Active','PropertyProfitNumber','RehabMiscDescription','CloseMiscDescription'
,'UpdatedOn','UpdatedBy','SaleClosingDate','AcqPurchaseClosingDate','AcqCommissionTo','RehabSupervisionFeeTo')
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Set @Str = Substring(@str, 1, len(@str)- 9)
Another way is to make your While more clever not to add the last "Union All", but I cannot help without the code.