August 21, 2005 at 6:47 pm
Hi,
I have a T-SQL script that sets up a named database and various other bits and pieces.
I'd like to be able to call this script multiple times with different database names.
Is it possible to pass parameters into T-SQL scripts?
(Something like: osql -i myscript.sql parameter-1 parameter-2)
Currently I declare a variable for the database name and modify it with
an appropriate value each time I need to run the script.
And if anyone has a good websites/resources on SQL Server programming that they can point me to, I'd appreciate it...
Thank you,
Lucy (new to MS SQL Server)
August 22, 2005 at 3:17 am
Hi,
It should be easy to create a stored procedure that accepts a parameter from your script.
If you post the full script, i'm sure we can point you in the right direction.....
As for websites:-
http://www.sql-server-performance.com/
http://www.karaszi.com/sqlserver/
But i'm afraid you wont beat this one.........
Have Fun
Steve
We need men who can dream of things that never were.
August 22, 2005 at 5:35 pm
Hi,
This is my T-SQL script:
project.sql:
------------------------------------------------------------------------------
-- Creates a database for a project
DECLARE @project_name NVARCHAR(520)
DECLARE @device_directory NVARCHAR(520)
SET @project_name = 'banking'
SET @device_directory = 'D:\SQLEVAL\MSSQL\Data\'
-- Create the database
EXEC(N'CREATE DATABASE ' + @project_name + '
ON PRIMARY (NAME = ' + @project_name +', FILENAME = ''' + @device_directory + @project_name + '.mdf'')' +
'LOG ON (NAME = ' + @project_name + '_log' + ', FILENAME = ''' + @device_directory + @project_name + '.ldf'')' )
------------------------------------------------------------------------------
To create a database for a project 'customer', for example,
I have to modify the project.sql script and change
the definition of @project_name variable to 'customer'
I would like to be able pass the project name string into the project.sql script,
so that I don't have to hardcode the definition of @project_name everytime,
i.e do something like:
D:\> osql -i project.sql project-name-parameter
As an analogy, in Oracle, I can pass 'administration_role' parameter
to a script project.sql:
$ sqlplus username/password @project.sql administration_role
project.sql:
------------------------------------------------------------------------------
DEFINE ACCESS_ROLE = &1-- Takes the value of the first parameter
create role &ACCESS_ROLE;
------------------------------------------------------------------------------
Is this possible in T-SQL?
Thank you again,
Lucy
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply