> It should return a numeric value when called from the SELECT part of a INSERT INTO... SELECT statement<

Should this value be unique? should it represent the order of inserts?

>/* My Question was is there are simpler way of doing this ? The return value  is set in the Act_ind which in turn is used again for another function*/<

I...

If it is because of 'unknown' IF-statements, do the following:

WHERE

 (@SICCode IS NULL OR @SICCode = dbo.Split(... ,CM.SICCode2))

AND (@PlantNumber IS NULL OR @PlantNumber = dbo.Split( ...,CM.Plantnumber))

AND (@ProductGroup IS NULL OR @ProductGroup...

Why do you want to have it dynamic? Why not just compile it?

>For example if i have the word "field" and "part" in 2 rows where the unique id is the same then which once should be the more important  hence the...

Oh, Carl Federl, seems like same thoughts in parallel  would not have posted otherwise.

>My third attempt was to write it all in ASP - TSQL code (which was the faster by FAR), but I get an error <

Fast error producing statements are always...

>My idea was to have a separate table containing just one column and one row with a numeric value<

No bad idea, but what is your sequence based on?

I omitted existence and null tests on purpose. This should of course be considered.

Taking the ranks out to a small persistent table will give you more control on what really...

-- simple version:

SELECT

 UniqueID,

 field1,

 field2,

 CASE field1 WHEN 'field' THEN 1

    WHEN 'solve' THEN 2

    WHEN 'part' THEN 3

    ELSE 100 END

 AS OrderCriteria1,

 CASE field2 WHEN 'field' THEN 1

    WHEN 'solve' THEN 2

    WHEN 'part' THEN 3

    ELSE 100 END

 AS...

You're the really confusing type of SQL-Cowboy

What are you trying to find out in fact? Whether any combination of specified four terms exists?

>This whole problem of getting the correct tables and stored proc has been messing me for some times . Had in many occasions tried many...

in and not in are not simply the opposite of each other in case nulls occur.

why?

because for a specific partnumber, you can tell, that its is IN a selection if...

Kenneth, why should the ##temptable go out of scope? It's still a regular table. Of course, on more # is needed

Why don't you simply select them (in Procedure A!) from the #temptable you have created? - or did you do that just for fun?