why Concatination not performed..???? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server Development (2000)

why Concatination not performed..????

Author

Topic

ramana123
Yak Posting Veteran

57 Posts

Posted - 2005-08-05 : 07:10:51

hi all,
iam not able to concatination for following thisngs:

SET @v_SQL_Stmnt = isnull(@v_SQL_Stmnt,'') + isnull(@v_SQL_Context_Part, '')

actually the @v_SQL_Stmnt contains some text(nearly 100 chars)
now i concatinated this with the @v_SQL_Context_Part ,it uis also having data(nearly 20 cars).why???

pls giv a sol pls.
thanks
ramns123

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-05 : 07:19:33

Did you get any error?
Post the full code you used

Madhivanan

Failing to plan is Planning to fail

ramana123
Yak Posting Veteran

57 Posts

Posted - 2005-08-05 : 07:23:30

hi ididnt get any error..
but after the concatination if i saw the @v_SQL_Stmnt variable it was not concatinATED WITH THE @v_SQL_Context_Part
THE BELOW IF CONDT WAS TRUE. THEN WHY...

IF (@In_UIO_ID > 0)/* Must be a UIO context. */
SET @v_SQL_Context_Part = (' And fv2.UIO_ID = ' + isnull(CAST( @In_UIO_ID AS CHAR(8000)), '') + ' And fv2.Charge_Level = ''U''' + isnull(@v_SQL_Context_Part, ''))

ELSE
IF (ISNULL((@In_Exam_Code + '.'), '.') <> '.') /* An exam context. */
SET @v_SQL_Context_Part = (' And fv2.Board_Code = ''' + isnull(@In_Board_Code, '') + ''' And fv2.Board_Occurrence = ''' + isnull(@In_Board_Occurrence, '') + ''' And fv2.Exam_Code = ''' + isnull(@In_Exam_Code, '') + '''' + ' And fv2.Charge_Level = ''E''' + isnull(@v_SQL_Context_Part, ''))
ELSE
IF ((@In_Price_Band_ID > 0) AND (@In_Fee_Value_Number > 0))
BEGIN

/* No context passed, so checking a Fee_Values record against a Price Band */
/* (i.e. an internal check within the Price band). */
/* NOTE :- Must also restrict the Calocc details so that we only check against fee values of the same calocc. */
SET @v_SQL_Context_Part = (' And fv2.Price_Band_ID = ' + isnull(CAST( @In_Price_Band_ID AS CHAR(8000)), '') + ' And fv2.Calocc_Code = fv1.Calocc_Code' + ' And fv2.Calocc_CType = fv1.Calocc_CType' + isnull(@v_SQL_Context_Part, ''))
END
ELSE
BEGIN
/* An invalid combination of arguments! */
IF (@In_Fail <> /* FALSE */ 0)
BEGIN
/* User wants this process to 'fall over' with an Oracle Error. */
RAISERROR ( -20903, 16, 1)
END
ELSE
BEGIN
SET @return_value_argument = /* FALSE */ 0
RETURN
END
END
SET @v_SQL_Stmnt = isnull(@v_SQL_Stmnt,'') + isnull(@v_SQL_Context_Part, '')
SET @param = ('Overlapping_Fee_Dates : ' + 'SQL Statement - ' + isnull(@v_SQL_Stmnt, ''))
EXEC dbo.SQL_UTILS_PKG_DB @param

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-05 : 07:40:47

>>isnull(CAST( @In_UIO_ID AS CHAR(8000))

Is it required to declare for 8000 length?

Try it with

isnull(CAST( @In_UIO_ID AS VARCHAR(2000))

Otherwise tell us your exact requirement

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2005-08-05 : 08:25:16

>> isnull(CAST( @In_UIO_ID AS CHAR(8000))

This is the problem, casting to CHAR is filling it up with spaces to the maximum 8000 characters, so the subsequent concatenation is working, but the length is more than 8,000 characters, and thus getting truncated (or causing error maybe).

You can CAST int, datetime and GUIDs to 20, 24, and 36 respectively. "50" is an easy number to remember! so limiting the size of your CASTs to 50, for all these datatypes, is safe, and if you use VARCHAR there will be no trailing spaces

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources