Passing Parameters to SQL Scripts

Question

Post reply

Passing Parameters to SQL Scripts

RJ-476420

SSCarpal Tunnel

Points: 4806
More actions
July 8, 2008 at 1:57 pm

#122352

Hi,
I have a SQL Script & I would like to pass parameters to it. Is this a possible case with SQL Server 2000?
~Thanks

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

cy-dba SSCarpal Tunnel Points: 4179 More actions · Answer 1

Stored procs and functions accept parameters. Take a look at BOL to learn more.

RJ-476420 SSCarpal Tunnel Points: 4806 More actions · Answer 2

Thanks. But, this is a one time script & this cannot be created as procedures or functions as this would be a script like patches given to different users to run them once.

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 3

Ummm... the best thing to do would be to create a stored proc and send the users instructions on how to run it with the correct parameters.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

cy-dba SSCarpal Tunnel Points: 4179 More actions · Answer 4

I agree. Even if it is a one-time deal, it's more secure to have them run a stored procedure vs. script. When they're done, just drop the procedure. The little extra work is better than potential greater problems.

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 5

Heh... if more than one user has to use it even just once, it's not "one time"... 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RJ-476420 SSCarpal Tunnel Points: 4806 More actions · Answer 6

well! Each user has their own database & server. Not all users use common server.

MannySingh SSChampion Points: 12465 More actions · Answer 7

Can you post a small detail about what you are trying to do in the script...

Maninder
www.dbanation.com

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 8

RJ (7/9/2008)
well! Each user has their own database & server. Not all users use common server.

So, you're going to send out scripts customized for each user?

You can write a batch file that will run the script from OSQL using "parameters" they type in after the batch file command as environment variables.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RJ-476420 SSCarpal Tunnel Points: 4806 More actions · Answer 9

Our application have 3 databases. Users name all these databases with their own name. As we are moving from present database version to newer database version we are sending all users the patches to update schema, data, & so on. I'm trying to use osql in a batch file but i want to get the inputs from the user for the database name and I want to use only a single SQL file to update all 3 databases (i think a single sql file would be easy for the users to follow the instructions). So, I'm trying to figure out a way wherein I can pass all 3 database names as parameters to the SQL Script through OSQL.

Moreover, I would also be wrappring these SQL Scripts inside a .Net or VBScript file to make it an executable file. And, as of now I have to pass only the database names as parameters but I may need to pass some more parameters as I keep working on these patches.

Thanks for all your suggestions.