Calling SQL Stored Procedures 3

[astrodestino]

Hi
I got this stored procedure (my first time using it...

create procedure usp_ReturnData as
select *
from efemerides
where mes = month(getdate())
   and dia = day(getdate())
   and anofecha = year(getdate())
go

I normally call the recordset like this:

sqlstm="SELECT * FROM efemerides where diafecha="&varDay&" and mesfecha=" &varMonth&" and anofecha="&varYear'&"'"
set efemerides = Server.CreateObject("ADODB.Recordset")
efemerides.ActiveConnection = MM_efemeridesyoroscopo_STRING
efemerides.Source = sqlstm
efemerides.CursorType = 0
efemerides.CursorLocation = 2
efemerides.LockType = 3
efemerides.Open()
efemerides_numRows = 0

How can I call the stored procedure I've created?

Thank you very much!

 

[DotNetGnat]

you just replace your sql query:

sqlstm="SELECT * FROM efemerides where diafecha="&varDay&" and mesfecha="

with something like this:

sqlstm="EXECUTE mystoredprocedure"

-DNG

 

[Ovatvvon]

or if you have variables / parameters to pass, you would do something like this:

sql = "usp_ReturnData @sp_variable1='" & someAspVariable & "', @sp_variable2='" & someOtherVariable & "'"

Set conn = Server.CreateObject("ADODB.Connection")
conn.Open connString

Set rst = conn.Execute(sql)
  'Do some stuff with your recordset
rst.Close

conn.Close
Set conn = Nothing

Call the name of the stored procedure, and then open your recordset like you would if it were a normal sql statement

-Ovatvvon :-Q

 

[Sheco]

You can also call a stored procedure as a parameter of your connection object like this:

set rst = conn.usp_ReturnData

where usp_ReturnData is the name of the stored procedure.

 

[astrodestino]

thank you all very much!!!