how to pass paramters form ASP to SQL Stored Procedures 2

[techzone12]

I have an ASP page that contains a database table name as a string variable, i.e "strTableName".

I need to pass this string form the ASP page to a SQL stored procedure. The sp will execute something like:

select * from @strTableName

The results will be retured to the ASP in a record Set.

How do I do this?

Thanks!

 

[Sheco]

There are several ways to do it...

Assuming you already have the table name in a variable named [tt]sName[/tt] you could do this:
[tt]
Set myRS = myConn.Execute "spWhatever '" & sName & "'"
[/tt]

Another thing you can do is create an instance of the ADO Command object and the add paremeters to its parameter collection.

If the stored procedure doesnt have any input parameters you can just do: [tt]Set myRS = myConn.spWhatever[/tt] ... so that you are referencing the proc as a method of the connection object.... but don't do that without putting a good comment in your code or the next guy that comes along to maintain the code will have no idea what you're doing.

 

[DotNetGnat]

should look something like this...

sql = "Execute myprocedure 'value1','value2' "

con.excute sql

-DNG

 

[Sheco]

But it returns a recordset so you need the [tt]Set rs =[/tt] part.

 

[techzone12]

How would the stored procedure look like?
If I am trying to do something like:

select * from @sName

Thanks

 

[Sheco]

For MS SQL Server try something like:
[tt]
CREATE PROCEDURE spSelectAll
@sName varchar(50)
AS
SET NOCOUNT ON

Exec('SELECT * FROM ' + @sName)
[/tt]

... not sure if you need the parens...

 

[Sheco]

Of course if that is all you need the stored procedure to do then you might as well just use plain SQL:
[tt]
Set myRS = myConn.Execute "SELECT * FROM " & sName
[/tt]

 

[techzone12]

Sorry I got side-tracked.

Yes your method works fine. I did this in the ASP page:
oRs.Open "Exec spSelectAll " & sName, conn

The stored procedure is as outlined above.

Thanks for the help