Receive (bind) the data coming from two select commands n a single proc in SQL Server

Question

1.00/5 (1 vote)

See more:

alter proc [dbo].[SpCateProducts](@cate_id int) 
as 
begin 
with 
tbl1 as 
(
select sb.subcate_name, 
sum(p.qty) 
from subcategory sb join product p 
on p.subcate_id = sb.subcate_id 
where sb.cate_id = @cate_id 
group by sb.subcate_name
) 
with tbl2 as
(
select top 1 pname from product join subcategory 
on product.subcate_id= 
(
select top 1 subcate_id 
from subcategory 
where cate_id=3 
order by NEWID() 
)
) 

select * from tbl1 join tbl2 end

But it shows some errors and doesnt executing......
1st errors in 5th line that (no column is specified in for column tbl1)
2nd in with keyword
3rd in tbl1
4th in end

plz tell me soon abt these errors ..............

Posted 14-Oct-12 5:33am

ashokdamani

Add a Solution

Comments

[no name] 14-Oct-12 11:45am

And what have you declared tbl1 to be?

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

**Zoltán Zörgő** · Answer 1 · 2012-10-14T06:31:00

This is a nonsense in t-sql. See my answer at your other question: how can i bind two tbls (returning by 2 select queries)[^].

By the way, how can you expect somebody to fix your code, without giving proper information: what you want as result (not that you want somebody to fix your code, we can figure this out by our own), what is your input (the table schemas and relation), what is the error message ("some error" means nothing)...
Please remember, that we can not see your screen, or read your mind. We can help you only, if you provide us the information needed...

damodara naidu betha · Answer 2 · 2012-10-14T19:45:00

Hi,

Error 1 is occurred because you didn't specify alias name for sum(p.qty) in the first
CTE.
Error 2, you didn't put ';' at the end of the first CTE.
Error 3, I am not able to tell the reason because you didn' provide any info of it.
Error 4, in the last statement, replace 'Join' with ','.

Above all syntactical errors. One more thing, CTE result can be persisted for its immediate select query only. So you need to convert your query like this..

SQL

alter proc [dbo].[SpCateProducts](@cate_id int) 
as 
begin 
with 
tbl1 as 
(
select sb.subcate_name, 
sum(p.qty) Qty
.............
) 
, tbl2 as
(
select top 1 pname from product join subcategory 
on product.subcate_id= 
(
select top 1 subcate_id 
from subcategory Join tbl1 ON
<your condition="">
--------- 
)
) 
 
select * from tbl2
 </your>

Please provide inputs and output of your require so that we can give our best solutions.

Thank you.

Receive (bind) the data coming from two select commands n a single proc in SQL Server

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0