SQL About AMO: weird

Showing posts with label weird. Show all posts

Thursday, March 8, 2012

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 Süßmeyer" <Jens@.[Remove_that][for contacting me]sqlserver2005.de>
wrote in message news:5B086194-C01B-4016-95DD-76B2438BA400@.microsoft.com...
> 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!!
> >
> >
> >
> >
> >|||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...
> 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!!
> >
> >
> >
> >
>|||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.googlegroups.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
>

Thursday, February 9, 2012

about CTE in SQL 2k5

Hi all,
I tested a new feature of SQL2005 (named cte for recursive selection)
and found the order of the result was weird.
According to the description of help:
1. Split the CTE expression into anchor and recursive members.
2. Run the anchor member(s) creating the first invocation or base
result set (T0).
3. Run the recursive member(s) with Ti as an input and Ti+1 as an
output.
4. Repeat step 3 until an empty set is returned.
5. Return the result set. This is a UNION ALL of T0 to Tn.
But the result was just like the stack-calls.
Is there something wrong on the description of the help?
table creation script:
CREATE TABLE tbUser(
userId int NOT NULL,
mgrId int NULL,
CONSTRAINT [PK_tbUser] PRIMARY KEY CLUSTERED
(
[userId] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
table values:
insert tbUser values(1,null)
insert tbUser values(2,1)
insert tbUser values(3,2)
insert tbUser values(4,2)
CTE scripts
WITH reps_cte (userId, mgrId, recursion_level)
AS
(
SELECT userId, mgrId, 0 FROM tbUser
UNION ALL
SELECT reps_cte.userId, tbUser.mgrId, recursion_level+1 --get the
higher level of the mgr
FROM reps_cte inner join tbUser -- Join with Employee
on reps_cte.mgrId= tbUser.userId -- This employee's manager
where recursion_level<=20 -- up to 20 levels of mgmt
)
Result:
1 NULL 0
2 1 0
3 2 0
4 2 0
4 1 1
4 NULL 2
3 1 1
3 NULL 2
2 NULL 1
Expecting result:
1 NULL 0 - T0 result
2 1 0 - T0 result
3 2 0 - T0 result
4 2 0 - T0 result
2 NULL 1 - T1 result
3 1 1 - T1 result
4 1 1 - T1 result
3 NULL 2 - T2 result
4 NULL 2 - T2 result
Regards,
congling"congling" <congling@.hotmail.com> wrote in message
news:OK9CPzwAGHA.1312@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> I tested a new feature of SQL2005 (named cte for recursive selection)
> and found the order of the result was weird.
> According to the description of help:
> 1. Split the CTE expression into anchor and recursive members.
> 2. Run the anchor member(s) creating the first invocation or base
> result set (T0).
> 3. Run the recursive member(s) with Ti as an input and Ti+1 as an
> output.
> 4. Repeat step 3 until an empty set is returned.
> 5. Return the result set. This is a UNION ALL of T0 to Tn.
> But the result was just like the stack-calls.
> Is there something wrong on the description of the help?
>
The order of a result is always undefined unless the query contains an ORDER
BY clause. The BOL has this description under the heading of "Pseudocode
and Semantics", and it's is a logical description of the result set, not a
guarantee about how it is processed or the order in which the rows are
returned.
David|||congling wrote:
> Hi all,
> I tested a new feature of SQL2005 (named cte for recursive selection)
> and found the order of the result was weird.
> According to the description of help:
> 1. Split the CTE expression into anchor and recursive members.
> 2. Run the anchor member(s) creating the first invocation or base
> result set (T0).
> 3. Run the recursive member(s) with Ti as an input and Ti+1 as an
> output.
> 4. Repeat step 3 until an empty set is returned.
> 5. Return the result set. This is a UNION ALL of T0 to Tn.
> But the result was just like the stack-calls.
> Is there something wrong on the description of the help?
>
> table creation script:
> CREATE TABLE tbUser(
> userId int NOT NULL,
> mgrId int NULL,
> CONSTRAINT [PK_tbUser] PRIMARY KEY CLUSTERED
> (
> [userId] ASC
> )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
> ) ON [PRIMARY]
> table values:
> insert tbUser values(1,null)
> insert tbUser values(2,1)
> insert tbUser values(3,2)
> insert tbUser values(4,2)
> CTE scripts
> WITH reps_cte (userId, mgrId, recursion_level)
> AS
> (
> SELECT userId, mgrId, 0 FROM tbUser
> UNION ALL
> SELECT reps_cte.userId, tbUser.mgrId, recursion_level+1 --get the
> higher level of the mgr
> FROM reps_cte inner join tbUser -- Join with Employe
e
> on reps_cte.mgrId= tbUser.userId -- This employee's manag
er
> where recursion_level<=20 -- up to 20 levels of mgmt
> )
> Result:
> 1 NULL 0
> 2 1 0
> 3 2 0
> 4 2 0
> 4 1 1
> 4 NULL 2
> 3 1 1
> 3 NULL 2
> 2 NULL 1
> Expecting result:
> 1 NULL 0 - T0 result
> 2 1 0 - T0 result
> 3 2 0 - T0 result
> 4 2 0 - T0 result
> 2 NULL 1 - T1 result
> 3 1 1 - T1 result
> 4 1 1 - T1 result
> 3 NULL 2 - T2 result
> 4 NULL 2 - T2 result
>
> Regards,
> congling
If you don't specify ORDER BY then the sorting of the result is
undefined. Try:
WITH reps_cte (userid, mgrid, recursion_level)
AS
(
SELECT userId, mgrId, 0 FROM tbUser
UNION ALL
SELECT reps_cte.userId, tbUser.mgrId, recursion_level+1
FROM reps_cte inner join tbUser
on reps_cte.mgrId= tbUser.userId
where recursion_level<=1
)
SELECT userid, mgrid, recursion_level
FROM reps_cte
ORDER BY recursion_level, mgrid, userid ;
David Portas
SQL Server MVP
--|||thx
Regards,
congling
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> д?:%23CcF$FxAGHA.20
40@.TK2MSFTNGP14.phx.gbl...
> "congling" <congling@.hotmail.com> wrote in message
> news:OK9CPzwAGHA.1312@.TK2MSFTNGP09.phx.gbl...
> The order of a result is always undefined unless the query contains an
> ORDER BY clause. The BOL has this description under the heading of
> "Pseudocode and Semantics", and it's is a logical description of the
> result set, not a guarantee about how it is processed or the order in
> which the rows are returned.
> David
>

Thursday, March 8, 2012

absolutely weird String Concatenation problem, and really daring!

absolutely weird String Concatenation problem, and really daring!

absolutely weird String Concatenation problem, and really daring!

Thursday, February 9, 2012

about CTE in SQL 2k5

SQL About AMO

Blog Archive

About Me