Help optmizing a stored proc

I have this

CREATE PROCEDURE dbo.cmsGetTaskOrdersAndFunding2
(
@.FundingDate SMALLDATETIME,
@.BillingContractID INT, -- null for all contracts
@.Filter BIT = NULL

)
AS
-- get list of taskorders with their respective fundingtotals as of
specified date
IF @.Filter IS NULL
BEGIN
SELECT TO1.TaskOrderID
FROM TaskOrder TO1
LEFT OUTER JOIN
WHERE (@.BillingContractID IS NULL OR TO1.BillingContractID =
@.BillingContractID)
END
ELSE
BEGIN
SELECT TO1.TaskOrderID,
FROM TaskOrder TO1
WHERE (@.BillingContractID IS NULL OR TO1.BillingContractID =
@.BillingContractID) AND TO1.Retired <> @.Filter
END

RETURN
GO
------
Is there a less redundant way to write this? basically @.Filter is an
optional parameter, if it isn't present, I want to return all records and if
it is present all records where Retired <> @.Filter. Any ideas? Can I wrap
the WHERE clause in an if statement? Or is there a better way?

TIA,
ChrisJust one block:

-- get list of taskorders with their respective fundingtotals as of
specified date
SELECT TO1.TaskOrderID,
FROM TaskOrder TO1
WHERE (@.BillingContractID IS NULL OR TO1.BillingContractID =
@.BillingContractID)
AND (@.Filter IS NULL OR TO1.Retired <> @.Filter)

Note this last line. If @.Filter is NULL, the entire block is ALWAYS
true so "TO1.Retired <> @.Filter" doesn't matter. If @.filter is not
null, "TO1.Retired <> @.Filter" is the part that matters.|||Sweet! Thanks man.

"figital" <mharen@.gmail.com> wrote in message
news:1141927222.299970.274010@.i39g2000cwa.googlegr oups.com...
> Just one block:
> -- get list of taskorders with their respective fundingtotals as of
> specified date
> SELECT TO1.TaskOrderID,
> FROM TaskOrder TO1
> WHERE (@.BillingContractID IS NULL OR TO1.BillingContractID =
> @.BillingContractID)
> AND (@.Filter IS NULL OR TO1.Retired <> @.Filter)
> Note this last line. If @.Filter is NULL, the entire block is ALWAYS
> true so "TO1.Retired <> @.Filter" doesn't matter. If @.filter is not
> null, "TO1.Retired <> @.Filter" is the part that matters.

Help on Stored Procedure

CREATE PROCEDURE CreateMenuItems

@.in_cls_id int


AS
declare ....

DECLARE cursor_name CURSOR FOR
SELECT DISTINCT column1,column1,column3 ...
FROM viewUserPrivileges
WHERE ....
ORDER BY ....

OPEN cursor_name

FETCH NEXT FROM cursor_name
INTO declared local variables

while @.@.FETCH_STATUS <> -1
begin

if <condition>
begin
Print 'null'
insert into table...
Execute CreateMenuItems @.in_cls_id
end
else
insert into table...
Print 'Not null'
FETCH NEXT FROM cls_cursor
INTO declared local variables
End
close cursor_name
deallocate cursor_name

In the above procedure CreateMenuItems
calls itself. Is this possible? If not is there any other way to achieve the same?

Thanks in advance
P.C. VaidyanathanExecute CreateMenuItems @.in_cls_idIf it called itself, you would have an endless loop.

Not a good thing - I would not do it.

J.

Help on OPENXML with NameSpaces

DECLARE @.batchXML XML
DECLARE @.docHandle INT

-- <Response xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.microsoft.com/windows/test/XMLSchema.xsd">

SET @.batchXML ='<?xml version="1.0" encoding="utf-8"?>
<Response>
<Student>
<Name>Tony</Name><Status>Success</Status>
</Student>
<Student>
<Name>Pam</Name><Status>Fail</Status>
</Student>
<Student>
<Name>Sam</Name><Status>Success</Status>
</Student>
</Response>'

EXECsp_xml_preparedocument @.docHandle OUTPUT, @.batchXML
SELECT NAME, Status FROM OPENXML(@.docHandle, N'/Response/Student')
WITH(NameNVARCHAR(25)'./Name', Status NVARCHAR(25)'./Status')
EXECsp_xml_removedocument @.docHandle

The above query returns the values correctly.

Name Status
Tony Success
Pam Fail
Sam Success

But when I use the root element along namespaces it isn't retuning any values. (commented line above)

Your help is much appreciated.

Thanks,
Loonysan

For SQL Server 2000

EXEC sp_xml_preparedocument @.docHandle OUTPUT, @.batchXML,'<Response xmlns:x="'" href="http://schemas.microsoft.com/windows/test/XMLSchema.xsd"/>'">http://schemas.microsoft.com/windows/test/XMLSchema.xsd"/>'

SELECT NAME, Status FROM OPENXML(@.docHandle, N'/x:Response/x:Student')
WITH (Name NVARCHAR(25) './x:Name', Status NVARCHAR(25) './x:Status')
EXEC sp_xml_removedocument @.docHandle

For SQL Server 2005

;
WITH XMLNAMESPACES(default 'http://schemas.microsoft.com/windows/test/XMLSchema.xsd' )
SELECT x.value('./Name[1]','NVARCHAR(25)') AS Name,
x.value('./Status[1]','NVARCHAR(25)') AS Status
FROM @.batchXML.nodes('/Response/Student') as R(x)

Help on a query

Hi,
Iam trying to figure out the query to achieve the output depicted below

create table master (
iss_dtl_seq_nbr int
)

create table child1(
iss_dtl_seq_nbr int,
line_no int
)

create table child2(
iss_dtl_seq_nbr int,
line_no int
)

insert into master
select 1

insert into child1
select 1, 1
insert into child1
select 1, 2

insert into child2
select 1, 1

insert into child2
select 1, 2

insert into child2
select 1, 3

SELECT MASTER.ISS_DTL_SEQ_NBR,CHILD1.LINE_NO, CHILD2.LINE_NO
FROM CHILD1, CHILD2, MASTER
WHERE MASTER.ISS_DTL_SEQ_NBR = CHILD1.ISS_DTL_SEQ_NBR
AND MASTER.ISS_DTL_SEQ_NBR = CHILD2.ISS_DTL_SEQ_NBR
AND CHILD1.LINE_NO = CHILD2.LINE_NO
ORDER BY CHILD1.LINE_NO, CHILD2.LINE_NO

Expected Output:

ISS_DTL_SEQ_NBR LINE_NO LINE_NO
----- ---- ----
1 1 1
1 2 2
1 NULL 3

Can anybody help with the query to achieve this?

Thanks.On 9 Sep 2004 09:33:21 -0700, Sudhir wrote:

>Hi,
> Iam trying to figure out the query to achieve the output depicted below
>create table master (
>iss_dtl_seq_nbr int
>)
>create table child1(
>iss_dtl_seq_nbr int,
>line_no int
>)
>create table child2(
>iss_dtl_seq_nbr int,
>line_no int
>)
>insert into master
>select 1
>insert into child1
>select 1, 1
>insert into child1
>select 1, 2
>insert into child2
>select 1, 1
>insert into child2
>select 1, 2
>insert into child2
>select 1, 3
>
>SELECT MASTER.ISS_DTL_SEQ_NBR,CHILD1.LINE_NO, CHILD2.LINE_NO
>FROM CHILD1, CHILD2, MASTER
>WHERE MASTER.ISS_DTL_SEQ_NBR = CHILD1.ISS_DTL_SEQ_NBR
>AND MASTER.ISS_DTL_SEQ_NBR = CHILD2.ISS_DTL_SEQ_NBR
>AND CHILD1.LINE_NO = CHILD2.LINE_NO
>ORDER BY CHILD1.LINE_NO, CHILD2.LINE_NO
>Expected Output:
>ISS_DTL_SEQ_NBR LINE_NO LINE_NO
>----- ---- ----
>1 1 1
>1 2 2
>1 NULL 3
>Can anybody help with the query to achieve this?
>Thanks.

Hi Sudhir,

Thanks for providing the statements to recreate your table structure and
data. The following query will produce the expected output:

