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?
 
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

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
from where do you want to pass the parameters?
0
 
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
0
 
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!
0
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.