how to use function column in sproc column

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)

how to use function column in sproc column

Author

Topic

khusiie
Yak Posting Veteran

78 Posts

Posted - 2008-02-13 : 09:20:17

i have select query using function like this:

SELECT 1 as section_id, 'Schools / Child Care Centers' as categories,
sku, topic, format
from dbo.DataSet_iv00101_sy01200_funcDeeRick('')
where Topic in ('LUNG-ASCPG','LUNG-ASTHM')

and store procedure like this:

CREATE PROCEDURE MYPAGESECDEFtest

AS

BEGIN

SET NOCOUNT ON

declare @a int

set @a = (select len(code) from mypagesectiondefinition where code_type='categories' and cell=0)

SELECT distinct

s.section_id, s.cell,

case when cell = 0 then left(cl.categorylist, @a)
when cell = 1 then right(cl.categorylist, len(cl.categorylist)- @a)
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 s.code as [text()]
from mypagesectiondefinition s
where Section_ID=s.Section_ID
and code_type='categories'
for xml path('') ) cl(categorylist)

cross apply (select distinct s.code + ',' as [text()]
from mypagesectiondefinition s
where Section_ID=s.Section_ID
and code_type='topic'
for xml path('') ) tl(topiclist)

cross apply (select distinct s.code + ',' as [text()]
from mypagesectiondefinition s
where Section_ID=s.Section_ID
and code_type='format'
for xml path('') ) fl(formatlist)

cross apply (select distinct s.code + ',' as [text()]
from mypagesectiondefinition s
where Section_ID=s.Section_ID
and code_type='audience'
for xml path('') ) al(audiencelist)

cross apply (select distinct s.code + ',' as [text()]
from mypagesectiondefinition s
where Section_ID=s.Section_ID
and code_type='source'
for xml path('') ) sl(sourcelist)

cross apply (select distinct s.code + ',' as [text()]
from mypagesectiondefinition s
where Section_ID=s.Section_ID
and code_type='sourcenot'
for xml path('') ) snl(sourcenotlist)

cross apply (select distinct s.code + ',' as [text()]
from mypagesectiondefinition s
where Section_ID=s.Section_ID
and code_type='sectiontype'
for xml path('') ) stl(sectiontypelist)

end

now i have to use column name sku from that select query...in sproc to get output.. but i don't know how to join function in this sproc..

can anybody help me?

thanks.

khusiie
Yak Posting Veteran

78 Posts

Posted - 2008-02-13 : 09:30:53

CREATE PROCEDURE MYPAGESECDEFtest

AS

BEGIN

SET NOCOUNT ON

declare @a int

set @a = (select len(code) from mypagesectiondefinition where code_type='categories' and cell=0)

SELECT distinct

s.section_id, s.cell,

case when cell = 0 then left(cl.categorylist, @a)
when cell = 1 then right(cl.categorylist, len(cl.categorylist)- @a)
end as categories,

case when cell = 0 then '' else left(skul.skulist, len(skul.skulist)-1) end as sku,

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 s.code as [text()]
from mypagesectiondefinition s
where Section_ID=s.Section_ID
and code_type='categories'
for xml path('') ) cl(categorylist)

cross apply (select distinct dt.sku + ',' as [text()]
from DataSet_iv00101_sy01200_funcDeeRick('') dt
where Section_ID=s.Section_ID
and code_type='categories'
for xml path('') ) skul(skulist)

cross apply (select distinct s.code + ',' as [text()]
from mypagesectiondefinition s
where Section_ID=s.Section_ID
and code_type='topic'
for xml path('') ) tl(topiclist)

cross apply (select distinct s.code + ',' as [text()]
from mypagesectiondefinition s
where Section_ID=s.Section_ID
and code_type='format'
for xml path('') ) fl(formatlist)

cross apply (select distinct s.code + ',' as [text()]
from mypagesectiondefinition s
where Section_ID=s.Section_ID
and code_type='audience'
for xml path('') ) al(audiencelist)

cross apply (select distinct s.code + ',' as [text()]
from mypagesectiondefinition s
where Section_ID=s.Section_ID
and code_type='source'
for xml path('') ) sl(sourcelist)

cross apply (select distinct s.code + ',' as [text()]
from mypagesectiondefinition s
where Section_ID=s.Section_ID
and code_type='sourcenot'
for xml path('') ) snl(sourcenotlist)

cross apply (select distinct s.code + ',' as [text()]
from mypagesectiondefinition s
where Section_ID=s.Section_ID
and code_type='sectiontype'
for xml path('') ) stl(sectiontypelist)

end

i try like this...i m getting results but not correct..i m getting 3rows instead of 2.and also last 2rows r same..means if cell=o then sku'' that row result is getting correct but when cel==1 then sku=..that row result getting twice..one as correct result and other as sku=null..can anyone help me to get correct result..

thanks

khusiie
Yak Posting Veteran

78 Posts

Posted - 2008-02-13 : 09:39:03

CREATE PROCEDURE MYPAGESECDEFtest

AS

BEGIN

SET NOCOUNT ON

declare @a int

set @a = (select len(code) from mypagesectiondefinition where code_type='categories' and cell=0)

SELECT distinct

s.section_id, s.cell,

case when cell = 0 then left(cl.categorylist, @a)
when cell = 1 then right(cl.categorylist, len(cl.categorylist)- @a)
end as categories,

case when cell = 0 then ''
else left(skul.skulist, len(skul.skulist)-1) end as sku,

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 s.code as [text()]
from mypagesectiondefinition s
where Section_ID=s.Section_ID
and code_type='categories'
for xml path('') ) cl(categorylist)

cross apply (select distinct dt.sku + ',' as [text()]
from DataSet_iv00101_sy01200_funcDeeRick('') dt
where Section_ID=s.Section_ID
and code_type='categories' and dt.sku is not null
for xml path('') ) skul(skulist)

cross apply (select distinct s.code + ',' as [text()]
from mypagesectiondefinition s
where Section_ID=s.Section_ID
and code_type='topic'
for xml path('') ) tl(topiclist)

cross apply (select distinct s.code + ',' as [text()]
from mypagesectiondefinition s
where Section_ID=s.Section_ID
and code_type='format'
for xml path('') ) fl(formatlist)

cross apply (select distinct s.code + ',' as [text()]
from mypagesectiondefinition s
where Section_ID=s.Section_ID
and code_type='audience'
for xml path('') ) al(audiencelist)

cross apply (select distinct s.code + ',' as [text()]
from mypagesectiondefinition s
where Section_ID=s.Section_ID
and code_type='source'
for xml path('') ) sl(sourcelist)

cross apply (select distinct s.code + ',' as [text()]
from mypagesectiondefinition s
where Section_ID=s.Section_ID
and code_type='sourcenot'
for xml path('') ) snl(sourcenotlist)

cross apply (select distinct s.code + ',' as [text()]
from mypagesectiondefinition s
where Section_ID=s.Section_ID
and code_type='sectiontype'
for xml path('') ) stl(sectiontypelist)

end

i add this condition and dt.sku is not null still i m getting null row result.

can anyone hel pme to figure out this solution?

thanks.

khusiie
Yak Posting Veteran

78 Posts

Posted - 2008-02-13 : 09:50:05

SELECT 1 as indx, 'Schools / Child Care Centers' as dTitle,title,
sku, ltrim(rtrim(topic)) as topic, ltrim(rtrim(format)) as format, ltrim(rtrim(source)) as source,
ltrim(rtrim(Audience)) as Audience, ltrim(rtrim(pub_name )) as pub_name, ltrim(rtrim(abbreviated_name)) as abbreviated_name,
ltrim(rtrim(itemtype)) as itemtype, ltrim(rtrim(view_type)) as view_type, ltrim(rtrim(pdf_url)) as pdf_url ,
ltrim(rtrim(html_url))as html_url , 'pubs' as SectionType, qtyavail, uomprice
from dbo.DataSet_iv00101_sy01200_funcDeeRick('') A LEFT OUTER JOIN (SELECT USCATVAL, IMAGE_URL FROM IV40600 WHERE USERCATLONGDESCR = 'PROGRAM' ) DT ON A.TOPIC = DT.USCATVAL 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) order by dTitle, pub_name

with this select statement..i m getting 9rows - output...so 9 sku column values...and

with that sproc i m getting 456 sku column values..i want only 9 sku values..can anybody plz help me?

thanks..

khusiie
Yak Posting Veteran

78 Posts

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

CREATE PROCEDURE MYPAGESECDEFtest

AS

BEGIN

SET NOCOUNT ON

declare @a int

set @a = (select len(code) from mypagesectiondefinition where code_type='categories' and cell=0)

SELECT distinct

s.section_id, s.cell,

case when cell = 0 then left(cl.categorylist, @a)
when cell = 1 then right(cl.categorylist, len(cl.categorylist)- @a)
end as categories,

case when cell = 0 then ''
else left(skul.skulist, len(skul.skulist)-1) end as sku,

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 s.code as [text()]
from mypagesectiondefinition s
where Section_ID=s.Section_ID
and code_type='categories'
for xml path('')) cl(categorylist)

cross apply (select distinct dt.sku + ',' as [text()]
from DataSet_iv00101_sy01200_funcDeeRick('') dt
where Section_ID=s.Section_ID
and dt.sku is not null
for xml path('')) skul(skulist)

cross apply (select distinct s.code + ',' as [text()]
from mypagesectiondefinition s
where Section_ID=s.Section_ID
and code_type = 'topic'
for xml path('') ) tl(topiclist)

cross apply (select distinct s.code + ',' as [text()]
from mypagesectiondefinition s
where Section_ID=s.Section_ID
and code_type='format'
for xml path('') ) fl(formatlist)

cross apply (select distinct s.code + ',' as [text()]
from mypagesectiondefinition s
where Section_ID=s.Section_ID
and code_type='audience'
for xml path('') ) al(audiencelist)

cross apply (select distinct s.code + ',' as [text()]
from mypagesectiondefinition s
where Section_ID=s.Section_ID
and code_type='source'
for xml path('') ) sl(sourcelist)

cross apply (select distinct s.code + ',' as [text()]
from mypagesectiondefinition s
where Section_ID=s.Section_ID
and code_type='sourcenot'
for xml path('') ) snl(sourcenotlist)

cross apply (select distinct s.code + ',' as [text()]
from mypagesectiondefinition s
where Section_ID=s.Section_ID
and code_type='sectiontype'
for xml path('') ) stl(sectiontypelist)

end

now i m getting only 2rows..that's correct but sku values r not getting correct..

i want in sku -
05-3651
56-034SP
56-034N
55-830
03-NAEPPRESMGTASTHMA
05-SCHOOLHEALTHINDEX
00-STUDENTSCHRONICILLNESS
06-EMERGPROTASTHM
04-ADDRESNAEPPORG

but i m getting all values...i think i ahve to use one condition in where clause..but don't know what?

can anybody suggest me plz?

thanks.

khusiie
Yak Posting Veteran

78 Posts

Posted - 2008-02-13 : 12:34:44

CREATE PROCEDURE MYPAGESECDEFtest

AS

BEGIN

SET NOCOUNT ON

declare @a int

set @a = (select len(sku) from DataSet_iv00101_sy01200_funcDeeRick(''))

SELECT distinct

s.section_id, s.cell,

case when cell = 0 then left(skul.skulist, @a)
when cell = 1 then right(skul.skulist, len(skul.skulist)- @a)
end as sku

FROM myPageSectionDefinition S

cross apply (select distinct dt.sku as [text()]
from DataSet_iv00101_sy01200_funcDeeRick('') dt
where Section_ID=s.Section_ID
and dt.sku is not null
for xml path('')) skul(skulist)

end

i m getting null values..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources