query in sproc - 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)

query in sproc

Author

Topic

khusiie
Yak Posting Veteran

78 Posts

Posted - 2008-02-13 : 13:31:34

CREATE PROCEDURE MYPAGESECDEFtest4

AS

BEGIN

SET NOCOUNT ON

SELECT distinct

s.section_id, s.cell,

case when cell in (0,1) then left(cl.categorylist, len(cl.categorylist)-1) end as categories

FROM myPageSectionDefinition S

cross apply (select distinct s.code + ' ' as [text()]
from mypagesectiondefinition s
where Section_ID=s.Section_ID
and code_type='categories'
for xml path('')) cl(categorylist)

end

i m getting results:

section_id cell categories
----------- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 0 COPD: Learn More, Breathe Better,NETT Clinical Trial,none,Schools / Child Care Centers
1 1 COPD: Learn More, Breathe Better,NETT Clinical Trial,none,Schools / Child Care Centers
2 0 COPD: Learn More, Breathe Better,NETT Clinical Trial,none,Schools / Child Care Centers
2 1 COPD: Learn More, Breathe Better,NETT Clinical Trial,none,Schools / Child Care Centers
3 0 COPD: Learn More, Breathe Better,NETT Clinical Trial,none,Schools / Child Care Centers
3 1 COPD: Learn More, Breathe Better,NETT Clinical Trial,none,Schools / Child Care Centers
4 0 COPD: Learn More, Breathe Better,NETT Clinical Trial,none,Schools / Child Care Centers
5 0 COPD: Learn More, Breathe Better,NETT Clinical Trial,none,Schools / Child Care Centers

but i want results like:

select distinct s.code + ' ' as [text()]
from mypagesectiondefinition s
where Section_ID=s.Section_ID
and code_type='categories' and cell=1

then output is:
text()
----------------------------------------------------------------
COPD: Learn More, Breathe Better
NETT Clinical Trial
Schools / Child Care Centers

(3 row(s) affected)

and when

select distinct s.code + ' ' as [text()]
from mypagesectiondefinition s
where Section_ID=s.Section_ID
and code_type='categories' and cell=0

then output is:
text()
----------------------------------------------------------------

none

(2 row(s) affected)

so i want output related like this in sproc.

can anybody help me plzzzzzzz

thanks.

khusiie
Yak Posting Veteran

78 Posts

Posted - 2008-02-13 : 13:49:46

i want output like this:

section_id cell categories
----------------------
1 0
1 0 none
1 1 COPD: Learn More, Breathe Better
1 1 NETT Clinical Trial
1 1 Schools / Child Care Centers

can anybody help me?

khusiie
Yak Posting Veteran

78 Posts

Posted - 2008-02-13 : 13:56:41

CREATE PROCEDURE MYPAGESECDEFtest4

AS

BEGIN

SET NOCOUNT ON

SELECT distinct

s.section_id, s.cell,

case when cell in (0,1) and code_type='categories' then s.code

end as categories

FROM myPageSectionDefinition S
where Section_ID=s.Section_ID

end

then i m getting output:

section_id cell categories
----------- ----------- -------------------------------
1 0 NULL
1 0 none
1 1 NULL
1 1 Schools / Child Care Centers
2 0 NULL
2 0 none
2 1 NULL
2 1 NETT Clinical Trial
3 0 NULL
3 0 none
3 1 NULL
3 1 COPD: Learn More, Breathe Better
4 0 NULL
4 0
4 0 none
5 0 NULL
5 0
5 0 none

talleyrand
Starting Member

35 Posts

Posted - 2008-02-13 : 18:31:36

Does this work at all?


SELECT
    S.section_id
,   S.cell
,   S.code AS categories
FROM
    myPageSectionDefinition S 
WHERE
    S.code_type = 'categories'
    AND S.cell = 1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-13 : 23:33:27

[code]can you change like this & try:-

CREATE PROCEDURE MYPAGESECDEFtest4

AS

BEGIN

SET NOCOUNT ON

SELECT distinct

s.section_id, s.cell,

case when cell in (0,1) then left(cl.categorylist, len(cl.categorylist)-1) end as categories

FROM myPageSectionDefinition S

cross apply (select distinct s.code + ' ' as [text()]
from mypagesectiondefinition s
where Section_ID=s.Section_ID
and cell=s.Cell
and code_type='categories'
for xml path('')) cl(categorylist)

