August 27, 2009 at 6:54 pm
Hi Guys
REQUIREMENT:
we have to compare the selected values and display filtered data to the client depending on the selected parameters.
Example: if @arStatus has multiple selections example current and expired, then the intended output is to retrieve all the employees who have status of either current/expired.
have to construct
... in ('expired','current').
I have tried all the methods using XQuery and as mentioned in the articles by Jacob Sebastian and had no luck doing so..
Any help here is most appreciated!!
This is the code:
DECLARE @types varchar(300)
DECLARE @tblstats TABLE(Statustype varchar(50), Keep_status_1 bit)
IF @arStatus IS NOT NULL
BEGIN
EXEC sp_xml_preparedocument @status OUTPUT, @arStats
DECLARE @item varchar(50)
INSERT INTO @tblstats
SELECT item, k
FROM OPENXML(@status, N'/r/i', 1)
with (item varchar(50),k bit)
EXEC sp_xml_removedocument @status
END
DECLARE @stats varchar(50)
DECLARE status_cursor CURSOR FOR
SELECT Statustype
FROM @tblStatus
OPEN status_cursor
FETCH NEXT FROM status_cursor INTO @stats
WHILE @@FETCH_STATUS = 0
BEGIN
SET @types = COALESCE(@types + ',',SPACE(1))+ @stats
FETCH NEXT FROM status_cursor INTO @stats
END
CLOSE status_cursor
DEALLOCATE status_cursor
SELECT @types;
SELECT LEFT(@types,CASE WHEN CHARINDEX(',',@types)>0 THEN LEN(@types)-1 ELSE LEN(@types) END)
I am finding trouble to extract each value and append to a string to make a query to replicate
To display Selected statustypes related data in table
Input is from .NET Application as a Generic List of Strings--
Example Data:
Status = Current (or) Expired (or) Completed (or) Withdrawn etc.
August 30, 2009 at 9:55 pm
Can we have some sample data and can you tell us exactly what errors you are experiencing?
If you read this article and follow the suggestions it outlines for submitting questions, you will get an answer much quicker, and make friends among the volunteers:
Forum Etiquette: How to post data/code on a forum to get the best help[/url]
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 30, 2009 at 11:24 pm
we have to compare the selected values and display filtered data to the client depending on the selected parameters.
Example: if @arStatus has multiple selections example current and expired, then the intended output is to retrieve all the employees who have status of either current/expired.
have to construct
... in ('expired','current').
I have tried all the methods using XQuery and as mentioned in the articles by Jacob Sebastian and had no luck doing so..
Any help here is most appreciated!!
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply