create procedure using outputs

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)

create procedure using outputs

Author

Topic

ri16
Yak Posting Veteran

64 Posts

Posted - 2008-02-13 : 11:39:18

indx Title plateno
-------------------------------
0 Wallet 07-5245
0 Palletbox 07-5248
0 Juicecup 07-5251

(3 row(s) affected)

indx Title plateno
----------- ------------------
1 Boxcup 3651
1 Boxcup 034SPO
1 Boxcup 034NO
1 Boxcup 830
1 Boxcup ABO
1 Boxcup INO
1 Boxcup ED87
1 Boxcup TY89
1 Boxcup IU8

(9 row(s) affected)

i have to use these 2select statements ouput and create one procedure if indx=0 then ...
and if indx=1 then....

how do i create procedure?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-13 : 11:43:27

[code]CREATE PROCEDURE YourProc
@Indx int
AS
if @indx=0
begin
.......
.......
code here
end
if @indx=1
begin
.......
.......
code here
end

go[/code]

ri16
Yak Posting Veteran

64 Posts

Posted - 2008-02-13 : 12:00:28

thanks for ur reply..but its not simple..actually, i forgot to mention that indx and title is in one table and plateno is in one function

CREATE PROCEDURE myproc
AS
BEGIN

SELECT distinct

t.indx, t.title,

case when t.indx = 0 then dt.plateno
when t.indx = 1 then dt.plateno end as plateno

FROM titleindxtab t

right outer join funcDtitle('') dt
on t.indx=1
where indx=t.indx
and dt.plateno is not null

end

but insteada of getting 12rows as output..i m getting 900rows..

ri16
Yak Posting Veteran

64 Posts

Posted - 2008-02-13 : 12:07:35

SELECT 0 as indx,Title as dTitle,
plateno from funcDtitle('')
where num in ('P','he1r','pu14')
and Topnum in ('PG','THM')
and Aucnum in ('47c', '145p')
and not Snum in ('EA','LPTC')

SELECT 1 as indx, 'Care Cent' as dTitle,
plateno
from funcDtitle('')
where num in ('P','he1r','pu14')
and Topnum in ('PG','THM')
and Aucnum in ('47c', '145p')
and Snum in ('EA','LPTC')

these r 2select statements which i have andi have to use in sproc..but i don't have to use where clause values..in sproc..

can u help me now?

thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-13 : 12:09:19

Sorry but you have explain this more,giving table structures,some sample data and also tell what function does.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-14 : 04:24:40

Try

CREATE PROCEDURE myproc(@indx int)
AS
BEGIN
SELECT distinct
t.indx, t.title,
dt.plateno end as plateno
FROM titleindxtab t
right outer join funcDtitle('') dt
on t.indx=1
where t.indx=@indx
and dt.plateno is not null
END

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources