How to pass Multiple aparamters, including datetime to sp 1

[techzone12]

I have an ASP page with this code:

strTable = "table1"
oRsSubs.Open "Execute sp_Proc " & strTable, conn

The stored procedure in SQL looks like this:

CREATE PROCEDURE sp_Proc ( @strTable varchar(50) )
AS
EXEC('SELECT * FROM ' + @strTable)

This work fine. I am trying however to add some functionality to the code. Basically I want be able to pass some datetime values, besides the table name. Can you show me the ASP code and also the SQL code for the stored procedure.

I want to be able to execute a query like this:
execute('SELECT * FORM + '@strTable + 'where TimeStamp >' + @someDate')

Thanks

 

[Sheco]

For parameters that are not character data (not char, varchar, nvarchar, etc) you can use the CAST() or CONVERT() functions to cast them as characters so that you can append their values to your SQL string.

Separate parameters with commas for both your additional parameters in the ALTER PROCEDURE statement and the string you pass to your recordset object in the ASP.

 

[techzone12]

Thanks for the hint. And sorry for posting two questions in one thread. I will leave this thread just for how to pass multiple paramters to Stored procedure.

In the ASP page I do the following:

oRs.Open "Execute sp_Proc " & strTable & ", " & strColumn , conn

In the Stored procedure, I would do this:

CREATE PROCEDURE sp_Proc (@strtable varchar(50) , @strColumn varchar(50))
AS
EXEC('SELECT '+ @strColumn + ' FROM ' + @strTable)

All this is now clear to me. Thanks

 

[Sheco]

yeah, and if you want to use a date variable in the WHERE clause it would be something like:
[tt]
EXEC('SELECT '+ @strColumn + ' FROM ' + @strTable + ' WHERE MyDateField > ''' + CAST(@DateParam AS varchar(20)) + ''''
[/tt]

...hmmm, might have messed up the number of quote thingies

or, if you want a specific date format use CONVERT instead because it takes an optional 3rd parameter to specify the date format.