@kaffenils - thanks for the info. Normally we have the user enter that "comma-delimited" string in the client-side - so passing that varchar(100) string makes sense in most of our cases - but I will keep this XML trick in mind
@waely - use [vbcode] and [/vbcode] tags (or just "code" tags) around you sql and vb code - it will format it nicer.
This should work for you (and note that I do not have the "CREATE PROCEDURE" line from your SPROC - so I cannot see how you are defining the parameters:
You mention that you are having speed issues with this VIEW and are going the SPROC route in hopes of fixing it.
...Create Procedure SomeSproc @Param1 int, @Param2 int, @ListStr varchar(100)
-- Note that I am assuming you have some parameters already - I've added this third
-- parameter to pass in the "list string"
Set NoCount On
Declare @ListTbl Table (SelectValue int)
Declare @CP int
Declare @SV int
Set @SV=Cast(Left(@ListStr,@CP-1) as int)
Set @SV=Cast(@ListStr as int)
Insert into @ListTbl Values (@SV)
-- We have now built our "table variable" with a row for each "list item"
SELECT TOP 100 PERCENT
code AS TA, code2 AS CT, ProjName AS Project, Phase, Protocol,
fields, ProjectName AS [Study Name],
CONVERT(char(10), POD_Op, 101) AS [POD],POD_Note AS [Comment]
, CONVERT(char(10), Op, 101) AS [IND],Note AS [NComment]
, CONVERT(char(10), POp, 101) AS [Approved],
dbo.vwMSP_KPI_Singl_Sorted.PrtclA_Note AS [Protocol
FROM Fields LEFT OUTER JOIN PKSuppl2 ON
PROJ_ID = ProjectUniqueID LEFT OUTER JOIN
OutlineCodes ON Fields.PROJ_ID = PROJ_ID LEFT OUTER JOIN
Sorted ON dbo.vwMSP_Project_Fields.PROJ_ID = ProjectUniqueID
[b]Where Id in (Select SelectValue From @ListTbl)[/b]
-- Something like this might work
If all you are doing is burying the VIEW in the SPROC you might not see any gain. Sometimes the best gain can be achieved by breaking down the VIEW and it's JOINS into parts - building TEMP TABLES first - and then constructing from the final product.
One way to see where the VIEW was having problems is to go into QUERY ANALYZER - select from the VIEW - and then look at the execution plan tab and it will tell you where the big I/O bottleneck is.