end[/code]

khusiie
Yak Posting Veteran

78 Posts

Posted - 2008-02-14 : 09:54:05

thanks for ur help..

i changed cell=s.cell but the output remains same..

but i changed like this:

CREATE PROCEDURE MYPAGESECDEFtestfor

AS

BEGIN

SET NOCOUNT ON

SELECT distinct

s.section_id, s.cell,

case when cell = 0 and code_type='categories' then s.code
when cell = 1 and code_type='categories' then s.code
end as categories

FROM myPageSectionDefinition S

right outer join DataSet_iv00101_sy01200_funcDeeRick('') dt
on section_id=s.section_id
where s.code_type='categories'
and dt.sku is not null

end

i m getting 3rows results correct:
section_id cell categories
----------- ----------- ---------------------------------------
1 0 none
1 1 Schools / Child Care Centers
2 0 none
2 1 NETT Clinical Trial
3 0 none
3 1 COPD: Learn More, Breathe Better
4 0
4 0 none
5 0
5 0 none

10 rows affected (2of each when cell=o and cell=1)

but i want 4th row of sku which is in function and when i m selecting that row i m getting categories null...

CREATE PROCEDURE MYPAGESECDEFtestfor

AS

BEGIN

SET NOCOUNT ON

SELECT distinct

s.section_id, s.cell,

case when cell = 0 and code_type='categories' then s.code
when cell = 1 and code_type='categories' then s.code
end as categories,

case when cell = 0 then dt.sku
when cell = 1 then dt.sku
end as sku

FROM myPageSectionDefinition S

right outer join DataSet_iv00101_sy01200_funcDeeRick('') dt
on section_id=s.section_id
where section_id=s.section_id
and dt.sku is not null

end

can u help me to get correct results of sku and categories

khusiie
Yak Posting Veteran

78 Posts

Posted - 2008-02-14 : 14:12:37

i createad view:

CREATE view MYPAGESECDEFtest_Viewtt
as SELECT distinct s.Section_ID,
s.cell,
case when (cell in (0,1) and s.code_type = 'categories') and s.code is not null then s.code
end as categories,
case when cell in (0,1) then left (tl.topiclist , len( tl.topiclist )-1) end as topics,
case when cell in (0,1) then left (fl.formatlist , len( fl.formatlist )-1) end as format,
case when cell in (0,1) then left (al.audiencelist , len( al.audiencelist)-1) end as audience,
case when cell = 0 then ''
else right(sl.sourcelist , len( sl.sourcelist)-1) end as source,
case when cell = 0 then right(snl.sourcenotlist, len(snl.sourcenotlist)-1)
else '' end as sourcenot,
case when cell in (0,1) then left (stl.sectiontypelist,len(stl.sectiontypelist)-1) end as sectiontype
FROM myPageSectionDefinition S

cross apply (select distinct rtrim(ltrim(x.code)) + ',' as [text()]
from mypagesectiondefinition x where x.Section_ID = s.Section_ID and x.code_type = 'topic'
for xml path('') ) tl(topiclist)
cross apply (select distinct rtrim(ltrim(x.code)) + ',' as [text()]
from mypagesectiondefinition x where x.Section_ID = s.Section_ID and x.code_type = 'format'
for xml path('') ) fl(formatlist)
cross apply (select distinct rtrim(ltrim(x.code)) + ',' as [text()]
from mypagesectiondefinition x where x.Section_ID = s.Section_ID and x.code_type = 'audience'
for xml path('') ) al(audiencelist)
cross apply (select distinct rtrim(ltrim(x.code)) + ',' as [text()]
from mypagesectiondefinition x where x.Section_ID = s.Section_ID and x.code_type = 'source'
for xml path('') ) sl(sourcelist)
cross apply (select distinct rtrim(ltrim(x.code)) + ',' as [text()]
from mypagesectiondefinition x where x.Section_ID = s.Section_ID and x.code_type = 'sourcenot'
for xml path('') ) snl(sourcenotlist)
cross apply (select distinct rtrim(ltrim(x.code)) + ',' as [text()]
from mypagesectiondefinition x where x.Section_ID = s.Section_ID and x.code_type = 'sectiontype'
for xml path('') ) stl(sectiontypelist)

