Passing a Parameter to a Stored Procedure

[parkes]

I'm using VB6 and SQL7 and I need to pass 4 parameters across to my SP and then I want it to return a message, what's the easiest way to do this as I'm new to SQL.

The stored prodecure works fine as this has already been tested

[nexus]

create a connection to the database through the data enviroment .

in the data view window create the stored proc
transact sql (sql server db language)
eg

Create or Replace PROCEDURE CustOrdersDetail (declare varables)
(
@OrderID int
)
AS
(command text)
SELECT ProductName,
UnitPrice=ROUND(Od.UnitPrice, 2),
Quantity,
Discount=CONVERT(int, Discount * 100),
ExtendedPrice=ROUND(CONVERT(money, Quantity * (1 - Discount) * Od.UnitPrice), 2)
FROM Products P, [Order Details] Od
WHERE Od.ProductID = P.ProductID and Od.OrderID = @OrderID

save it to the data base
this code will take 1 param and the return a record set

insert this sp in to the dataeviroment so you can acsess it!

you can now acsess use this sp any time by calling it through the data env
hope this is help ful!!!

[parkes]

Thanks for that, but I'm not using the DE, just using simple code. Or would you recomment using the DE instead of code

[Clunietp]

Is the message you have to return being returned by the SP?

[parkes]

Yes the message I need to return is coming from the SP

[nexus]

first off i would use the de enviroment cus once its set up it will make its easy and quicker (very easy & and very quick) to develop data base project and you can also create the stored procs through the data eviroment (tables ,database diagrams,views ect)so u dont need to use the sql server interface at all (after youve created the db)

but if you must heres some code to call a stored porc manually!!!

dim objspcomand as adodb.command
dim pr_insert(2) as variant
set objspcomand = new adodb.command
objspcommand.activeconnection = objactiveconnection
objspcommand.comandtype = adcmdstoredproc

objspcommand.comandtext = "insert_jobs"
pr_insert(0) = "president"
pr_insert(1) = 50
pr_insert(2) = 250
objspcommande.execute , pr_insert

set objspcommand = nothing

as you can see its pritty long winded
i think the code is pritty self explanatry pr_insert are the parameters u are passing.

hope this is helpful but try the data enviroment way first its lot better.

[parkes]

Thanks that worked fine. I'll give the DE a try