How to expand my Result ? - 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 2000 Forums

Transact-SQL (2000)

How to expand my Result ?

Author

Topic

wawansur
Starting Member

44 Posts

Posted - 2009-07-16 : 03:25:07

I create function with return value varchar(2000),
When it run, the result is only 256 characters.
I need help as soon as possible.

This the function :

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

ALTER FUNCTION f_GetSumBuySell_Buy (@TglTrans Datetime,@Gudang Varchar(30),@KdStock Varchar(30))
RETURNS VarChar (2000)
--WITH ENCRYPTION
AS
BEGIN
DECLARE @NoAwal VarChar(100)
DECLARE @NoAkhir VarChar(2000)
Declare @Gabung Varchar(100)

Declare @NoTrans Varchar(30)
Declare @QtyMain INT
Declare @QtyMainCon Varchar(30)
Declare @HPP Money

SET @NoAwal =''
SET @NoAkhir =''

DECLARE Curs CURSOR
LOCAL FOR
SELECT FNTrans,FQtyMain,dbo.f_Divide(FNominal,FQtyMain) As HPP FROM StckMsk
Where (FTpTr='BELI_R' or FTpTr='TrStckPdh_R') And FKStck=@KdStock And FKGdg=@Gudang And FTgl=@TglTrans
--Group BY FNTrans,FKStck,FTgl,FKGdg,FSat
OPEN Curs
FETCH NEXT FROM Curs INTO @NoTrans,@QtyMain,@HPP

WHILE @@FETCH_STATUS=0
BEGIN
Set @QtyMainCon=Cast(@QtyMain as Varchar)
Set @Gabung=@NoTrans+'('+@QtyMainCon+')'+'@'+Cast(@HPP As Varchar)
IF @NoAkhir =''
BEGIN
SET @NoAkhir = @Gabung
END
ELSE
BEGIN
SET @NoAkhir = @NoAkhir +','+ @Gabung
END
FETCH NEXT FROM Curs INTO @NoTrans,@QtyMain,@HPP
END
RETURN (@NoAkhir)
END

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

And I Call this funnction from procedure :

ALTER PROCEDURE RPt_STCK_LaporanPartHarian(@pPeriode DateTime,@pGudang Varchar(30))
--WITH ENCRYPTION
AS
select FTGL,FKstck,Fkgdg,SUM(QtyMsk) AS QtyMsk,SUM(QtyKlr) AS QtyKlr,
Sum(QtyAkhir) as QtyAkhir,ISNULL(QTYSISA,0) AS QTYSISA,
Dbo.f_GetSumBuySell(FTgl,FKgdg,FKstck) AS NoBukti,
Dbo.f_GetSumBuySell_Buy(FTgl,FKgdg,FKstck) AS NoBukti2,
(select FNamaper From CMPY) AS NAMAPER,
@pPeriode AS pPeriode,@pGudang AS pGudang

FROM PARTHARIAN

WHERE (@pPeriode=FTGL) AND
((@pGudang=FKGdg) OR @pGudang='{SEMUA}')

GROUP BY FTGL,FKstck,Fkgdg,QTYSISA

ORDER BY FTGL,FKstck

Rgds

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-16 : 04:04:38

Look at options in your query window. Often the standard is to truncate output to 256.
So it is only a visual problem.

No, you're never too old to Yak'n'Roll if you're too young to die.

wawansur
Starting Member

44 Posts

Posted - 2009-07-16 : 04:33:16

thax bro.
i got headeche for this problem for a week

BestRegards,
Readyfredd@gmail.com

Rgds

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-16 : 04:35:01

welcome

No, you're never too old to Yak'n'Roll if you're too young to die.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-16 : 05:49:37

Have you tried this set-based rewrite? It will be much faster than your function including CURSOR.

ALTER FUNCTION dbo.f_GetSumBuySell_Buy
(
	@TglTrans Datetime,
	@Gudang Varchar(30),
	@KdStock Varchar(30)
)
RETURNS VARCHAR(8000)
AS
BEGIN
	DECLARE	@Output VARCHAR(8000)

	SET	@Output = ''

	SELECT	@Output = @Output + ',' + FNTrans + '(' + CAST(FQtyMain AS VARCHAR(30)) + ')@'
				+ CAST(dbo.f_Divide(FNominal, FQtyMain) AS VARCHAR(30))
	FROM	StckMsk
	WHERE	FTpTr IN ('BELI_R', 'TrStckPdh_R')
		AND FKStck = @KdStock
		AND FKGdg = @Gudang
		AND FTgl = @TglTrans

	RETURN SUBSTRING(@Output, 2, DATALENGTH(@Output)) 
END

N 56°04'39.26"
E 12°55'05.63"

wawansur
Starting Member

44 Posts

Posted - 2009-07-17 : 08:09:51

thanks for idea

Rgds

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources