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

 

[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 dont 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 youre eliminating any benefit of using a proc, since youre running dynamic SQL thats hard-coded

-ner