but as a resultset;
select * from MYPAGESECDEFtest_Viewtt

it gives me :
Section_ID cell categories
----------- ----------- ---------------------------------------------------------------
1 0 NULL
1 0 none
1 1 NULL
1 1 Schools / Child Care Centers
2 0 NULL
2 0 none
2 1 NULL
2 1 NETT Clinical Trial
3 0 NULL
3 0 none
3 1 NULL
3 1 COPD: Learn More, Breathe Better
4 0 NULL
4 0
4 0 none
5 0 NULL
5 0
5 0 none

(18 row(s) affected)

i want only 10rows...not null values rows...
can anybody help me to ge that.

talleyrand
Starting Member

35 Posts

Posted - 2008-02-14 : 14:31:14

What were the results, in brief if it's long, of my suggestion? The cell = S.cell, or section_ID = S.section_ID, is doing nothing more than saying when 1 = 1 or any other axiom since you are only using one table in the query.

khusiie
Yak Posting Veteran

78 Posts

Posted - 2008-02-14 : 15:00:29

i have 2 select statements

SELECT
0 as section_id,
sku
from dbo.DataSet_iv00101_sy01200_funcDeeRick('')

where Format in ('PUBS-RECIP','pubs+other','pubs-dcifs','SLIDESHOWS','MISC','POSTER','pubs+other','pubs-dcifs','misc','video/cdro','PUBS-NHLBI','PUBS-NONNH','PUBS-OTHER')
and Topic in ('LUNG-ASCPG','LUNG-ASTHM')
and Audience in ('public', 'prof+publi')
and not Source in ('PAR/CH/TEA','LATINO/PTC','LATINOS-B','LATINOS-E','LATINOS-S','LATINS/PTC','LATINB/PTC')
and itemtype in (1,3)

SELECT
1 as section_id,
sku
from dbo.DataSet_iv00101_sy01200_funcDeeRick('')

where Format in ('PUBS-RECIP','pubs+other','pubs-dcifs','SLIDESHOWS','MISC','POSTER','pubs+other','pubs-dcifs','misc','video/cdro','PUBS-NHLBI','PUBS-NONNH','PUBS-OTHER')
and Topic in ('LUNG-ASCPG','LUNG-ASTHM')
and Audience in ('public', 'prof+publi')
and Source in ('PAR/CH/TEA','LATINO/PTC','LATINOS-B','LATINOS-E','LATINOS-S','LATINS/PTC','LATINB/PTC')
and itemtype in (1,3)

section_id sku
----------- -------------------------------
0 07-5245
0 07-5248
0 07-5251

(3 row(s) affected)

section_id sku
----------- -------------------------------
1 55-830
1 56-034SP
1 00-STUDENTSCHRONICILLNESS
1 05-3651
1 03-NAEPPRESMGTASTHMA
1 56-034N
1 04-ADDRESNAEPPORG
1 05-SCHOOLHEALTHINDEX
1 06-EMERGPROTASTHM

(9 row(s) affected)

so total is 12rows if section_id =0 or 1

but i m getting 900rows only just when secction_id=1 using sproc..
CREATE PROCEDURE MYPAGESECDEFtestfor

AS

BEGIN

SET NOCOUNT ON

SELECT distinct

s.section_id, s.cell, s.categories,
dt.sku
as sku

from DataSet_iv00101_sy01200_funcDeeRick('') dt

left outer join MYPAGESECDEFtest_View2 s

on section_id=s.section_id
where section_id=1
and dt.sku is not null
and (s.cell=0 or s.cell=1)
and categories=s.categories

can you help me to figure it out?

Thanks a lot!!

talleyrand
Starting Member

35 Posts

Posted - 2008-02-14 : 15:11:42

I could be insane but how about


SELECT 
    CASE 
        WHEN Source in ('PAR/CH/TEA','LATINO/PTC','LATINOS-B','LATINOS-E','LATINOS-S','LATINS/PTC','LATINB/PTC') 
    	    THEN 1
        ELSE
            0
    END AS section_id
,    sku 
from dbo.DataSet_iv00101_sy01200_funcDeeRick('') 
where Format in ('PUBS-RECIP','pubs+other','pubs-dcifs','SLIDESHOWS','MISC','POSTER','pubs+other','pubs-dcifs','misc','video/cdro','PUBS-NHLBI','PUBS-NONNH','PUBS-OTHER') 
and Topic in ('LUNG-ASCPG','LUNG-ASTHM')
and Audience in ('public', 'prof+publi')
and itemtype in (1,3)