select coalesce(master.iss_dtl_seq_nbr, child2.iss_dtl_seq_nbr) AS
iss_dtl_seq_nbr,
child1.line_no, child2.line_no
from master
inner join child1
on master.iss_dtl_seq_nbr = child1.iss_dtl_seq_nbr
right outer join child2
on child1.iss_dtl_seq_nbr = child2.iss_dtl_seq_nbr
and child1.line_no = child2.line_no
order by child2.line_no, child1.line_no

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Try this:

select master.iss_dtl_seq_nbr, child1.line_no, child2.line_no
from child1 full outer join child2 on child1.iss_dtl_seq_nbr =
child2.iss_dtl_seq_nbr and child1.line_no = child2.line_no
join master on master.iss_dtl_seq_nbr =
isnull(child1.iss_dtl_seq_nbr, child2.iss_dtl_seq_nbr)

Shervin

s.sudhir@.gmail.com (Sudhir) wrote in message news:<80a5355b.0409090833.4ccd6d2b@.posting.google.com>...
> Hi,
> Iam trying to figure out the query to achieve the output depicted below
> create table master (
> iss_dtl_seq_nbr int
> )
> create table child1(
> iss_dtl_seq_nbr int,
> line_no int
> )
> create table child2(
> iss_dtl_seq_nbr int,
> line_no int
> )
> insert into master
> select 1
> insert into child1
> select 1, 1
> insert into child1
> select 1, 2
> insert into child2
> select 1, 1
> insert into child2
> select 1, 2
> insert into child2
> select 1, 3
>
> SELECT MASTER.ISS_DTL_SEQ_NBR,CHILD1.LINE_NO, CHILD2.LINE_NO
> FROM CHILD1, CHILD2, MASTER
> WHERE MASTER.ISS_DTL_SEQ_NBR = CHILD1.ISS_DTL_SEQ_NBR
> AND MASTER.ISS_DTL_SEQ_NBR = CHILD2.ISS_DTL_SEQ_NBR
> AND CHILD1.LINE_NO = CHILD2.LINE_NO
> ORDER BY CHILD1.LINE_NO, CHILD2.LINE_NO
> Expected Output:
> ISS_DTL_SEQ_NBR LINE_NO LINE_NO
> ----- ---- ----
> 1 1 1
> 1 2 2
> 1 NULL 3
> Can anybody help with the query to achieve this?
> Thanks.

Help needed with complex query

Hi,

I have a sql table, over 30 milion recs, with the following fields:
(id1 int, id2 int, itemsCollection varchar(100), myText TEXT)
I have also sql table, with the following fields: (item varchar(10), rate int)

I need to write a query that returns the following info: id1, id2, itemsCollection, item, rate, myText
The output need to be ordered as:
- Get id1 & id2 with the bigest rate
- output all the recs for the id1 & 2, ordered by rate (sub order)
eg
Main table:
id1 id2 itemsCollection myText
1 1 'a,b' 'count-11-a,B - max = 15 additional txt'
1 1 'a,b' 'count-11-a-B - max = 15'
1 1 '' 'count-11'
1 1 'a,c' 'count-11-a,C - max = 20'
2 8 'c,d' 'count-28-C-d - max = 20 additional txt'
2 8 'c,d' 'count-28-C-d - max = 20'
2 8 'd' 'count-28-D - max = 5'
3 2 'a,d' 'count-32-A-d - max = 10'
3 2 '' 'count-32'

Rates table:
item rate
a 10
b 15
c 20
d 5
'' 0

RequestedOutput:
itemsCollection item rate id1 id2 myText
a,c c 20 1 1 count-11-a,C - max = 20
a,b b 15 1 1 count-11-a,B - max = 15 additional txy
a,b b 15 1 1 count-11-a,B - max = 15
0 1 1 count-11
c,d c 20 2 8 count-28-C-d - max = 20 additional txt
c,d c 20 2 8 count-28-C-d - max = 20
d d 5 2 8 count-28-D - max = 5
a,d a 10 3 2 count-32-A-d - max = 10
0 3 2 count-32
Thanks!you need an application program for that logic

