[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

sql2000 parameter

Posted on 2006-04-26
4
Medium Priority
?
333 Views
Last Modified: 2008-02-01
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?

0
Comment
Question by:Richardsoet
4 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16543753
from where do you want to pass the parameters?
0
 
LVL 11

Accepted Solution

by:
Otana earned 2000 total points
ID: 16543771
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
 

Author Comment

by:Richardsoet
ID: 16544113
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
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16544742
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question