Skip to content
+1-888-319-3663

COMMUNITY FORUM

SQL Command – Insert Into using variable

MSL asked 6 years ago
Hi,
 
I’m trying to execute a sql insert from sql command before the map.
It seems that it doesn’t interpret the variable.
If I use a string it works, if I use a variable it not working.
What is the good syntax to use the variable in a SQL Command ?
 Not working :
IF NOT EXISTS(SELECT custnmbr FROM rm00101 WHERE custnmbr = _CUSTID)
BEGIN
                INSERT INTO [dbo].[RM00101] ([CUSTNMBR],[CUSTNAME]) VALUES (_CUSTID, ‘TBD’)
END
 
Working :
IF NOT EXISTS(SELECT custnmbr FROM rm00101 WHERE custnmbr = ‘AD’)
BEGIN
                INSERT INTO [dbo].[RM00101] ([CUSTNMBR],[CUSTNAME]) VALUES (‘AD’, ‘TBD’)
END


 
 
 
 
 
Answers
Patrick Roth Staff answered 6 years ago
MSL,
In your first statement with the _CUSTID, the issue might be that you didn’t wrap it in single ticks.  When SC replaces the variables, it would replace it with perhaps AARONFIT0001.
Because it isn’t wrapped in single quotes to be a literal, I would expect the SQL statement to fail because the column “AARONFIT0001” doesn’t exist.
But more than likely that isn’t the issue – you’d need a SQL trace to confirm.
But the issue is – on the “before map” task SmartConnect hasn’t pulled the source data yet.  And even if it did and you had multiple rows of source data – how would SC know what to put in “_CUSTID”?
If you need to create customers, you should use  a Create Customer task in your map before whatever you need to do (create trx etc).
Patrick
eOne
MSL answered 6 years ago
Oups, not ‘Before Map’, It’ was ‘Before Document’
I tried to create the customer in the map SOP Transaction Type, but when the customer already exists, I receive an error. If we check ‘Update if exists’, it updates all the customer information. I have only the customer id and the customer name.
MSL
Alberto Pinal replied 6 years ago

You can try to put your varaible between single quotes:

IF NOT EXISTS(SELECT custnmbr FROM rm00101 WHERE custnmbr = ‘_CUSTID’)
BEGIN
INSERT INTO [dbo].[RM00101] ([CUSTNMBR],[CUSTNAME]) VALUES (‘_CUSTID’, ‘TBD’)
END

Hope it can help you!

MSL answered 6 years ago
Hi,
I found the solution after 111 tests of different syntax…
Enjoy the future now…
MSL
DECLARE @tCust varchar(100)
DECLARE @sql_str varchar(300)
DECLARE @tAdrCode varchar(100)
SET @tcust=  ‘_CUSTID’
SET @tAdrCode = ‘_ADRCODE’
SET @sql_str = N’IF NOT EXISTS(SELECT custnmbr FROM RM00101 WHERE custnmbr = ”’ + @tCust + ”’) INSERT INTO RM00101 ([CUSTNMBR],[CUSTNAME],[SHRTNAME], [STMTNAME], [ADRSCODE]) VALUES (”’ + @tCust + ”’,”’ + @tCust + ”’,”’ + @tCust + ”’,”’ + @tCust + ”’,”’ + @tAdrCode + ”’)
–Print(@sql_str)
Exec(@sql_str)
 
 
 
 
 
 
 
 
 


DECLARE @tCust varchar(100)


DECLARE @sql_str varchar(300)


DECLARE @tAdrCode varchar(100)


SET @tcust=  ‘_CUSTID’


SET @tAdrCode = ‘_ADRCODE’


SET @sql_str = N’IF NOT EXISTS(SELECT custnmbr FROM RM00101 WHERE custnmbr = ”’ + @tCust + ”’) INSERT INTO RM00101 ([CUSTNMBR],[CUSTNAME],[SHRTNAME], [STMTNAME], [ADRSCODE]) VALUES (”’ + @tCust + ”’,”’ + @tCust + ”’,”’ + @tCust + ”’,”’ + @tCust + ”’,”’ + @tAdrCode + ”’)


 


–SET @sql_str = N’IF NOT EXISTS(SELECT custnmbr FROM RM00101 WHERE custnmbr = ”’ + @tCust + ”’) INSERT INTO RM00101 ([CUSTNMBR],[CUSTNAME]) VALUES (”’ + @tCust + ”’,”’ + @tCust + ”’)’


–Print(@sql_str)
Exec(@sql_str)
 
 
 
 

If you would like to submit an answer or comment, please sign in to the eOne portal.