you can sort the results of a join (and in your case the join will be monstrously inefficient, because of the comma-separated list of ids) by descending order of rate, but you can not "take a side trip" and sort all the a/b rows up under the a/b row with the largest rate|||r937, thanks for reply.
No app available - I'm trying to do it using cursor & temp tables, because

Thanks anyway|||okay, then transact-sql is your application programming language

good luck

HELP ME...........

It my ddl table:
CREATE TABLE [dbo].[TTEMP_BC] (
[RecID] [int] IDENTITY (1, 1) Primary Key,
[FDATE] [smalldatetime] NULL ,
[FTIME] [smalldatetime] NULL ,
[NOID] [nvarchar] (6) COLLATE Latin1_General_CI_AS NULL ,
[FSTATUS] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL
)
and my data like it:
noid fdate ftime fstatus
---
1 1/1/2005 1/1/2005 6:30:00 1
1 1/1/2005 1/1/2005 6:30:00 1
1 1/1/2005 1/1/2005 6:31:00 1
1 1/1/2005 1/1/2005 16:30:00 1
1 1/1/2005 1/1/2005 16:30:00 0
1 1/1/2005 1/1/2005 16:33:00 0
1 1/1/2005 1/1/2005 16:33:00 0
2 1/1/2005 1/1/2005 6:27:00 1
2 1/1/2005 1/1/2005 6:28:00 1
2 1/1/2005 1/1/2005 6:32:00 1
2 1/1/2005 1/1/2005 16:30:00 0
2 1/1/2005 1/1/2005 16:31:00 0
2 1/1/2005 1/1/2005 16:45:00 0
2 1/1/2005 1/1/2005 16:45:00 0
I want to delete. if fstatus =1 so fisrt record (min(Ftime)) of group
noid,fdate is not deleted. but if fstatus =0 so last record (max(Ftime)) of
group noid,fdate is not deleted. So its data will be;
noid fdate ftime fstatus
---
1 1/1/2005 1/1/2005 6:30:00 1
1 1/1/2005 1/1/2005 16:33:00 0
2 1/1/2005 1/1/2005 6:27:00 1
2 1/1/2005 1/1/2005 16:45:00 0
Can u help me? How sintax sql? Can it be solved with one statement?Hi
I think Steve Kass has already provided solution for you .
Would you mind to post a sample data when you ask for help or at least to
fix your current DDL
This is one of the many options that others provided
CREATE TABLE [dbo].[TTEMP_BC] (
[RecID] [int] IDENTITY (1, 1) Primary Key,
[NOID] [INT],
[FDATE] [smalldatetime] NULL ,
[FTIME] [smalldatetime] NULL ,
[FSTATUS] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL
)
INSERT INTO [dbo].[TTEMP_BC] VALUES (1,' 1/1/2005','1/1/2005 06:30:00',1)
INSERT INTO [dbo].[TTEMP_BC] VALUES (1,' 1/1/2005','1/1/2005 06:30:00',1)
INSERT INTO [dbo].[TTEMP_BC] VALUES (1,' 1/1/2005','1/1/2005 06:31:00',1)
INSERT INTO [dbo].[TTEMP_BC] VALUES (1,' 1/1/2005','1/1/2005 16:30:00',1)
INSERT INTO [dbo].[TTEMP_BC] VALUES (1,' 1/1/2005','1/1/2005 16:30:00',0)
INSERT INTO [dbo].[TTEMP_BC] VALUES (1,' 1/1/2005','1/1/2005 16:33:00',0)
INSERT INTO [dbo].[TTEMP_BC] VALUES (1,' 1/1/2005','1/1/2005 16:33:00',0)
INSERT INTO [dbo].[TTEMP_BC] VALUES (2,' 1/1/2005','1/1/2005 06:27:00',1)
INSERT INTO [dbo].[TTEMP_BC] VALUES (2,' 1/1/2005','1/1/2005 06:28:00',1)
INSERT INTO [dbo].[TTEMP_BC] VALUES (2,' 1/1/2005','1/1/2005 06:32:00',1)
INSERT INTO [dbo].[TTEMP_BC] VALUES (2,' 1/1/2005','1/1/2005 16:30:00',0)
INSERT INTO [dbo].[TTEMP_BC] VALUES (2,' 1/1/2005','1/1/2005 16:31:00',0)
INSERT INTO [dbo].[TTEMP_BC] VALUES (2,' 1/1/2005','1/1/2005 16:45:00',0)
INSERT INTO [dbo].[TTEMP_BC] VALUES (2,' 1/1/2005','1/1/2005 16:45:00',0)
SELECT * FROM
(
SELECT noid,MIN(ftime)as ftime
FROM TTEMP_BC WHERE fstatus=1
GROUP BY noid
UNION ALL
SELECT noid,MIN(ftime)as ftime
FROM TTEMP_BC WHERE fstatus=0
GROUP BY noid
) AS Der
ORDER BY noid
"Bpk. Adi Wira Kusuma" <adi_wira_kusuma@.yahoo.com.sg> wrote in message
news:uK2KgVQkFHA.3336@.tk2msftngp13.phx.gbl...
> It my ddl table:
> CREATE TABLE [dbo].[TTEMP_BC] (
> [RecID] [int] IDENTITY (1, 1) Primary Key,
> [FDATE] [smalldatetime] NULL ,
> [FTIME] [smalldatetime] NULL ,
> [NOID] [nvarchar] (6) COLLATE Latin1_General_CI_AS NULL ,
> [FSTATUS] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL
> )
> and my data like it:
> noid fdate ftime fstatus
> ---
> 1 1/1/2005 1/1/2005 6:30:00 1
> 1 1/1/2005 1/1/2005 6:30:00 1
> 1 1/1/2005 1/1/2005 6:31:00 1
> 1 1/1/2005 1/1/2005 16:30:00 1
> 1 1/1/2005 1/1/2005 16:30:00 0
> 1 1/1/2005 1/1/2005 16:33:00 0
> 1 1/1/2005 1/1/2005 16:33:00 0
> 2 1/1/2005 1/1/2005 6:27:00 1
> 2 1/1/2005 1/1/2005 6:28:00 1
> 2 1/1/2005 1/1/2005 6:32:00 1
> 2 1/1/2005 1/1/2005 16:30:00 0
> 2 1/1/2005 1/1/2005 16:31:00 0
> 2 1/1/2005 1/1/2005 16:45:00 0
> 2 1/1/2005 1/1/2005 16:45:00 0
> I want to delete. if fstatus =1 so fisrt record (min(Ftime)) of group
> noid,fdate is not deleted. but if fstatus =0 so last record (max(Ftime))
of
> group noid,fdate is not deleted. So its data will be;
> noid fdate ftime fstatus
> ---
> 1 1/1/2005 1/1/2005 6:30:00 1
> 1 1/1/2005 1/1/2005 16:33:00 0
> 2 1/1/2005 1/1/2005 6:27:00 1
> 2 1/1/2005 1/1/2005 16:45:00 0
> Can u help me? How sintax sql? Can it be solved with one statement?
>
>
>|||Try,
delete t1
where
recid !=
case
when fstatus = 1 then (select min(a.recid) from t1 as a where a.fstatus = 1
and a.noid = t1.noid and a.fdate = t1.fdate and a.ftime = (select
min(b.ftime) from t1 as b where b.fstatus = 1 and b.noid = t1.noid and
b.fdate = t1.fdate))
when fstatus = 0 then (select max(a.recid) from t1 as a where a.fstatus = 0
and a.noid = t1.noid and a.fdate = t1.fdate and a.ftime = (select
max(b.ftime) from t1 as b where b.fstatus = 0 and b.noid = t1.noid and
b.fdate = t1.fdate))
end
go
AMB
"Bpk. Adi Wira Kusuma" wrote:

