passing parameter in function - 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

SQL Server 2005 Forums

Transact-SQL (2005)

passing parameter in function

Author

Topic

sqlhelp14
Yak Posting Veteran

55 Posts

Posted - 2008-03-10 : 09:49:50

I have to pass 3parameters in function,
@begindate,@enddate and @group_type..
but in @group_type should be - state,zipcode and country from salestable

inview :vwstzipcont
create view vwstzipcont
as
select distinct s2.stype,s3.itemnmbr,s2.docdate,s3.state,s3.zipcode,s3.country from Salestable s3
left outer join (select distinct stype,docdate from salesdisttable) s2
on s2.stype = s3.stype
where s2.soptype = 2
go

create function mystzipcont

( @begindate datetime, @enddate datetime, @group_type char(70))

RETURNS TABLE
AS
RETURN
(Select distinct t.docdate,t.itemnmbr,t.index,t.group_type from
(
select distinct
vs.docdate,vs.itemnmbr,

p.index From Pubs P

inner join vwstzipcont vs

on vs.index = p.index

Where (vs.docdate between @begindate and @enddate)
and @group_type ) as t

order by t.itemnmbr,t.docdate

end

how can i assign @group_type variable or t.group_type? in s3.state,s3.zipcode,s3.country
can anyone tell me? what condition should be in where clause for this variable?

thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-10 : 09:53:32

You need to either use CASE WHEN... or dynamic sql for this. CASE WHEN can be used as

create function mystzipcont

( @begindate datetime, @enddate datetime, @group_type char(70))

RETURNS TABLE
AS
RETURN
(Select distinct t.docdate,t.itemnmbr,t.index,t.group_type from
(
select distinct
vs.docdate,vs.itemnmbr,
case @group_type
when 'state' then vs.state
when 'zipcode' then vs.zipcode
when 'country' then vs.country
end
p.index From Pubs P

inner join vwstzipcont vs

on vs.index = p.index

Where (vs.docdate between @begindate and @enddate)
) as t

order by t.itemnmbr,t.docdate

end

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-03-10 : 09:55:29

quote:
Originally posted by sqlhelp14

I have to pass 3parameters in function,
@begindate,@enddate and @group_type..
but in @group_type should be - state,zipcode and country from salestable

inview :vwstzipcont
create view vwstzipcont
as
select distinct s2.stype,s3.itemnmbr,s2.docdate,s3.state,s3.zipcode,s3.country from Salestable s3
left outer join (select distinct stype,docdate from salesdisttable) s2
on s2.stype = s3.stype
where s2.soptype = 2
go

create function mystzipcont

( @begindate datetime, @enddate datetime, @group_type char(70))

RETURNS TABLE
AS
RETURN
(Select distinct t.docdate,t.itemnmbr,t.index,t.group_type from
(
select distinct
vs.docdate,vs.itemnmbr,

p.index, @group_type as group_type From Pubs P

inner join vwstzipcont vs

on vs.index = p.index

Where vs.docdate between @begindate and @enddate
) as t

order by t.itemnmbr,t.docdate

end

how can i assign @group_type variable or t.group_type? in s3.state,s3.zipcode,s3.country
can anyone tell me? what condition should be in where clause for this variable?

thanks

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-10 : 09:56:18

[code]CREATE VIEW vwStZipCont
AS

SELECT DISTINCT s2.stype,
s3.itemnmbr,
s2.docdate,
s3.state,
s3.zipcode,
s3.country
FROM Salestable AS s3
LEFT JOIN SalesDistTable AS s2 ON s2.stype = s3.stype
AND s2.soptype = 2[/code]Please read this carefully
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

E 12°55'05.25"
N 56°04'39.16"

sqlhelp14
Yak Posting Veteran

55 Posts

Posted - 2008-03-10 : 10:19:28

thanks visakh16 ..i got the results..and also thanks to harsh and peso..

sqlhelp14
Yak Posting Veteran

55 Posts

Posted - 2008-03-11 : 10:52:22

i have to add one column in function to get in output of - totalquantity for each state which i did..

but i want for each single state totalquantity..

create function mystzipcont

( @begindate datetime, @enddate datetime, @group_type char(70))

RETURNS TABLE
AS
RETURN
(Select distinct t.docdate,t.itemnmbr,t.totalquantity,t.index,t.group_type from
(
select distinct
vs.docdate,vs.itemnmbr,vs.totalquantity,
case @group_type
when 'state' then vs.state
when 'zipcode' then vs.zipcode
when 'country' then vs.country
end
p.index From Pubs P

inner join vwstzipcont vs

on vs.index = p.index

Where (vs.docdate between @begindate and @enddate)
) as t

order by t.itemnmbr,t.docdate

end
---------------------------------------------

camp 2007-06-06 00:00:00.000 06-5840 100.00000 AK
camp 2007-06-06 00:00:00.000 06-5841 25.00000 AK
camp 2007-01-19 00:00:00.000 06-5840 1.00000 AL
camp 2007-01-19 00:00:00.000 06-5841 1.00000 AL
camp 2007-01-29 00:00:00.000 06-5840 1.00000 AL
camp 2007-01-29 00:00:00.000 06-5841 1.00000 AL
camp 2007-02-07 00:00:00.000 06-5840 1.00000 AL
camp 2007-10-17 00:00:00.000 KT-048 1.00000 AL

i want totalquanitty for AK state and AL state one one row..
i sum to totalquantity but still same ..

how can i get it?

thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-11 : 11:03:08

You want it as a seperte row or should it come as a seperate field in each row?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-11 : 11:05:26

Please read this blog post CAREFULLY

E 12°55'05.25"
N 56°04'39.16"

sqlhelp14
Yak Posting Veteran

55 Posts

Posted - 2008-03-11 : 11:10:31

i m getting like this:

camp 2007-06-06 00:00:00.000 06-5840 100.00000 AK
camp 2007-06-06 00:00:00.000 06-5841 25.00000 AK
camp 2007-01-19 00:00:00.000 06-5840 1.00000 AL
camp 2007-01-19 00:00:00.000 06-5841 1.00000 AL
camp 2007-01-29 00:00:00.000 06-5840 1.00000 AL
camp 2007-01-29 00:00:00.000 06-5841 1.00000 AL
camp 2007-02-07 00:00:00.000 06-5840 1.00000 AL
camp 2007-10-17 00:00:00.000 KT-048 1.00000 AL

but want like this:
for each itemnbmr and each state totalquantity..
means for example: AK state itemnmbr will not repetetive..

camp 2007-06-06 00:00:00.000 06-5840 100.00000 AK
camp 2007-06-06 00:00:00.000 06-5841 25.00000 AK

taht's fine.. for AL state
camp 2007-01-19 00:00:00.000 06-5840 3.00000 AL
camp 2007-01-19 00:00:00.000 06-5841 3.00000 AL
camp 2007-10-17 00:00:00.000 KT-048 1.00000 AL

means sum(totalquantity) for same itemnmbr for same state..if itemnmbr different for same state then remain totalquanitty as it is..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-11 : 11:13:36

change like this:-

create function mystzipcont

( @begindate datetime, @enddate datetime, @group_type char(70))

RETURNS TABLE
AS
RETURN
(Select distinct t.docdate,t.itemnmbr,sum(t.totalquantity) as totalquantity,t.index,t.group_type from 
(
select distinct
vs.docdate,vs.itemnmbr,vs.totalquantity,
case @group_type 
when 'state' then vs.state
when 'zipcode' then vs.zipcode
when 'country' then vs.country
end
p.index From Pubs P

inner join vwstzipcont vs 

on vs.index = p.index

Where (vs.docdate between @begindate and @enddate) 
) as t

group by t.docdate,t.itemnmbr,t.index,t.group_type 
order by t.itemnmbr,t.docdate

sqlhelp14
Yak Posting Veteran

55 Posts

Posted - 2008-03-11 : 11:16:37

no its not for report...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-11 : 11:20:11

Sorry i misinterpreted...have editted my reply... :)

sqlhelp14
Yak Posting Veteran

55 Posts

Posted - 2008-03-11 : 11:27:04

thanks for ur reply visakh16.

but still same results.
camp 2007-01-19 00:00:00.000 06-5840 1.00000 AL
camp 2007-01-19 00:00:00.000 06-5841 1.00000 AL
camp 2007-01-29 00:00:00.000 06-5840 1.00000 AL
camp 2007-01-29 00:00:00.000 06-5841 1.00000 AL
camp 2007-02-07 00:00:00.000 06-5840 1.00000 AL
camp 2007-10-17 00:00:00.000 KT-048 1.00000 AL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-11 : 11:31:48

Have you used all other fields in group by than total quantity?

sqlhelp14
Yak Posting Veteran

55 Posts

Posted - 2008-03-11 : 11:39:46

yes i have used all fields in group by

and when i m not seelcting docdate then i m getting correct results..each itemnmbr for each state and total quantity...but as i m paassing docdate-begindate and enddate in function..i also want docdate in output and then i m not getting results.

anyways, thanks a lot!! visakh16.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources