Replace code in SP

Question

Replace code in SP

msh083

SSC Journeyman

Points: 80
More actions
April 27, 2014 at 4:27 am

#292836

Hi,
In this SP
SELECT TOP (@topval) dbo.bigtb.fire, dbo.maingroup.maingroup, dbo.bigtb.subgroup, dbo.maingroup.groupnamefa, dbo.subgroup.subgroupfa, dbo.bigtb.title1,
dbo.bigtb.tbody1, dbo.bigtb.film1, dbo.v_userpro.pageview, ISNULL(dbo.pictb.picurl1, '') AS picurl1, dbo.pictb.tooltip1, dbo.pictb.main1, dbo.bigtb.photo1,
dbo.v_userpro.pageview
FROM dbo.bigtb INNER JOIN
dbo.subgroup ON dbo.bigtb.subgroup = dbo.subgroup.subgroup INNER JOIN
dbo.maingroup ON dbo.subgroup.maingroup = dbo.maingroup.maingroup LEFT OUTER JOIN
dbo.v_userpro ON dbo.bigtb.fire = dbo.v_userpro.fire LEFT OUTER JOIN
dbo.pictb ON dbo.bigtb.fire = dbo.pictb.fire
dbo.v_userpro.pageview is A view: code:
SELECT fire, COUNT(id1) AS pageview
FROM dbo.userpro
GROUP BY fire
I do not want use view, I want use the code of view in SP,
i want replace
SELECT fire, COUNT(id1) AS pageview
FROM dbo.userpro
GROUP BY fire
to
dbo.v_userpro.pageview
How i do?
Please help me

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

Something like this?

SELECT TOP (@topval) dbo.bigtb.fire, dbo.maingroup.maingroup, dbo.bigtb.subgroup, dbo.maingroup.groupnamefa, dbo.subgroup.subgroupfa, dbo.bigtb.title1,

dbo.bigtb.tbody1, dbo.bigtb.film1, v_userpro.pageview, ISNULL(dbo.pictb.picurl1, '') AS picurl1, dbo.pictb.tooltip1, dbo.pictb.main1, dbo.bigtb.photo1,

v_userpro.pageview

FROM dbo.bigtb INNER JOIN

dbo.subgroup ON dbo.bigtb.subgroup = dbo.subgroup.subgroup INNER JOIN

dbo.maingroup ON dbo.subgroup.maingroup = dbo.maingroup.maingroup LEFT OUTER JOIN

(SELECT fire, COUNT(id1) AS pageview

FROM dbo.userpro

GROUP BY fire

)

v_userpro ON dbo.bigtb.fire = v_userpro.fire LEFT OUTER JOIN

dbo.pictb ON dbo.bigtb.fire = dbo.pictb.fire

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

msh083 SSC Journeyman Points: 80 More actions · Answer 2

v_userpro is a View,

I dont use View,

I want use

SELECT fire, COUNT(id1) AS pageview

FROM dbo.userpro

GROUP BY fire

as a SP, and i dont want use a view(v_userpro)

LutzM SSC Guru Points: 107049 More actions · Answer 3

Can you please explain why you neither want to use the view nor the underlying code itself?

Also, what you've posted is not a sp but single SELECT statement.

There's no simple way to include a stored procedure in a SELECT.

At the moment it is not clear what you're trying to do and why.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

msh083 SSC Journeyman Points: 80 More actions · Answer 4

In this SP

SELECT TOP (@topval) dbo.bigtb.fire, dbo.maingroup.maingroup, dbo.bigtb.subgroup, dbo.maingroup.groupnamefa, dbo.subgroup.subgroupfa, dbo.bigtb.title1,

dbo.bigtb.tbody1, dbo.bigtb.film1, dbo.v_userpro.pageview, ISNULL(dbo.pictb.picurl1, '') AS picurl1, dbo.pictb.tooltip1, dbo.pictb.main1, dbo.bigtb.photo1,

dbo.v_userpro.pageview

FROM dbo.bigtb INNER JOIN

dbo.subgroup ON dbo.bigtb.subgroup = dbo.subgroup.subgroup INNER JOIN

dbo.maingroup ON dbo.subgroup.maingroup = dbo.maingroup.maingroup LEFT OUTER JOIN

dbo.v_userpro ON dbo.bigtb.fire = dbo.v_userpro.fire LEFT OUTER JOIN

dbo.pictb ON dbo.bigtb.fire = dbo.pictb.fire

v_userpro is a View,

Userpro:

SELECT fire, COUNT(id1) AS pageview

FROM dbo.userpro

GROUP BY fire

I do not want use v_userpro.

I want use

SELECT fire, COUNT(id1) AS pageview

FROM dbo.userpro

GROUP BY fire

Instead v_userpro

one of firends sys:

SELECT TOP (@topval) dbo.bigtb.fire, dbo.maingroup.maingroup, dbo.bigtb.subgroup, dbo.maingroup.groupnamefa, dbo.subgroup.subgroupfa, dbo.bigtb.title1, dbo.bigtb.tbody1, dbo.bigtb.film1,

x.pageview, ISNULL(dbo.pictb.picurl1, '') AS picurl1, dbo.pictb.tooltip1, dbo.pictb.main1, dbo.bigtb.photo1,

x.pageview

FROM dbo.bigtb INNER JOIN

dbo.subgroup ON dbo.bigtb.subgroup = dbo.subgroup.subgroup INNER JOIN

dbo.maingroup ON dbo.subgroup.maingroup = dbo.maingroup.maingroup LEFT OUTER JOIN

x ON dbo.bigtb.fire = x.fire LEFT OUTER JOIN

dbo.pictb ON dbo.bigtb.fire = dbo.pictb.fire,

(

SELECT fire, COUNT(id1) AS pageview

FROM dbo.userpro

GROUP BY fire

) x

but it has error:

Msg 102, Level 15, State 1, Line 13

Incorrect syntax near 'x'.

Please Help me.

LutzM SSC Guru Points: 107049 More actions · Answer 5

Since you obviously simply don't like the alias v_userpro I provided a solution that doesn't use it at all.

Please note that it is absolutely equivalent to what I posted before (the one you didn't even bother to give a try), except for the subquery alias being renamed.

SELECT TOP (@topval) dbo.bigtb.fire, dbo.maingroup.maingroup, dbo.bigtb.subgroup, dbo.maingroup.groupnamefa, dbo.subgroup.subgroupfa, dbo.bigtb.title1,
dbo.bigtb.tbody1, dbo.bigtb.film1, some_alias.pageview, ISNULL(dbo.pictb.picurl1, '') AS picurl1, dbo.pictb.tooltip1, dbo.pictb.main1, dbo.bigtb.photo1,
some_alias.pageview
FROM dbo.bigtb INNER JOIN
dbo.subgroup ON dbo.bigtb.subgroup = dbo.subgroup.subgroup INNER JOIN
dbo.maingroup ON dbo.subgroup.maingroup = dbo.maingroup.maingroup LEFT OUTER JOIN
(SELECT fire, COUNT(id1) AS pageview
FROM dbo.userpro
GROUP BY fire
)
some_alias ON dbo.bigtb.fire = some_alias.fire LEFT OUTER JOIN
dbo.pictb ON dbo.bigtb.fire = dbo.pictb.fire

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

Could you please explain why you are so adamant about replacing the view in the code of the stored procedure? I really don't see any advantage to doing so based on what you have posted so far.

msh083 SSC Journeyman Points: 80 More actions · Answer 7

msh083

SSC Journeyman

Points: 80

April 27, 2014 at 6:44 am

#1709034

Thanks.