Passing Parameters to SQLCMD from Shell Script

Question

Hi,

We are adopting to SQL Server 2019. We have a requirement to invoke SQL Scripts to invoke Stored procedures that have parameters using Shell script. To be precise it has one Input and two Output parameters.

However, when I try to use the combination of -Q and -v options to define the Execute command and passing the variables, it is not working.

1. Shell script gets the value for v_file_name as input parameter Here is how I am trying:
   $path_to_bin/sqlcmd -U ${user_name} -P ${password} -S ${sqlserver_ip_address} -d ${database_name} -Q "EXECUTE PROCESS_STG_DATA v_file_name @ERROR_CODE OUT @ERROR_TEXT OUT;" -v v_file_name=${file_name} P_ERR_CD=0 P_ERR_TXT=''>>$path_to_log/LOG_FILE.LOG
2. Below is the query:
   EXECUTE PROCESS_STG_DATA @v_file_name, @ERROR_CODE OUT, @ERROR_TEXT OUT;

This is not working. Had tried multiple combinations. It would be of great help if someone had already tried this option and share their experiences.

Regards,
Phaneendra

Answer 1

Maybe this:

$path_to_bin/sqlcmd -U ${user_name} -P ${password} -S ${sqlserver_ip_address} -d ${database_name} -Q "DECLARE @ERROR_CODE int, @ERROR_TEXT nvarchar(2048) EXECUTE PROCESS_STG_DATA '$(v_file_name)' @ERROR_CODE OUT @ERROR_TEXT OUT;" -v v_file_name=${file_name} SELECT @ERROR_CODE, @ERROR_TEXT" P_ERR_TXT=''>>$path_to_log/LOG_FILE.LOG

Some observations:

• You must declare the variables for the output parameters in the SQL batch.
• To refer to the variable defined with the -v option, you use $(). (I hope that the Unix shell does not react on the $.)
• I don't know what there P_ERR_CD and P_ERR_TEXT were supposed to be. They did not make any sense with the T-SQL syntax I deleted.
• Instead I added a SELECT of the two variables. That is the only way you can get back the values from SQLCMD.