Shell Programming and Scripting

HI,

I am calling a stored procedure to archieve records from the DB based on a date condition. The syntax for the same is as below:
shell script - xyz.sh

Code:

 sqlcmd -s<servername> -U <username> -p<password> -I <embedded SQL input file>

The contents of the Embedded SQL INput file are as below

Code:

exec <SP Name> '$date_var'

Query 1: Can someone please advice how can I pass the date through the shell script so that it can be passed to the Embedded SQL input file's Date_var variable during the runtime.

Query 2: Also, I want to check if the user has passed the date paramaeter or else will use the system date to delete the records.

I am thinking of the below code for the same

Code:

 if [ $# -eq 0 ]; then
    echo "No arguments provided"
      set date_var='$(date)'
     else 
      set date_var=$1
fi

Is the above sysntax correct. I want to pass the date in yyyy-mm-dd format.
Would appreciate if a prompt response is provided

Thanks in advance!

Is there a -v switch for sqlcmd, see man pages. What is the operating system and sql version.

Hi Blackrageous,

Thanks for the reply!

it's SQL SERVER 2008 R2 version.

I will check on the -v switch and let you know. But my doubt is how to pass the date from the unix script to the input file containing the sqlcmd command.

Code:

 
 ./XYZ.sh '2014-11-06'

XYZ.sh

Code:

 
 sqlcmd -s<servername> -U <username> -p<password> -I <embedded SQL input file>

Input File

Code:

 
 exec <SP Name> '$date_var'

I want to pass the date '2014-11-06' to date_var

I gather you are using the sqlcmd client on the UNIX side. The sqlcmd -v switch (at least on the dos version is for the purpose of passing variables into the embeddeded SQL input file.

Please confirm that you are running sqlcmd on unix. If so what operating system / version of Unix?

Usually, you choose a string format that is compatible, as passing binary date-time is a compiled code deal, not scripting. I like "YYYY-mm-dd HH:MM:SS.ssssss" but your SQL engine may vary. Sometimes you need a conversion tool like a CONVERT() function.

HI Blackrageous,

Yes, I am passing the date variable through unix. We are using Linux version.

I am able to pass the variable but still will need suggestions in case of improvements.

- passed the date while executing the shell as a argument.
- Checked whether the date is passed or else moved the environment variable which contains a date in format yyyy-mm-dd. THis will go into variable_passed_date1 local variable
- used the export variable command for variable passed_date1.
Now, I am able to use varaiable_passed_date1 in the input SQLCMD sql file.
- Also, in the SQL server SP, I have defined the argument as datetime and using the CONVERT function to convert it into YYYY-mm-dd HH:MM:SSS (something of this format).

This is working, but the HH:MM:SS.SSS is 0 and I want it to be set as a high value so that all records of the particular date are captured.

---------- Post updated at 11:33 PM ---------- Previous update was at 11:31 PM ----------

Thanks for the reply!

I have declared the date variable as date and later converting it into datetime using CONVERT. This is working fine now.

Yes, date-time and the BETWEEN clause do not get along. The cleanest thing is to say "DTColName >= YourDate AND DTColName < YourDateNext" so trailing nanoseconds do not fall into a hole. Ditto for "find ... -newer X ! -newer Y" and file dates! You do not want the overhead and index-disabling "CONVERT(DTColName,Date) = YourDate" (although some RDBMS allow you to index function expressions!)