Qualis
Posting Yak Master

145 Posts

Posted - 2008-02-14 : 15:31:37

You ARE insane Talley!!

khusiie
Yak Posting Veteran

78 Posts

Posted - 2008-02-14 : 15:33:13

Thanks for ur reply.

but i can't use values as datas r changing daily..so i can use only colunname..

like my function is :

CREATE function
DataSet_iv00101_sy01200_funcDeeRick
(@boType varchar(100))
returns table as
return (
select rtrim(sku) as sku,
topic,
format,
source,
Audience, format, itemtype
from DataSet_iv00101_sy01200
)

and view is:
CREATE view MYPAGESECDEFtest_View2
as SELECT distinct s.Section_ID,
s.cell,

case when cell =0 then cl.categorylist
when cell =1 then cl.categorylist
end as categories ,

case when cell in (0,1) then left (tl.topiclist , len( tl.topiclist )-1) end as topics,
case when cell in (0,1) then left (fl.formatlist , len( fl.formatlist )-1) end as format,
case when cell in (0,1) then left (al.audiencelist , len( al.audiencelist)-1) end as audience,
case when cell = 0 then ''
else right(sl.sourcelist , len( sl.sourcelist)-1) end as source,
case when cell = 0 then right(snl.sourcenotlist, len(snl.sourcenotlist)-1)
else '' end as sourcenot,
case when cell in (0,1) then left (stl.sectiontypelist,len(stl.sectiontypelist)-1) end as sectiontype
FROM myPageSectionDefinition S

cross apply (select distinct rtrim(ltrim(x.code)) as [text()]
from mypagesectiondefinition x where x.Section_ID = s.Section_ID and x.code_type = 'categories'
for xml path('') ) cl(categorylist)
cross apply (select distinct rtrim(ltrim(x.code)) + ',' as [text()]
from mypagesectiondefinition x where x.Section_ID = s.Section_ID and x.code_type = 'topic'
for xml path('') ) tl(topiclist)
cross apply (select distinct rtrim(ltrim(x.code)) + ',' as [text()]
from mypagesectiondefinition x where x.Section_ID = s.Section_ID and x.code_type = 'format'
for xml path('') ) fl(formatlist)
cross apply (select distinct rtrim(ltrim(x.code)) + ',' as [text()]
from mypagesectiondefinition x where x.Section_ID = s.Section_ID and x.code_type = 'audience'
for xml path('') ) al(audiencelist)
cross apply (select distinct rtrim(ltrim(x.code)) + ',' as [text()]
from mypagesectiondefinition x where x.Section_ID = s.Section_ID and x.code_type = 'source'
for xml path('') ) sl(sourcelist)
cross apply (select distinct rtrim(ltrim(x.code)) + ',' as [text()]
from mypagesectiondefinition x where x.Section_ID = s.Section_ID and x.code_type = 'sourcenot'
for xml path('') ) snl(sourcenotlist)
cross apply (select distinct rtrim(ltrim(x.code)) + ',' as [text()]
from mypagesectiondefinition x where x.Section_ID = s.Section_ID and x.code_type = 'sectiontype'
for xml path('') ) stl(sectiontypelist)

i have t ojoin this function and view and columns of both instead of actual value.

thanks for ur help.

kiri
Starting Member

38 Posts

Posted - 2008-02-14 : 15:49:40

try like this:

CREATE PROCEDURE MYPAGESECDEFtestfor
AS
BEGIN

SELECT distinct
s.section_id, s.cell,
case when cell = 0 then dt.sku
when cell = 1 then dt.sku
end as sku

from DataSet_iv00101_sy01200_funcDeeRick('') dt

left outer join MYPAGESECDEFtest_View2 s

on section_id=s.section_id
where section_id = s.section_id
and dt.sku is not null
and cell=s.cell
and s.source <> s.sourcenot
and s.topics=dt.topic

end

khusiie
Yak Posting Veteran

78 Posts

Posted - 2008-02-14 : 15:56:04

hey!! thank you.

i m getting results for only section_id=5...not for 1,2,3,4

anyways, i really appreciate for ur time.
thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources