DosTips.com
for Windows10,Windows8,Windows7,VISTA,
XP,NT,Server 2000,Server 2003,Server 2008


OSQL.EXE - Run SQL script from DOS Batch, passing parameters

Run SQL scripts with parameters passed in from the batch script.

Description:

Now we can embed SQL queries into a batch file. But how can we pass arguments from the batch script into SQL? This can be done using a temporary table. Temporary tables live as long as the connection to the SQL Server. But how can we fill a temporary table with runtime data and execute the embedded SQL script without creating two separate SQL Server connections by calling OSQL.EXE twice? The trick is that OSQL allows to use the -i and -q option at the same time whereas:

  • -q specifies a query string to be executed
  • -i specifies a filename with SQL syntax to be executed
Both the query string and the SQL file will be executed using the same Server connection. Some testing shows that the query string -q will always be executed before the -I SQL file, which allows us to use a query string to set up a temporary table.

Script: Download: Batch4SQL2.bat  
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
 GOTO START
-- DOS jumps to the ':START' label
-- SQL jumps to the 'START:' label
-- Source https://www.dostips.com

/* Begin of SQL comment, this makes the BATCH script invisible for SQL
:: BATCH starts below here
:START
@echo off
CLS

set /p AuthorName=Author Name:
set /p AuthorCity=Author City:

rem.-------------------------------------------------------------------------
rem.Execute THIS file in SQL context transmitting arguments per temp table
set Args=

rem.--BEGIN ARGS SECTION ------------------
set Args=%Args% INSERT #ArgsTable VALUES ('AuthorName','%AuthorName%')
set Args=%Args% INSERT #ArgsTable VALUES ('AuthorCity','%AuthorCity%')
rem.--END   ARGS SECTION ------------------

set Args= -q "SET NOCOUNT ON CREATE TABLE #ArgsTable(Arg char(16) PRIMARY KEY, Val char(32)) %Args%"

OSQL.EXE -n -E -w 65536 -d NORTHWIND %Args% -i "%~f0"

PAUSE&GOTO:EOF
*/

-- SQL starts below here
START:
GO

DECLARE @AuthorName varchar(128)
DECLARE @AuthorCity varchar(128)

--Set defaults for values as needed
SET @AuthorName = ''
SET @AuthorCity = ''

--Copy data from temporary table into variables
IF EXISTS (SELECT name, type FROM tempdb..sysobjects WHERE name like '#ArgsTable_%' AND type = 'U')
BEGIN
    SELECT @AuthorName=Val FROM #ArgsTable  WHERE Arg='AuthorName'
    SELECT @AuthorCity=Val FROM #ArgsTable  WHERE Arg='AuthorCity'
END

print @AuthorName
print @AuthorCity

GO