stored procedure - 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

General SQL Server Forums

New to SQL Server Programming

stored procedure

Author

Topic

gagani
Posting Yak Master

112 Posts

Posted - 2012-03-22 : 12:50:17

table1
orderid product quantity measure_units
100 A 1 1
100 B 1 1
100 C 1 1
101 A 1 1
101 B 1 1

product
A
B
C
D
E
the output i am getting is
100
101

but the output should be
101
from the list of products in the front end, if
the user selects multiple products(say A and B) and flag=1, then it
should give the output as
101 (that is, the output should only filter the orderids which has got orderid with products A and B only, 100 has 3 products, so it should not come in output)

the stored procedure is as follows
for the selection of A,B in the front end,
the UF_CSVToTable1 will result in the #ordertest with
A
B

ALTER procedure [dbo].[test_flag1flag2]
(
@product1 varchar(1000),

@Flag varchar(100)
)
As
--product csv format to table format
create table #ordertest (product varchar(300))
insert into #ordertest(product) select surveyor from dbo.UF_CSVToTable1(@product1)

select distinct product into #AllProducts from product where product not in (select product from #ordertest)
DECLARE @OP_TABLE TABLE (OrderId bigint)

if(@Flag=1)

Begin
--
declare @orderid as varchar(50)
declare @ProName as varchar(50)
declare @c1 as int
declare @c2 as int

declare CUR1 cursor for select distinct orderid from table1
open CUR1
FETCH NEXT FROM CUR1 into @orderid
WHILE (@@FETCH_STATUS = 0)
BEGIN

set @c1 =1

declare CUR2 cursor for select distinct product from #ordertest
open CUR2
FETCH NEXT FROM CUR2 into @ProName
WHILE (@@FETCH_STATUS = 0)
BEGIN

set @c2 =0

select @c2=count(*) from table1 where orderid=@orderid and product = @ProName and product not in (select product from #AllProducts)

IF(@c2=0)
begin
set @c1=0
end
FETCH NEXT FROM CUR2 into @ProName
END
CLOSE CUR2
DEALLOCATE CUR2

if((@c1=1)
insert into @OP_TABLE(OrderId) values(@orderid)

FETCH NEXT FROM CUR1 into @orderid
END

CLOSE CUR1
DEALLOCATE CUR1
End

select * from @OP_TABLE

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-22 : 13:21:49

What does @Flag have to do with anything?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-22 : 13:24:35

Do us a favor...in Business terms...describe what the sproc is suppose to fo?

No pseudo code, just like a spec

Like

Show me all Products that have A & B if Request Flag = 1, Otherwise show me all Products

Something like that

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

gagani
Posting Yak Master

112 Posts

Posted - 2012-03-22 : 17:17:48

flag has nothing to do with the database and stored procedure.
please do not consider that flag part anywhere.
I am thinking that the problem would be at this line.

select @c2=count(*) from table1 where orderid=@orderid and product = @ProName and product not in (select product from #AllProducts)

I have also considered

select @c2=count(*) from table1 where orderid=@orderid and product = @ProName and orderid not in(select orderid from table1 where product in (select product from #AllProducts))

but is not giving any result.can you please help in this

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-03-22 : 17:29:14

http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-22 : 21:36:04

Ahhh.you're like me..you don't read the posts...ahh well

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources