Table Filtering - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Table Filtering

Author

Topic

krasnokojiy
Starting Member

18 Posts

Posted - 2008-03-02 : 05:10:13

Hi Friends, i have a question table and i want to filter this table using the following stored procedure but i cannot take a resultset ; (there is no error)

Stored Procedure is here:

CREATE PROCEDURE [dbo].[SP$AllQuestion]
@Session nchar(10),
@Class nchar(10),
@Unit nchar(10)
AS
BEGIN
SELECT L.QID, L.CLASS, L.SESSION, L.UNIT, L.CONTENT as QUESTION
FROM tblQUESTION L
WHERE @Session IS NULL OR L.SESSION = @Session)
AND (@Unit IS NULL OR L.UNIT = @Unit
AND (@Class IS NULL OR L.CLASS = @Class)
AND ((@Session IS NULL AND @Unit IS NULL ) OR (L.SESSION = @Session AND L.UNIT = @Unit))
AND ((@Session IS NULL AND @Class IS NULL ) OR (L.SESSION = @Session AND L.CLASS = @Class))
AND ((@Unit IS NULL AND @Class IS NULL ) OR (L.UNIT= @Unit AND L.CLASS = @Class))
AND (L.UNIT = @Unit AND L.SESSION = @Session AND L.CLASS = @Class)
END

i dont know what my fault is ; to your opinion what i have to do?
thanks from now on...

MC

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-02 : 09:40:34

What do you mean "take a resultset"

If the query provides no records then you may have a criteria issue.

I you change this and just run it in QA by declariing and settting th variables...does the SELECT statement produce the desired results?

You may want to change this to a Table function, where the returned object is a table of records (it would be very similar to how you have that..check books online for Table functions)

Poor planning on your part does not constitute an emergency on my part.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-03-02 : 10:09:10

Just a guess:

CREATE PROCEDURE [dbo].[SP$AllQuestion]
@Session nchar(10),
@Class nchar(10),
@Unit nchar(10)
AS
BEGIN
SELECT L.QID, L.CLASS, L.SESSION, L.UNIT, L.CONTENT as QUESTION
FROM tblQUESTION L
WHERE @Session IS NULL OR L.SESSION = @Session)
AND (@Unit IS NULL OR L.UNIT = @Unit
AND (@Class IS NULL OR L.CLASS = @Class)

AND ((@Session IS NULL AND @Unit IS NULL ) OR (L.SESSION = @Session AND L.UNIT = @Unit))
AND ((@Session IS NULL AND @Class IS NULL ) OR (L.SESSION = @Session AND L.CLASS = @Class))
AND ((@Unit IS NULL AND @Class IS NULL ) OR (L.UNIT= @Unit AND L.CLASS = @Class))
AND (L.UNIT = @Unit AND L.SESSION = @Session AND L.CLASS = @Class)
END

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources