Run a SQL Server Job to process multiple Stored Procedures with the same Date Parameters

How can I create a batch file to:

Prompt the user to enter a Start and End date variable
Process multiple SQL stored procedures that use the stored date variables (@From & @ Thru)


Who is Participating?
Jim P.Connect With a Mentor Commented:
Based on using the SQLCmd line tool the batch file would look something like this:

@echo off
color 1e
cd "C:\Program Files\Microsoft SQL Server\100\Tools\Binn"
set /p StartDate= Please enter the start date as YYYY-MM-DD? 
set /p EndDate= Please enter the end date as YYYY-MM-DD? 
set SrvrName=MyServer\MyInstance
set DBName=DatabaseName
set CmdStr="exec MyStoredProc1 @StartDate='%StartDate%', @EndDate='%EndDate%'"
SQLCMD.EXE -S %SrvrName% -d %DBName% -E -Q %CmdStr%
set CmdStr="exec MyStoredProc2 @StartDate='%StartDate%', @EndDate='%EndDate%'"
SQLCMD.EXE -S %SrvrName% -d %DBName% -E -Q %CmdStr%

Open in new window

Microsoft (R) SQL Server Command Line Tool
Version 10.50.1600.1 NT x64
Copyright (c) Microsoft Corporation.  All rights reserved.

usage: Sqlcmd            [-U login id]          [-P password]
  [-S server]            [-H hostname]          [-E trusted connection]
  [-N Encrypt Connection][-C Trust Server Certificate]
  [-d use database name] [-l login timeout]     [-t query timeout]
  [-h headers]           [-s colseparator]      [-w screen width]
  [-a packetsize]        [-e echo input]        [-I Enable Quoted Identifiers]
  [-c cmdend]            [-L[c] list servers[clean output]]
  [-q "cmdline query"]   [-Q "cmdline query" and exit]
  [-m errorlevel]        [-V severitylevel]     [-W remove trailing spaces]
  [-u unicode output]    [-r[0|1] msgs to stderr]
  [-i inputfile]         [-o outputfile]        [-z new password]
  [-f <codepage> | i:<codepage>[,o:<codepage>]] [-Z new password and exit]
  [-k[1|2] remove[replace] control characters]
  [-y variable length type display width]
  [-Y fixed length type display width]
  [-p[1] print statistics[colon format]]
  [-R use client regional setting]
  [-b On error batch abort]
  [-v var = "value"...]  [-A dedicated admin connection]
  [-X[1] disable commands, startup script, enviroment variables [and exit]]
  [-x disable variable substitution]
  [-? show syntax summary]

Open in new window

This should cover most of your questions regrading Batch files

BTW, have you considered using VBS or another scripting language instead of Batch files?
GPSPOWAuthor Commented:

Works great

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.