> It my ddl table:
> CREATE TABLE [dbo].[TTEMP_BC] (
> [RecID] [int] IDENTITY (1, 1) Primary Key,
> [FDATE] [smalldatetime] NULL ,
> [FTIME] [smalldatetime] NULL ,
> [NOID] [nvarchar] (6) COLLATE Latin1_General_CI_AS NULL ,
> [FSTATUS] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL
> )
> and my data like it:
> noid fdate ftime fstatus
> ---
> 1 1/1/2005 1/1/2005 6:30:00 1
> 1 1/1/2005 1/1/2005 6:30:00 1
> 1 1/1/2005 1/1/2005 6:31:00 1
> 1 1/1/2005 1/1/2005 16:30:00 1
> 1 1/1/2005 1/1/2005 16:30:00 0
> 1 1/1/2005 1/1/2005 16:33:00 0
> 1 1/1/2005 1/1/2005 16:33:00 0
> 2 1/1/2005 1/1/2005 6:27:00 1
> 2 1/1/2005 1/1/2005 6:28:00 1
> 2 1/1/2005 1/1/2005 6:32:00 1
> 2 1/1/2005 1/1/2005 16:30:00 0
> 2 1/1/2005 1/1/2005 16:31:00 0
> 2 1/1/2005 1/1/2005 16:45:00 0
> 2 1/1/2005 1/1/2005 16:45:00 0
> I want to delete. if fstatus =1 so fisrt record (min(Ftime)) of group
> noid,fdate is not deleted. but if fstatus =0 so last record (max(Ftime)) o
f
> group noid,fdate is not deleted. So its data will be;
> noid fdate ftime fstatus
> ---
> 1 1/1/2005 1/1/2005 6:30:00 1
> 1 1/1/2005 1/1/2005 16:33:00 0
> 2 1/1/2005 1/1/2005 6:27:00 1
> 2 1/1/2005 1/1/2005 16:45:00 0
> Can u help me? How sintax sql? Can it be solved with one statement?
>
>
>

