How to pass DateTime variable to stored procedure 2

[techzone12]

I have some ASP code that looks like this:

objRs.Open "Execute Proc1 " & strTableName & ", " & strColumn , objConn

The Stored procedure "Proc1" looks like this:
CREATE PROCEDURE [Proc1]
@strTable varchar(50),
@strColumn varchar(50)
AS
Exec('SELECT ' + @strColumn + ' FROM ' + @strtable + ' order by [timeStamp]')

I need to modify the above Stored Procedure to do something like this:
Exec('SELECT ' + @strColumn + ' FROM ' + @strtable + ' WHERE TimeStamp > ' + @DateVariable)

What's the syntax in the ASP when passing DateTime Variables to a Stored procedure? Can you give an example?
Also how should the Stored Procedure SQL code look like?

Thanks

 

[DotNetGnat]

Change 1:

objRs.Open "Execute Proc1 " & strTableName & ", " & strColumn & " ," & DateVariable, objConn

change 2:


[red]ALTER[/red] PROCEDURE [Proc1]
@strTable varchar(50),
@strColumn varchar(50),
@DateVariable datetime
AS
Exec('SELECT ' + @strColumn + ' FROM ' + @strtable + ' WHERE TimeStamp > ' + @DateVariable)
[/code]

-DNG

 

[techzone12]

In the ASP page I set the DateVariable as follows:
DateVariable = "1/1/2005"

And I am getting an error at this line:

objRs.Open "Execute Proc1 " & strTableName & ", " & strColumn & " ," & DateVariable, objConn

It says:
Error Type:
Microsoft OLE DB Provider for SQL Server (0x80040E14)
Line 1: Incorrect syntax near '•'.
/energy web site/test2.asp, line 26

I guess I have to massage the date variable before passing it. I am not sure exectely how however?


Thanks

 

[chmohan]

I think time stamp is datetime right? You might have to send in a time along with your date variable

 

[techzone12]

The Column TimeStamp is a "DateTime" type. This column in table1 has ONLY the Date and not the time portion, i.e 10/18/2005.

 

[chmohan]

you may have to use formatting of the date variable you are sending,can you see if this works in your sql query analyzer
Exec Proc1 strTableName , strColumn ,"1/1/2005"

 

[techzone12]

I am getting the following error when I ran it in query analyzer:

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '•'.

Apparantly it doesn't like the way the date variable is formatted?

 

[chmohan]

seems like that can you use convert function and send it in?

 

[chmohan]

is your columnname or table name odly named? I wonder where it is seeing ".

 

[Sheco]

Use single quotes around the time.

Also the TimeStamp data type is not the same as the DateTime data type... The latter is exactly what you'd expect but the former is a guaranteed unique number that is generated based on the number of ticks since a offset date.

 

[techzone12]

Thank you all for all the valuable hints. you pointed me in the right direction.

I got it to work by doing this in the ASP code:
DateVariable = "'01/01/2005'"
objRs.Open "Execute Proc1 " & strTableName & ", " & strColumn & " ," & DateVariable, objConn


The change I made to the stored procedure is as follows:

CREATE PROCEDURE [Proc1]
@strTable varchar(50),
@strColumn varchar(50),
--@DateVariable datetime
@DateVariable varchar(50)
AS
Exec('SELECT ' + @strColumn + ' FROM ' + @strTable + ' WHERE TimeStamp > ' + @DateVariable)


The DateTime Variable is passed as a "string" to the stored procedure!. The Stored procedure is expecting a string.
SQL will magically interpert the string passed as a DateTime when executing the query!.

I probably shoud rename DateVariable to strDateVar to make clear that it's actually a string.

 

[techzone12]

Oops, Sorry guys!!.It does not work yet.
It does not generate errors, but the query does not produce the correct results.
I am basically getting the entire table. So basically the condition TimeStamp > '1/1/2005' is not evaluating correctly.

The '1/1/2005', which is passed as a string to the stored procedure, needs further massaging to convert it to correct date. I think I am closer but not there yet.

Thanks

 

[Sheco]

You might find it quicker/easier to use the Query Analyzer program for this sort of debugging.

 

[DotNetGnat]

TimeStamp>Convert(varchar(20),@DateVariable,120)

http://www.sqlteam.com/item.asp?ItemID=1410

-DNG

 

[Sheco]

... but @DateVariable is already a varchar 50

 

[DotNetGnat]

hmm..didnt see that...

-DNG

 

[techzone12]

Here is how I got it to work, in case some body is interested in this later.

CREATE PROCEDURE [Proc1]
@strDateVariable varchar(50)
AS
Exec('SELECT DateTime1 , TotalKWH FROM table1 WHERE DateTime1 > convert(datetime,''' + @strDateVariable + ''', 101)')

Notice that the DateVariable is passed from the ASP page as a string first.

Regards