condition in input - 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)

condition in input

Author

Topic

ri16
Yak Posting Veteran

64 Posts

Posted - 2008-04-18 : 13:16:26

create function quantprice

( @itemnmbr varchar(50), @startdate datetime, @enddate datetime)

RETURNS TABLE
AS
RETURN

(Select distinct t.itemnmbr,t.totalprice as totalcost

from
(
select distinct

vs.itemnmbr, sum(vs.totalsumprce) as totalprice

from vwquantityprice vs

Where vs.itemnmbr = @itemnmbr and (vs.docdate between @startdate and @enddate)

group by vs.itemnmbr

) as t

)

select * from quantityprice('06-5840','4/1/2007','4/1/2008')

well, guys, i have this function and obviously i will get one row from this in output...with that particular itemnumber but i want multi-itemnmbrs some times in output...some times 2..soemtimes 3...

example,
select * from quantityprice('06-5840,ab-4581,0a-1458,45-0945','4/1/2007','4/1/2008')

can you tell me what condition it will come to get this output..

like in where itemnmbr in('06-5840,ab-4581,0a-1458,45-0945')
and in starting input v ariable @itemnmbr..

create function quantprice

( @itemnmbr varchar(50)..

don't know what condition it will come and where i have to put condition in where clause or in input variable..

any help would be appreciated..thanks a lot!! guys plz reply.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-18 : 13:39:00

try like this:-

create function quantprice

( @itemnmbr varchar(2000), @startdate datetime, @enddate datetime)

RETURNS TABLE
AS
RETURN

(Select distinct t.itemnmbr,t.totalprice as totalcost

from 
(
select distinct

vs.itemnmbr, sum(vs.totalsumprce) as totalprice 


from vwquantityprice vs 


Where  ','+ @itemnmbr + ',' like '%,'+ vs.itemnmbr + ',%'
and (vs.docdate between @startdate and @enddate) 


group by vs.itemnmbr

) as t



)

tm
Posting Yak Master

160 Posts

Posted - 2008-04-18 : 13:41:06

You can try something like (below is not tested or complete) ...

create function quantprice ( @itemnmbr varchar(50), @startdate datetime, @enddate datetime)

RETURNS TABLE @Result table (itemnmbr varchar(50), TotalCost money)
AS
BEGIN
declare @temptable table (itemnumber varchar(50))

-- TODO :: do some processing to separate @itemnmbr and insert into @temptable as individual item numbers

-- below where clause will use temp table to detemine item numbers from variable table

insert into @Result (itemnmbr, Totalcost)
Select distinct t.itemnmbr,t.totalprice as totalcost

from
(
select distinct

vs.itemnmbr, sum(vs.totalsumprce) as totalprice

from vwquantityprice vs

Where vs.itemnmbr in (select itemnumber from @temptable ) and (vs.docdate between @startdate and @enddate)

group by vs.itemnmbr

) as t

RETURN -- return records to calling script

END

ri16
Yak Posting Veteran

64 Posts

Posted - 2008-04-18 : 13:47:48

thanks for immediate reply visakh and tm..

but i m not getting any rows from visakh's solution and from tm i m gettign error - Incorrect syntax near '@Result'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-18 : 14:01:57

How are you pssing the itemnumber values in parameter?

ri16
Yak Posting Veteran

64 Posts

Posted - 2008-04-18 : 14:06:35

itemnmbrs r coming from vwquantityprice

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-18 : 14:12:08

Nope. i was asking how are you passing value to function?
like this?
'06-5840,ab-4581,0a-1458,45-0945'

ri16
Yak Posting Veteran

64 Posts

Posted - 2008-04-18 : 14:19:22

hi visakh,

there are so many itemnmbrs like
'06-5840,ab-4581,0a-1458,45-0945'...and more..
i have to pass itemnmbr,startdate and endate in function...

so if i will pass - '06-5840','4/1/2007','4/1/2008'
select * from quantityprice('06-5840','4/1/2007','4/1/2008') - will get one row for itemnmbr'06-5840'

itemnmbr totalquantity
------------------------
06-5840 5100.00000

if i will pass - '06-5840,06-5841','4/1/2007','4/1/2008'
select * from quantityprice('06-5840,06-5841','4/1/2007','4/1/2008') - will get two rows for itemnmbr'06-5840' & '06-5841

itemnmbr totalquantity
------------------------
06-5840 5100.00000
06-5841 1542.00000

..so on..

thanks

ri16
Yak Posting Veteran

64 Posts

Posted - 2008-04-18 : 14:43:06

create function quantprice

( @itemnmbr varchar(2000), @startdate datetime, @enddate datetime)

RETURNS TABLE
AS
RETURN

(Select distinct t.itemnmbr,t.totalprice as totalcost

from
(
select distinct

case @itemnmbr
when vs.itemnmbr then vs.itemnmbr
else @itemnmbr
end as itemnmbr,

sum(vs.totalsumprce) as totalprice

from vwquantityprice vs

Where ','+ @itemnmbr + ',' like '%,'+ vs.itemnmbr + ',%'
and (vs.docdate between @startdate and @enddate)

group by vs.itemnmbr

) as t

)

i m passing - '06-5840,06-5841','4/1/2007','4/1/2008'
select * from quantityprice('06-5840,06-5841','4/1/2007','4/1/2008') - will get two rows for itemnmbr'06-5840' & '06-5841

itemnmbr totalcost
------------------------
06-5840,06-5841 1022868.620000000

totalcost is getting wrong fiigure..
and instead of two rows getting one row..

like i want output as:
itemnmbr totalquantity
------------------------
06-5840 5100.00000
06-5841 1542.00000

can anyone help me to figure this out. plz.

thank you.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-18 : 14:59:43

change like this & try
create function quantprice
( @itemnmbr varchar(2000), @startdate datetime, @enddate datetime)

RETURNS TABLE
AS
RETURN

(Select distinct t.itemnmbr,t.totalprice as totalcost

from
(
select distinct

vs.itemnumber,

sum(vs.totalsumprce) as totalprice

from vwquantityprice vs

Where ','+ @itemnmbr + ',' like '%,'+ vs.itemnmbr + ',%'
and (vs.docdate between @startdate and @enddate)
group by vs.itemnmbr

) as t

)

ri16
Yak Posting Veteran

64 Posts

Posted - 2008-04-18 : 15:12:45

i tried with ur solution visakh and i m getting 0rows affected.
select * from quantprice('06-5840','4/17/2007','4/18/2008')

with my solution:
create function quantprice

( @itemnmbr varchar(2000), @startdate datetime, @enddate datetime)

RETURNS TABLE
AS
RETURN

(Select distinct t.itemnmbr,t.totalprice as totalcost

from
(
select distinct

case @itemnmbr
when vs.itemnmbr then vs.itemnmbr
else @itemnmbr
end as itemnmbr,

sum(vs.totalsumprce) as totalprice

from vwquantityprice vs

Where ','+ @itemnmbr + ',' like '%,'+ vs.itemnmbr + ',%'
and (vs.docdate between @startdate and @enddate)

group by vs.itemnmbr

) as t

)

i m passing - '06-5840,06-5841','4/1/2007','4/1/2008'
select * from quantityprice('06-5840,06-5841','4/1/2007','4/1/2008') - will get two rows for itemnmbr'06-5840' & '06-5841

itemnmbr totalcost
------------------------
06-5840,06-5841 1022868.620000000

totalcost is getting wrong fiigure..
and instead of two rows getting one row..

like i want output as:
itemnmbr totalcost
------------------------
06-5840 5100.00000
06-5841 1542.00000

i m not getting correct totalcostfigure:
itemnmbr totalcost
------------------------
06-5840,06-5841 1022868.620000000

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-18 : 15:17:43

Did you try my last post? i've modified the case construct

ri16
Yak Posting Veteran

64 Posts

Posted - 2008-04-18 : 15:20:27

visakh, i tried urs:

change like this & try
create function quantprice
( @itemnmbr varchar(2000), @startdate datetime, @enddate datetime)

RETURNS TABLE
AS
RETURN

(Select distinct t.itemnmbr,t.totalprice as totalcost

from
(
select distinct

vs.itemnumber,

sum(vs.totalsumprce) as totalprice

from vwquantityprice vs

Where ','+ @itemnmbr + ',' like '%,'+ vs.itemnmbr + ',%'
and (vs.docdate between @startdate and @enddate)
group by vs.itemnmbr

) as t

)

but m not getting single row in output..

thanks for ur replies.

tm
Posting Yak Master

160 Posts

Posted - 2008-04-18 : 15:39:45

Sorry about the post earlier as it was quick and it was not tested.

Below code should work as I tried this on test data. Just cut and past and create the function.

If you already have the function created then please drop before creating.

---------------------------------------

create function dbo.quantprice ( @itemnmbr varchar(2000), @startdate datetime, @enddate datetime)
RETURNS @Result TABLE (itemnmbr varchar(50), TotalCost money)
AS
BEGIN
declare @temptable table (itemnumber varchar(50))

-- Code to process comma delimited variable
DECLARE @NextPos SMALLINT,
@LastPos SMALLINT

SELECT @NextPos = 0

WHILE @NextPos <= DATALENGTH(@itemnmbr)
BEGIN
SELECT @LastPos = @NextPos,
@NextPos = CASE
WHEN CHARINDEX(',', @itemnmbr, @LastPos + 1) = 0 THEN DATALENGTH(@itemnmbr) + 1
ELSE CHARINDEX(',', @itemnmbr, @LastPos + 1)
END

INSERT @temptable (itemnumber)
SELECT (SUBSTRING(@itemnmbr, @LastPos + 1, @NextPos - @LastPos - 1))
END

-- ==========================

insert @Result (itemnmbr, Totalcost)
Select distinct t.itemnmbr,t.totalprice as totalcost

from
(
select distinct

vs.itemnmbr, sum(vs.totalsumprce) as totalprice

from vwquantityprice vs

Where vs.itemnmbr in (select itemnumber from @temptable ) and (vs.docdate between @startdate and @enddate)

group by vs.itemnmbr

) as t

RETURN -- return records to calling script

END

ri16
Yak Posting Veteran

64 Posts

Posted - 2008-04-18 : 16:10:00

thanks tm..

perfect got the results..

thanks a tonnnnnnnnnnnnnn!!

its goot to know about temptable variable info..

ri16
Yak Posting Veteran

64 Posts

Posted - 2008-04-21 : 08:53:45

i have anotehr function and same scenario of inputing itemnumber but with the same process i m getting one error:
create function campaignitemstest ( @itemnmbr varchar(2000))
RETURNS @Result TABLE (itemnmbr varchar(2000),
[Name] varchar(80), Description varchar(1000),[Image] varchar(1000),
PDF_URL varchar(500), HTML_URL varchar(500), OLC_URL varchar(500),
NHLBI_URL varchar(500), QuantityOnHand money)
AS
BEGIN
declare @temptable table (itemnumber varchar(2000))

-- Code to process comma delimited variable
DECLARE @NextPos SMALLINT,
@LastPos SMALLINT

SELECT @NextPos = 0

WHILE @NextPos <= DATALENGTH(@itemnmbr)
BEGIN
SELECT @LastPos = @NextPos,
@NextPos = CASE
WHEN CHARINDEX(',', @itemnmbr, @LastPos + 1) = 0 THEN DATALENGTH(@itemnmbr) + 1
ELSE CHARINDEX(',', @itemnmbr, @LastPos + 1)
END

INSERT @temptable (itemnumber)
SELECT (SUBSTRING(@itemnmbr, @LastPos + 1, @NextPos - @LastPos - 1))
END

-- ==========================

insert @Result (itemnmbr, [Name], Description,[Image], PDF_URL, HTML_URL, OC_URL,
NI_URL, QuantityOnHand)
Select distinct t.Itemnmbr, t.[Name], t.Description,t.[Image], t.PDF_URL, t.HTML_URL, t.OC_URL,
t.NI_URL, t.QuantityOnHand

from
(
select distinct

vs.Itemnmbr, vs.[Name], vs.Description, vs.[image], vs.PDF_URL, vs.HTML_URL, vs.OC_URL,
vs.NI_URL, vs.QuantityOnHand

From vwiteminfo vs

Where vs.itemnmbr in (select itemnumber from @temptable )

) as t

RETURN -- return records to calling script

END

--select * from myitemgetcampaignitemstest('06-5840')

--select * from myitemgetcampaignitemstest('06-5840,06-5841,06-5845')

till here i m getting results perfectly...even if i m inputing 3more itemnumber i m getting results

but when i m inputing itemnumbers starting with alphabet characters..
i m getting error:

select * from myitemgetcampaignitemstest('ke-048')

String or binary data would be truncated.
The statement has been terminated.

tm
Posting Yak Master

160 Posts

Posted - 2008-04-21 : 09:12:42

quote:
Originally posted by ri16

@Result TABLE (itemnmbr varchar(2000),
[Name] varchar(80), Description varchar(1000),[Image] varchar(1000),
PDF_URL varchar(500), HTML_URL varchar(500), OLC_URL varchar(500),
NHLBI_URL varchar(500), QuantityOnHand money)

What is the vwiteminfo view columns declared as?
It could be simple case of [Image] needs to be larger than varchar(1000).

Another thing is itemnmbr is varchar(2000). Do you really need it to be this size?

ri16
Yak Posting Veteran

64 Posts

Posted - 2008-04-21 : 09:33:35

thanks tm..yes, i modified all the columns from vwitemsinfo columns datatypes...

and yes, i have itemnumber varchar(2000) as i have to input multiple itemnmbrs so..

thanks a lot!!! for ur help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources