T-SQL Help

[kingstonr]

I want to query a table like below.
The parameter @classval will have any class value
I want to do a T-sql for that.
Can anyb ody help me out what will be efficient way to do that.
Thought of doing some function with split but it performance is slow.

declare
@classval='Test1=2;Test3=1'

class Value
--------------------
Test1 3
Test2 12
Test3 2
....
.......

select * from table where
class=Test1 and value <=2
and class=Test3 and value <=1
......
......
......

 

[iwease]

You are saying is that you want a user function that has a parameter @classval (of the form Test1=2;Test3=1') passed to it, and that you want this functionto return a table of the form

class Value
--------------------
Test1 3
Test2 12
Test3 2

right?

I would do this with a CLR function. You simply split the parameter using the String.Split functions, insert it into a data table. It would be fast than using a TSQL script, mainly because your function would be compilied.

If you are dead set on using TSQL, then you might be able to adjust this to meet your needs. It is a function that just takes some text and a delimiter as arguments, and spilts the text so that all the results are in a table

ALTER FUNCTION [dbo].[fnSplit]
(
@sInputList VARCHAR(150) -- List of delimited items
, @sDelimiter VARCHAR(5) = ',' -- delimiter that separates items
)
RETURNS
@List TABLE
(
item VARCHAR(150)
)
AS
BEGIN
DECLARE @sItem VARCHAR(8000)
WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
BEGIN
SELECT
@sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),
@sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))

IF LEN(@sItem) > 0
INSERT INTO @List SELECT @sItem
END

IF LEN(@sInputList) > 0
INSERT INTO @List SELECT @sInputList -- Put the last item in
RETURN
END