Concat bind variables in insert

[Maria]

Hi all,
I need to do following:
In SQLServer Management Studio I write like this:

INSERT INTO mytable
(col1)
VALUES (
('Maria_'+cast(NEWID( ) as varchar(100)))
)

This works fine.

My question is how to write this in TD. 'Maria_' is a string variable.

SqlPrepare('INSERT INTO mytable
(col1)
VALUES (
(:strName +cast(NEWID( ) as varchar(100)))
')

doesn't work..


If I only would have wanted a GUID in col1 it works fine:
SqlPrepare('INSERT INTO mytable
(col1)
VALUES
((cast(NEWID()as varchar(100)))
')

but how do I concat this with :strName?

//Maria

[FRBhote]

SqlPrepare( " 'INSERT INTO mytable
(col1)
VALUES (
('" || strName || "'+cast(NEWID( ) as varchar(100)))
" )

[RainerE]

In Gupta SQLBase you concatenate strings by || (written als \|\| in TD).
I don't know if this also works in TD for Sql Server?
Regards,
Rainer

[Christof]

You could build your SQL as a pure literal like this

Code: Select all

"INSERT INTO mytable
(col1)
VALUES (
( '" || strName || "' + cast(NEWID( ) as varchar(100))
)"

...but you would loose the advantages of a precompiled statement.

Are you sure that using a bind variable doesn't work with SQLServer? Which error msg do you get?

[markus.essmayr]

Do you get any error when calling

Code: Select all

SqlPrepare('INSERT INTO mytable
 (col1) 
VALUES (
(:strName +cast(NEWID( ) as varchar(100)))
')

What about something like

Code: Select all

SqlPrepare('INSERT INTO mytable
 (col1) 
VALUES (
(LTRIM(:strName) +cast(NEWID( ) as varchar(100)))
')

Max