absolutely weird String Concatenation problem, and really daring!

Hi,
I'm having this realy weird stuff going on here with a string concatenation
I do in a Cursor. It seems my string (NVARCHAR(4000)) is somewherel imited
to a number of concatenations or stuff like hat. Instead of doing everytime
the "SET @.strSQL = @.strSQL + ' + ' + ''') AND (''' + ' + ' + @.strField "
everytime, it suddenly stops adding the @.strField, even when there is a
value in it...
Just check it out, I added the returned values...
Anybody who can help with this would be reallt appreciated, i can't find
anything about this on google :-(
Pieter
***************************************** My Query
CREATE TABLE #tmp
(COLUMN_NAME nvarchar(100),DATA_TYPE varchar(20))
INSERT INTO #tmp
EXEC spSyncKeyColumns 'STOCK2'
/* Make the cursor:*/
DECLARE curC CURSOR
FOR
SELECT /*col.COLUMN_NAME, col.DATA_TYPE, */
CASE col.DATA_TYPE
WHEN 'char' THEN '(''' + col.COLUMN_NAME + ' = '' + ' + '''''''''' + ' + '
+ col.COLUMN_NAME + ' + ' + '''''''''' + ' )'
WHEN 'nchar' THEN '(''' + col.COLUMN_NAME + ' = '' + ' + '''''''''' + ' +
' + col.COLUMN_NAME + ' + ' + '''''''''' + ' )'
WHEN 'varchar' THEN '(''' + col.COLUMN_NAME + ' = '' + ' + '''''''''' + '
+ ' + col.COLUMN_NAME + ' + ' + '''''''''' + ' )'
WHEN 'nvarchar' THEN '(''' + col.COLUMN_NAME + ' = '' + ' + '''''''''' + '
+ ' + col.COLUMN_NAME + ' + ' + '''''''''' + ' )'
WHEN 'text' THEN '(''' + col.COLUMN_NAME + ' = '' + ' + '''''''''' + ' + '
+ col.COLUMN_NAME + ' + ' + '''''''''' + ' )'
WHEN 'ntext' THEN '(''' + col.COLUMN_NAME + ' = '' + ' + '''''''''' + ' +
' + col.COLUMN_NAME + ' + ' + '''''''''' + ' )'
WHEN 'uniqueidentifier' THEN '(''' + col.COLUMN_NAME + ' = '' + ' +
'''''''''' + ' + ' + col.COLUMN_NAME + ' + ' + '''''''''' + ' )'
WHEN 'datetime' THEN '(''' + col.COLUMN_NAME + ' = '' + ' + '''''''''' + '
+ CAST(CONVERT(datetime, ' + col.COLUMN_NAME + ', 126) AS NVARCHAR) + ' +
'''''''''' + ' )'
WHEN 'smalldatetime' THEN '(''' + col.COLUMN_NAME + ' = '' + ' +
'''''''''' + ' + CAST(CONVERT(smalldatetime, ' + col.COLUMN_NAME + ', 126)
AS NVARCHAR) + ' + '''''''''' + ' )'
WHEN 'timestamp' THEN '(''' + col.COLUMN_NAME + ' = '' + ' +
col.COLUMN_NAME + ' )'
ELSE '(''' + col.COLUMN_NAME + ' = '' + CAST(' + col.COLUMN_NAME + ' AS
NVARCHAR) )'
END AS Syntax
FROM #tmp col
/* Open the Cursor*/
OPEN curC
/* Declaration of the variables*/
DECLARE @.strSQL nvarchar(4000), @.strField NVARCHAR(100)
SET @.strSQL = ''
/*FIRST*/
FETCH NEXT FROM curC INTO @.strField
BEGIN
SET @.strSQL = @.strField
SELECT @.strField
END
/*THE OTHERS*/
FETCH NEXT FROM curC INTO @.strField
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.strSQL = @.strSQL + ' + ' + ''') AND (''' + ' + ' + @.strField
SELECT @.strField
/* Take next Table*/
FETCH NEXT FROM curC INTO @.strField
END
/* Fermes le Cursor*/
CLOSE curC
DEALLOCATE curC
DROP TABLE #tmp
SET @.strSQL = '( ''('' + ' + @.strSQL + ' + '')'' ) AS TWhere'
SELECT COALESCE(@.strSQL, 'NULL AS TWhere')
GO
***************************************** End of My Query
***************************************** The Results:
(5 row(s) affected)
('CODE_SOCIETE = ' + '''' + CODE_SOCIETE + '''' )
(1 row(s) affected) -> My First @.strField
('ID_TYPE = ' + CAST(ID_TYPE AS NVARCHAR) )
(1 row(s) affected) -> My 2nd @.strField
('NUM_SITE = ' + CAST(NUM_SITE AS NVARCHAR) )
(1 row(s) affected) -> My 3nd @.strField
('ID_CONTAINER = ' + CAST(ID_CONTAINER AS NVARCHAR) )
(1 row(s) affected) -> My 4th @.strField
('DATE_TIME = ' + DATE_TIME )
(1 row(s) affected) -> My 5th @.strField
----
( '(' + ('CODE_SOCIETE = ' + '''' + CODE_SOCIETE + '''' ) + ') AND (' +
('ID_TYPE = ' + CAST(ID_TYPE AS NVARCHAR) ) + ') AND (' + ('NUM_SITE = ' +
CAST(NUM_SITE AS NVARCHAR) ) + ') AND (' + ('ID_CONTAINER = ' +
CAST(ID_CONTAINER AS NVARCHAR) ) + ') AND ('
(1 row(s) affected) -> The final @.strSQL... As you can see: the
5th @.strField isn't added, although the ") AND (" is added...
Can anybody help me with this absolutely weird stuff? Thank a lot in
advance!!
When using string concatenation you should always keep in mind the option
that a column / variable can be null --> ISNULL(SomeColumn,'')
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"DraguVaso" wrote:

> Hi,
> I'm having this realy weird stuff going on here with a string concatenation
> I do in a Cursor. It seems my string (NVARCHAR(4000)) is somewherel imited
> to a number of concatenations or stuff like hat. Instead of doing everytime
> the "SET @.strSQL = @.strSQL + ' + ' + ''') AND (''' + ' + ' + @.strField "
> everytime, it suddenly stops adding the @.strField, even when there is a
> value in it...
> Just check it out, I added the returned values...
> Anybody who can help with this would be reallt appreciated, i can't find
> anything about this on google :-(
> Pieter
> ***************************************** My Query
> CREATE TABLE #tmp
> (COLUMN_NAME nvarchar(100),DATA_TYPE varchar(20))
> INSERT INTO #tmp
> EXEC spSyncKeyColumns 'STOCK2'
> /* Make the cursor:*/
> DECLARE curC CURSOR
> FOR
> SELECT /*col.COLUMN_NAME, col.DATA_TYPE, */
> CASE col.DATA_TYPE
> WHEN 'char' THEN '(''' + col.COLUMN_NAME + ' = '' + ' + '''''''''' + ' + '
> + col.COLUMN_NAME + ' + ' + '''''''''' + ' )'
> WHEN 'nchar' THEN '(''' + col.COLUMN_NAME + ' = '' + ' + '''''''''' + ' +
> ' + col.COLUMN_NAME + ' + ' + '''''''''' + ' )'
> WHEN 'varchar' THEN '(''' + col.COLUMN_NAME + ' = '' + ' + '''''''''' + '
> + ' + col.COLUMN_NAME + ' + ' + '''''''''' + ' )'
> WHEN 'nvarchar' THEN '(''' + col.COLUMN_NAME + ' = '' + ' + '''''''''' + '
> + ' + col.COLUMN_NAME + ' + ' + '''''''''' + ' )'
> WHEN 'text' THEN '(''' + col.COLUMN_NAME + ' = '' + ' + '''''''''' + ' + '
> + col.COLUMN_NAME + ' + ' + '''''''''' + ' )'
> WHEN 'ntext' THEN '(''' + col.COLUMN_NAME + ' = '' + ' + '''''''''' + ' +
> ' + col.COLUMN_NAME + ' + ' + '''''''''' + ' )'
> WHEN 'uniqueidentifier' THEN '(''' + col.COLUMN_NAME + ' = '' + ' +
> '''''''''' + ' + ' + col.COLUMN_NAME + ' + ' + '''''''''' + ' )'
> WHEN 'datetime' THEN '(''' + col.COLUMN_NAME + ' = '' + ' + '''''''''' + '
> + CAST(CONVERT(datetime, ' + col.COLUMN_NAME + ', 126) AS NVARCHAR) + ' +
> '''''''''' + ' )'
> WHEN 'smalldatetime' THEN '(''' + col.COLUMN_NAME + ' = '' + ' +
> '''''''''' + ' + CAST(CONVERT(smalldatetime, ' + col.COLUMN_NAME + ', 126)
> AS NVARCHAR) + ' + '''''''''' + ' )'
> WHEN 'timestamp' THEN '(''' + col.COLUMN_NAME + ' = '' + ' +
> col.COLUMN_NAME + ' )'
> ELSE '(''' + col.COLUMN_NAME + ' = '' + CAST(' + col.COLUMN_NAME + ' AS
> NVARCHAR) )'
> END AS Syntax
> FROM #tmp col
> /* Open the Cursor*/
> OPEN curC
> /* Declaration of the variables*/
> DECLARE @.strSQL nvarchar(4000), @.strField NVARCHAR(100)
> SET @.strSQL = ''
> /*FIRST*/
> FETCH NEXT FROM curC INTO @.strField
> BEGIN
> SET @.strSQL = @.strField
> SELECT @.strField
> END
> /*THE OTHERS*/
> FETCH NEXT FROM curC INTO @.strField
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> SET @.strSQL = @.strSQL + ' + ' + ''') AND (''' + ' + ' + @.strField
> SELECT @.strField
> /* Take next Table*/
> FETCH NEXT FROM curC INTO @.strField
> END
> /* Fermes le Cursor*/
> CLOSE curC
> DEALLOCATE curC
> DROP TABLE #tmp
> SET @.strSQL = '( ''('' + ' + @.strSQL + ' + '')'' ) AS TWhere'
> SELECT COALESCE(@.strSQL, 'NULL AS TWhere')
> GO
> ***************************************** End of My Query
>
> ***************************************** The Results:
> (5 row(s) affected)
>
> ----
> --
> ('CODE_SOCIETE = ' + '''' + CODE_SOCIETE + '''' )
> (1 row(s) affected) -> My First @.strField
>
> ----
> --
> ('ID_TYPE = ' + CAST(ID_TYPE AS NVARCHAR) )
> (1 row(s) affected) -> My 2nd @.strField
>
> ----
> --
> ('NUM_SITE = ' + CAST(NUM_SITE AS NVARCHAR) )
> (1 row(s) affected) -> My 3nd @.strField
>
> ----
> --
> ('ID_CONTAINER = ' + CAST(ID_CONTAINER AS NVARCHAR) )
> (1 row(s) affected) -> My 4th @.strField
>
> ----
> --
> ('DATE_TIME = ' + DATE_TIME )
> (1 row(s) affected) -> My 5th @.strField
>
> ----
> ----
> ----
> --
> ( '(' + ('CODE_SOCIETE = ' + '''' + CODE_SOCIETE + '''' ) + ') AND (' +
> ('ID_TYPE = ' + CAST(ID_TYPE AS NVARCHAR) ) + ') AND (' + ('NUM_SITE = ' +
> CAST(NUM_SITE AS NVARCHAR) ) + ') AND (' + ('ID_CONTAINER = ' +
> CAST(ID_CONTAINER AS NVARCHAR) ) + ') AND ('
> (1 row(s) affected) -> The final @.strSQL... As you can see: the
> 5th @.strField isn't added, although the ") AND (" is added...
>
> Can anybody help me with this absolutely weird stuff? Thank a lot in
> advance!!
>
>
>
|||Dragu
What do you want to accomplish?
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:OOZz9SzlFHA.2472@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I'm having this realy weird stuff going on here with a string
> concatenation
> I do in a Cursor. It seems my string (NVARCHAR(4000)) is somewherel imited
> to a number of concatenations or stuff like hat. Instead of doing
> everytime
> the "SET @.strSQL = @.strSQL + ' + ' + ''') AND (''' + ' + ' + @.strField "
> everytime, it suddenly stops adding the @.strField, even when there is a
> value in it...
> Just check it out, I added the returned values...
> Anybody who can help with this would be reallt appreciated, i can't find
> anything about this on google :-(
> Pieter
> ***************************************** My Query
> CREATE TABLE #tmp
> (COLUMN_NAME nvarchar(100),DATA_TYPE varchar(20))
> INSERT INTO #tmp
> EXEC spSyncKeyColumns 'STOCK2'
> /* Make the cursor:*/
> DECLARE curC CURSOR
> FOR
> SELECT /*col.COLUMN_NAME, col.DATA_TYPE, */
> CASE col.DATA_TYPE
> WHEN 'char' THEN '(''' + col.COLUMN_NAME + ' = '' + ' + '''''''''' + ' +
> '
> + col.COLUMN_NAME + ' + ' + '''''''''' + ' )'
> WHEN 'nchar' THEN '(''' + col.COLUMN_NAME + ' = '' + ' + '''''''''' + ' +
> ' + col.COLUMN_NAME + ' + ' + '''''''''' + ' )'
> WHEN 'varchar' THEN '(''' + col.COLUMN_NAME + ' = '' + ' + '''''''''' + '
> + ' + col.COLUMN_NAME + ' + ' + '''''''''' + ' )'
> WHEN 'nvarchar' THEN '(''' + col.COLUMN_NAME + ' = '' + ' + '''''''''' +
> '
> + ' + col.COLUMN_NAME + ' + ' + '''''''''' + ' )'
> WHEN 'text' THEN '(''' + col.COLUMN_NAME + ' = '' + ' + '''''''''' + ' +
> '
> + col.COLUMN_NAME + ' + ' + '''''''''' + ' )'
> WHEN 'ntext' THEN '(''' + col.COLUMN_NAME + ' = '' + ' + '''''''''' + ' +
> ' + col.COLUMN_NAME + ' + ' + '''''''''' + ' )'
> WHEN 'uniqueidentifier' THEN '(''' + col.COLUMN_NAME + ' = '' + ' +
> '''''''''' + ' + ' + col.COLUMN_NAME + ' + ' + '''''''''' + ' )'
> WHEN 'datetime' THEN '(''' + col.COLUMN_NAME + ' = '' + ' + '''''''''' +
> '
> + CAST(CONVERT(datetime, ' + col.COLUMN_NAME + ', 126) AS NVARCHAR) + ' +
> '''''''''' + ' )'
> WHEN 'smalldatetime' THEN '(''' + col.COLUMN_NAME + ' = '' + ' +
> '''''''''' + ' + CAST(CONVERT(smalldatetime, ' + col.COLUMN_NAME + ',
> 126)
> AS NVARCHAR) + ' + '''''''''' + ' )'
> WHEN 'timestamp' THEN '(''' + col.COLUMN_NAME + ' = '' + ' +
> col.COLUMN_NAME + ' )'
> ELSE '(''' + col.COLUMN_NAME + ' = '' + CAST(' + col.COLUMN_NAME + ' AS
> NVARCHAR) )'
> END AS Syntax
> FROM #tmp col
> /* Open the Cursor*/
> OPEN curC
> /* Declaration of the variables*/
> DECLARE @.strSQL nvarchar(4000), @.strField NVARCHAR(100)
> SET @.strSQL = ''
> /*FIRST*/
> FETCH NEXT FROM curC INTO @.strField
> BEGIN
> SET @.strSQL = @.strField
> SELECT @.strField
> END
> /*THE OTHERS*/
> FETCH NEXT FROM curC INTO @.strField
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> SET @.strSQL = @.strSQL + ' + ' + ''') AND (''' + ' + ' + @.strField
> SELECT @.strField
> /* Take next Table*/
> FETCH NEXT FROM curC INTO @.strField
> END
> /* Fermes le Cursor*/
> CLOSE curC
> DEALLOCATE curC
> DROP TABLE #tmp
> SET @.strSQL = '( ''('' + ' + @.strSQL + ' + '')'' ) AS TWhere'
> SELECT COALESCE(@.strSQL, 'NULL AS TWhere')
> GO
> ***************************************** End of My Query
>
> ***************************************** The Results:
> (5 row(s) affected)
>
> ----
> --
> ('CODE_SOCIETE = ' + '''' + CODE_SOCIETE + '''' )
> (1 row(s) affected) -> My First @.strField
>
> ----
> --
> ('ID_TYPE = ' + CAST(ID_TYPE AS NVARCHAR) )
> (1 row(s) affected) -> My 2nd @.strField
>
> ----
> --
> ('NUM_SITE = ' + CAST(NUM_SITE AS NVARCHAR) )
> (1 row(s) affected) -> My 3nd @.strField
>
> ----
> --
> ('ID_CONTAINER = ' + CAST(ID_CONTAINER AS NVARCHAR) )
> (1 row(s) affected) -> My 4th @.strField
>
> ----
> --
> ('DATE_TIME = ' + DATE_TIME )
> (1 row(s) affected) -> My 5th @.strField
>
> ----
> ----
> ----
> --
> ( '(' + ('CODE_SOCIETE = ' + '''' + CODE_SOCIETE + '''' ) + ') AND (' +
> ('ID_TYPE = ' + CAST(ID_TYPE AS NVARCHAR) ) + ') AND (' + ('NUM_SITE = ' +
> CAST(NUM_SITE AS NVARCHAR) ) + ') AND (' + ('ID_CONTAINER = ' +
> CAST(ID_CONTAINER AS NVARCHAR) ) + ') AND ('
> (1 row(s) affected) -> The final @.strSQL... As you can see: the
> 5th @.strField isn't added, although the ") AND (" is added...
>
> Can anybody help me with this absolutely weird stuff? Thank a lot in
> advance!!
>
>
|||Yes but is not null! As you can see the 5th time is has a value!
"Jens Smeyer" <Jens@.[Remove_that][for contacting me]sqlserver2005.de>
wrote in message news:5B086194-C01B-4016-95DD-76B2438BA400@.microsoft.com...[vbcol=seagreen]
> When using string concatenation you should always keep in mind the option
> that a column / variable can be null --> ISNULL(SomeColumn,'')
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "DraguVaso" wrote:
concatenation[vbcol=seagreen]
imited[vbcol=seagreen]
everytime[vbcol=seagreen]
"[vbcol=seagreen]
+ '[vbcol=seagreen]
' +[vbcol=seagreen]
+ '[vbcol=seagreen]
+ '[vbcol=seagreen]
+ '[vbcol=seagreen]
' +[vbcol=seagreen]
+ '[vbcol=seagreen]
+[vbcol=seagreen]
126)[vbcol=seagreen]
AS
> ----
--
> ----
--
> ----
--
> ----
--
> ----
--
> ----
--
> ----
--
> ----
--[vbcol=seagreen]
+[vbcol=seagreen]
the[vbcol=seagreen]
|||Well, I want to concatenated all those things, to create the WHERE clause of
a SELECT-query.
It's kidn of complicated the whole project: I'm making this for some kind of
replication-database: When Deleteing a record, the delete-trigger makes me
the WHERE clause of the delete-statement. I need that delete-statement to go
delete the same record in another database :-)
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uaIHWazlFHA.3256@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Dragu
> What do you want to accomplish?
>
> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
> news:OOZz9SzlFHA.2472@.TK2MSFTNGP15.phx.gbl...
imited[vbcol=seagreen]
"[vbcol=seagreen]
+[vbcol=seagreen]
+[vbcol=seagreen]
'[vbcol=seagreen]
+[vbcol=seagreen]
+[vbcol=seagreen]
+[vbcol=seagreen]
+[vbcol=seagreen]
+
> ----
--
> ----
--
> ----
--
> ----
--
> ----
--
> ----
--
> ----
--
> ----
--[vbcol=seagreen]
+[vbcol=seagreen]
the
>
|||Hi There,
Ther is nothing wrong with the approach . You should check the
settings under
Tools ->Options -->Results(Tab) -->Maximum Characters per Column set it
to 4000 or so.
I hope it will solve your problem.
With warm regards
Jatinder Singh
|||Thanks! that did indeed the trick!!
Damn, Ihave been searching for hours on this problem :-(
Really thanks a lot!
<jatinder.singh@.clovertechnologies.com> wrote in message
news:1122970837.535624.191310@.o13g2000cwo.googlegr oups.com...
> Hi There,
> Ther is nothing wrong with the approach . You should check the
> settings under
> Tools ->Options -->Results(Tab) -->Maximum Characters per Column set it
> to 4000 or so.
> I hope it will solve your problem.
> With warm regards
> Jatinder Singh
>