SQL 2k Stored Procedures???

[LostProgrammer]

My first time to forum and I have a question for everyone. This is probly a easy question, but I am completely lost.

 

I made this stored procedure in SQL

 

CREATE PROCEDURE procGetSystemDate

AS

 

DECLARE @SQL nvarchar(100)

 

Select @SQL = "SELECT GetDate() AS SysDate"

 

EXEC sp_executeSQL @SQL

GO

 

Very simple, when executed it returns the current date/time from the server. I would like to beable to call this procedure in VB .net and store the time in my own vb.net datetime variable. Im clueless on how to do this. Here is my latest attempt at getting the variable.

 

Dim curTime As DateTime
       Dim cmdSql As New SqlClient.SqlCommand("procGetGystemDate")
       Dim conSql As New SqlClient.SqlConnection()
       Dim rdrSql As SqlClient.SqlDataReader
       conSql.ConnectionString = _
       "Integrated Security=True;Data Source=10.10.1.47;Initial Catalog=TimeLog;"
       cmdSql.Connection = conSql
       conSql.Open()
       rdrSql = cmdSql.ExecuteReader(CommandBehavior.CloseConnection)
       rdrSql.Read()
       curTime = rdrSql.GetDateTime(0)
       lbl.Text = curTime
       rdrSql.Close()

 

So anyways I dont think I am even close to getting this right, I have never used procedures before. Anyone have any ideas?

 

LostProgrammer

Edited January 17, 2003 by divil

[aikeith]

more info needed

 

Actually that looks ok, what is the error(s) you are getting?

 

Some things to be aware of:

 

DateTime Variable that is being put into a text field (label.text)

Your stored procedure is setting nvarchar instead of DateTime

Make sure your connection is properly open/close

 

If you can post the error I could give you specific help.

 

...

[Nerseus]

I used the following (C#, sorry) and it worked.

DateTime dt;
SqlConnection conn = new SqlConnection("Integrated Security=True;Data Source=myserver;Initial Catalog=master;");
conn.Open();
SqlCommand cmdSql = new SqlCommand("select getdate() as [systemDate]", conn);
SqlDataReader rdrSql;
      
rdrSql = cmdSql.ExecuteReader(CommandBehavior.CloseConnection);
rdrSql.Read();
dt = rdrSql.GetDateTime(0);
Debug.WriteLine(dt);

 

 

You can GREATLY simplify your proc, by the way. In fact, you don't even need a proc for what you want. Simply use this:

Dim cmdSql As New SqlClient.SqlCommand("SELECT getdate() as [systemDate]")

 

If you really want a proc, use:

CREATE PROCEDURE procGetSystemDate 
AS

SELECT getdate() as [systemDate]

 

By using sp_executeSQL you're eliminating any benefit of using a proc, since you're running dynamic SQL that's hard-coded :)

 

-ner