help me out for a simple query

I am sending a small scenario
i have a table
CREATE TABLE STUDENT_ANSWERS(EXAMID INT, MARKS INT);
INSERT INTO VALUES (1, 20)
INSERT INTO VALUES (1, 10)
INSERT INTO VALUES (1, 30)
INSERT INTO VALUES (1, 50)
INSERT INTO VALUES (2, 50)
INSERT INTO VALUES (2, 70)
INSERT INTO VALUES (2, 20)
INSERT INTO VALUES (2, 40)
INSERT INTO VALUES (2, 90)
i need the output like
examid marksstring
----
1 20,10,30,50
2 50,70,20,40,90
----
thx for ur help
*** Sent via Developersdex http://www.examnotes.net ***Here's one way to achieve this using a function:
CREATE FUNCTION dbo.fn_ConcatMarks(@.id INT) RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @.marks VARCHAR(8000)
SET @.marks = ''
SELECT @.marks = @.marks + CAST(MARKS AS VARCHAR(10)) + ',' FROM
STUDENT_ANSWERS
WHERE EXAMID = @.id
RETURN LEFT(@.marks, LEN(@.marks) - 1)
END
GO
SELECT EXAMID, dbo.fn_ConcatMarks(EXAMID) AS marks
FROM STUDENT_ANSWERS
GROUP BY EXAMID
EXAMID marks
-- --
1 20,10,30,50
2 50,70,20,40,90
You can find several other solutions in previous threads if you look for the
keywords PIVOT, crosstab.
BG, SQL Server MVP
www.SolidQualityLearning.com
"kamal hussain" <skkamalh@.rediffmail.com> wrote in message
news:OzYlXrmRFHA.1208@.TK2MSFTNGP10.phx.gbl...
>
> I am sending a small scenario
> i have a table
> CREATE TABLE STUDENT_ANSWERS(EXAMID INT, MARKS INT);
> INSERT INTO VALUES (1, 20)
> INSERT INTO VALUES (1, 10)
> INSERT INTO VALUES (1, 30)
> INSERT INTO VALUES (1, 50)
> INSERT INTO VALUES (2, 50)
> INSERT INTO VALUES (2, 70)
> INSERT INTO VALUES (2, 20)
> INSERT INTO VALUES (2, 40)
> INSERT INTO VALUES (2, 90)
>
>
> i need the output like
> examid marksstring
> ----
> 1 20,10,30,50
> 2 50,70,20,40,90
> ----
>
> thx for ur help
>
>
> *** Sent via Developersdex http://www.examnotes.net ***