March 10, 2011 at 11:24 am
I have a query to update a table:
UPDATE USERACCOUNT
SET USERPASSWORD = (SELECT USERPASSWORD
FROM USERACCOUNT
WHERE USERNAME='JOHN SMITH' AND INSTITUTION = '1')
WHERE USERNAME='JOHN SMITH'
I was wondering if there's a way that I could do a .bat file that person can run -- which runs this query -- but prompts the person to input the USERNAME = and INSTITUTION =. So me or anyone else can run this query and not have to edit the USERNAME= and INSITUTION= everytime with someone new. And it can be run without having to open SQL everytime.
Thanks
March 10, 2011 at 12:28 pm
It can be done using a couple Windows Batch commands to accept the user's input into StdIn and by calling sqlcmd.exe to execute the SQL. I woudl recommend putting your UPDATE into a stored procedure and calling that to protect yourself a bit from SQL injection since you'll be accepting free-form text entries.
The exact syntax for your Batch file will depend on which version of the OS you're planning on implementing on. Bing/Google and some trial and error will be your friend on that one.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 11, 2011 at 3:04 pm
This might help
@echo off
CLS
set /p UserName=User Name:
set /p Institution =Institution:
set Args=
set Args= -q "SET NOCOUNT ON UPDATE USERACCOUNT SET USERPASSWORD = (SELECT 'ER' FROM USERACCOUNT WHERE USERNAME='%UserName%' AND INSTITUTION = '%Institution%') WHERE USERNAME ='%UserName%'"
OSQL.EXE -n -E -w 65536 -d balatest %Args% -i "%~f0"
April 26, 2011 at 1:39 pm
Thank you Bala! That worked great!
🙂
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply