CREATE PROCEDURE usp_ShareHolders_Main_sel

@CompanyCode varchar(4),
@OrderByThisColumn varchar(30)

AS

DECLARE @StringSelect varchar(3000),
@StringWhere varchar(500),
@StringOrderBy varchar(200),
@ConcatStrings nvarchar(3700) --must be nvarchar datatype

--Get entire SELECT and FROM statement and set into variable.
SELECT @StringSelect =

'SELECT
H.Fund_Man as Holders,
H.Shares as SharesHeld,
H.Share_Pric * H.Shares as Value,
H.Pcent as SharesOutstanding,
H.Shares - H.Shares as ShareChange,
C.Reg_Date as ReportDate,
'Register' as Source,
((C.Capital / S.CapTotal) * (H.TotalTot * S.CapTotal)) / C.Capital as SectorWeightingPcent,
H.Pcent - (((C.Capital / S.CapTotal) * (H.TotalTot * S.CapTotal)) / C.Capital) as OverUnderWeight,
(H.Pcent - (((C.Capital / S.CapTotal) * (H.TotalTot * S.CapTotal)) / C.Capital)) * C.isc as SurplusDeficit

FROM
Citywatch_Company C
Inner Join
Citywatch_Holders H
On
C.Epic = H.Epic
Inner Join
Citywatch_Sector S
On
H.Sector = S.Sec_Code '

--Select WHERE statement into variable
Select @StringWhere =

' WHERE
C.Epic = ' + @CompanyCode

--Select ORDERBY statement into variable
Select @StringOrderBy =
' ORDER BY ' + @OrderByThisColumn

--Concat Strings
Select @ConcatStrings = @StringSelect + @StringWhere + @StringOrderBy

--Compile and execute the string
EXEC sp_executesql @ConcatStrings

GO

I hope this helps.
Thanks
Greg G.


------------
Paul at 1/22/01 1:12:53 PM

I am using SQL2000.

I have a SELECT statement in an SP that selects 10 fields, however, i want to be able to pass a variable to the SP to determine which field to ORDER BY.

Is there a way to do this ?

I've tried passing in one of the field names to a variable and then doing ORDER BY @OrderByThisColumn ...nope.
I've tried SETting a variable to the above @OrderByThisColumn ...nope.

Any ideas/clues would be a great help

The code...........

CREATE PROCEDURE usp_ShareHolders_Main_sel

@CompanyCode varchar(4),
@OrderByThisColumn varchar(30)

AS

SELECT
H.Fund_Man as Holders,
H.Shares as SharesHeld,
H.Share_Pric * H.Shares as Value,
H.Pcent as SharesOutstanding,
H.Shares - H.Shares as ShareChange,
C.Reg_Date as ReportDate,
'Register' as Source,
((C.Capital / S.CapTotal) * (H.TotalTot * S.CapTotal)) / C.Capital as SectorWeightingPcent,
H.Pcent - (((C.Capital / S.CapTotal) * (H.TotalTot * S.CapTotal)) / C.Capital) as OverUnderWeight,
(H.Pcent - (((C.Capital / S.CapTotal) * (H.TotalTot * S.CapTotal)) / C.Capital)) * C.isc as SurplusDeficit

FROM
Citywatch_Company C
Inner Join
Citywatch_Holders H
On
C.Epic = H.Epic
Inner Join
Citywatch_Sector S
On
H.Sector = S.Sec_Code
WHERE
C.Epic = @CompanyCode

ORDER BY <one of the select columns above>


Help me please someone
Paul