sql2000 parameter

I want to be able to pass parameters to the following script. These parameters would be used to replace ‘createContractAddressType’ and ‘Maintain Contact Address Type’. I will be call the script using osql

 

DECLARE @TestID as INTEGER

DECLARE @CycleID as INTEGER

DECLARE @Date as DATETIME

DECLARE @Time as VARCHAR(10)

SET @TestID = (SELECT TS_TEST_ID FROM td.TEST WHERE TS_NAME='createContactAddressType')

SET @CycleID = (SELECT CY_CYCLE_ID FROM td.CYCLE WHERE CY_CYCLE='Maintain Contact Address Type')

SET @Date = (SELECT TOP 1 TC_EXEC_DATE FROM td.TESTCYCL WHERE TC_TEST_ID = @TestID and TC_CYCLE_ID = @CycleID ORDER BY TC_EXEC_DATE DESC, TC_EXEC_TIME DESC)

SET @Time = (SELECT TOP 1 TC_EXEC_TIME FROM td.TESTCYCL WHERE TC_TEST_ID = @TestID and TC_CYCLE_ID = @CycleID ORDER BY TC_EXEC_DATE DESC, TC_EXEC_TIME DESC)

UPDATE td.TESTCYCL SET TC_STATUS = 'Failed' WHERE TC_TEST_ID = @TestID and TC_CYCLE_ID = @CycleID and TC_EXEC_DATE = @Date and TC_EXEC_TIME = @Time

GO

 

Can you tell me how to pass parameters to this script?

RichardsoetAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
from where do you want to pass the parameters?
OtanaCommented:
Create a stored procedure:

CREATE PROCEDURE stp_YourProcedure @cCAT varchar(50), @mCAT varchar(50)

DECLARE @TestID as INTEGER

DECLARE @CycleID as INTEGER

DECLARE @Date as DATETIME

DECLARE @Time as VARCHAR(10)

SET @TestID = (SELECT TS_TEST_ID FROM td.TEST WHERE TS_NAME=@cCAT)

SET @CycleID = (SELECT CY_CYCLE_ID FROM td.CYCLE WHERE CY_CYCLE=@mCAT)

SET @Date = (SELECT TOP 1 TC_EXEC_DATE FROM td.TESTCYCL WHERE TC_TEST_ID = @TestID and TC_CYCLE_ID = @CycleID ORDER BY TC_EXEC_DATE DESC, TC_EXEC_TIME DESC)

SET @Time = (SELECT TOP 1 TC_EXEC_TIME FROM td.TESTCYCL WHERE TC_TEST_ID = @TestID and TC_CYCLE_ID = @CycleID ORDER BY TC_EXEC_DATE DESC, TC_EXEC_TIME DESC)

UPDATE td.TESTCYCL SET TC_STATUS = 'Failed' WHERE TC_TEST_ID = @TestID and TC_CYCLE_ID = @CycleID and TC_EXEC_DATE = @Date and TC_EXEC_TIME = @Time

GO

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RichardsoetAuthor Commented:
Thanks  I have been able to create this stored procedure. What is the best way of calling this stored procedure from the command line
Aneesh RetnakaranDatabase AdministratorCommented:
Hi Otana,
You can use OSQL utility

OSQL -S server -U username -P password -d databasename -Q urspName

if you are using Windows uthentication

replace   -U and -p  with -E

Aneesh R!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.