Execute Sql Server stored procedure

Hi everyone,

I’m trying to insert/update data into a Sql Server table using a stored procedure.
If I do an direct insert into the table all the records are correctly inserted.
If I use a Execute Query with a stored procedure only the first record is processed.
Anyone know a solution for this scenario?
Below is the workflow I’m using
Beste regards

{
“nodes”: [
{
“parameters”: {},
“name”: “Start”,
“type”: “n8n-nodes-base.start”,
“typeVersion”: 1,
“position”: [
-3300,
-1750
]
},
{
“parameters”: {
“operation”: “executeQuery”,
“query”: “select isnull(max(project_updated_on), ‘1900-01-01’) as startDate\nFROM usrLeanProjects\n”
},
“name”: “Get last datetime”,
“type”: “n8n-nodes-base.microsoftSql”,
“typeVersion”: 1,
“position”: [
-3150,
-1750
],
“credentials”: {
“microsoftSql”: “Emp_ingpt”
}
},
{
“parameters”: {
“operation”: “executeQuery”,
“query”: “=select \tprojects.id as project_id, \n\t\tprojects.name as project_name, \n\t\tprojects.identifier as project_identifier, \n\t\tIFNULL(projects.status, 0) as project_status, \n\t\tcustom_values.value as project_centro_custo,\n\t\tprojects.created_on as project_created_on, \n\t\tprojects.updated_on as project_updated_on,\n \t\tIFNULL(parent_id, 0) as project_parent_id\nfrom \tprojects left outer join custom_values on \n\t\tprojects.id = custom_values.customized_id\nwhere \tcustom_values.custom_field_id = 6 and projects.updated_on > ‘{{$json[“startDate”]}}’”
},
“name”: “Get projects modified since”,
“type”: “n8n-nodes-base.mySql”,
“typeVersion”: 1,
“position”: [
-2850,
-1750
],
“alwaysOutputData”: false,
“credentials”: {
“mySql”: “MySQL Lean stable”
}
},
{
“parameters”: {
“functionCode”: “return item;”
},
“name”: “FunctionItem”,
“type”: “n8n-nodes-base.functionItem”,
“typeVersion”: 1,
“position”: [
-3000,
-1750
]
},
{
“parameters”: {
“operation”: “executeQuery”,
“query”: “=usrInsertUpdateProjects {{$json[“project_id”]}},\n’{{$json[“project_name”]}}’,\n’{{$json[“project_identifier”]}}’,\n{{$json[“project_status”]}},\n’{{$json[“project_centro_custo”]}}’,\n’{{$json[“project_created_on”]}}’,\n’{{$json[“project_updated_on”]}}’,\n{{$json[“project_parent_id”]}}”
},
“name”: “Insert into Projects”,
“type”: “n8n-nodes-base.microsoftSql”,
“typeVersion”: 1,
“position”: [
-2550,
-1750
],
“alwaysOutputData”: false,
“retryOnFail”: false,
“credentials”: {
“microsoftSql”: “Emp_ingpt”
}
},
{
“parameters”: {
“functionCode”: “return items;”
},
“name”: “Function before insert”,
“type”: “n8n-nodes-base.function”,
“typeVersion”: 1,
“position”: [
-2700,
-1750
]
}
],
“connections”: {
“Start”: {
“main”: [
[
{
“node”: “Get last datetime”,
“type”: “main”,
“index”: 0
}
]
]
},
“Get last datetime”: {
“main”: [
[
{
“node”: “FunctionItem”,
“type”: “main”,
“index”: 0
}
]
]
},
“Get projects modified since”: {
“main”: [
[
{
“node”: “Function before insert”,
“type”: “main”,
“index”: 0
}
]
]
},
“FunctionItem”: {
“main”: [
[
{
“node”: “Get projects modified since”,
“type”: “main”,
“index”: 0
}
]
]
},
“Function before insert”: {
“main”: [
[
{
“node”: “Insert into Projects”,
“type”: “main”,
“index”: 0
}
]
]
}
}
}

What you could do is use a ‘Split in Batches’ node before your SQL Node:
Screenshot 2021-10-21 at 10.09.26

You should set the batch size to 1 to make sure that it loops over every record in your node before. Don’t forget to connect the end of your SQL node to the start of the Split in Batches node.

I am quite curious why the execute for every-record functionality doesn’t seem to work for the ‘executeQuery’ mode of the SQL node.

1 Like

It is worth making a note of this page as well: Looping in n8n | Docs

It lists all the nodes where you may need to use a loop.

1 Like

Thank you for the suggestion @jon.

Best regards
Armando

Thank you @laurens-novyx, that solved my problem!!

Best regards
Armando

1 Like