Stored Procedure 1

[astrodestino]

Hi!
I got a website that has some asp pages that receives more o less 12.000 hits per day.
In that page I show only one recordset which date is equal to the current date.

Here is my sql:

varDate = date()
varDay = DatePart("d",varDate)
varMonth = DatePart("m", varDate)
varYear = DatePart("yyyy", varDate)
sqlstm="SELECT * FROM efemerides where mes="&varmonth&" and dia=" &varday&" and anofecha="&varYear

I do code visual basic net applications but I'm not into sql server. I was told that I can free sqlserver resources by creating a stored procedure and by calling it instead of using the code above.
Can anyone give me a hand about how do I create it, where should I put it and how to call it? Or give me some example so I can edit the resto of the site's sql.
Thank you very much!!

 

[mrdenny]

To create the stored procedure you'd do something like this in Query Analyzer.

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

To call the procedure simple run

exec usp_ReturnData

You'll need to check with the ASP.Net forums for how to run the procedure. If you aren't using all the columns within the table it is recommended that you only return the columns you are using on the web site.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[astrodestino]

Thank you very much for the example!
What is the diference between the normal sql function and the stored procedure that frees resources?
Thank you!

 

[gmmastros]

Here's a good article to read. I lists several reasons why stored procedures are better.

http://www.sql-server-performance.com/stored_procedures.asp

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[astrodestino]

tnx man!!