Concat bind variables in insert

Discussion forum about all things SqlBase.
Maria

Concat bind variables in insert

Post by Maria » 07 Oct 2014, 13:17

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
India
Posts: 2176
Joined: 09 Mar 2017, 05:32
Location: Hyderabad, India

Re: Concat bind variables in insert

Post by FRBhote » 08 Oct 2014, 06:13

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

RainerE
Germany
Posts: 2049
Joined: 27 Apr 2021, 12:40
Location: Germany

Re: Concat bind variables in insert

Post by RainerE » 08 Oct 2014, 09:12

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
Germany
Posts: 191
Joined: 06 Mar 2017, 07:27
Location: Frankfurt, Germany

Re: Concat bind variables in insert

Post by Christof » 08 Oct 2014, 14:16

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?

User avatar
markus.essmayr
Site Admin
Site Admin
Austria
Posts: 892
Joined: 06 Mar 2017, 06:07
Location: Austria

Re: Concat bind variables in insert

Post by markus.essmayr » 14 Oct 2014, 10:59

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
Markus Eßmayr
teamdeveloper@t-mx.com

Return to “General Discussion”

Who is online

Users browsing this forum: [Ccbot] and 0 guests