Stored Procs - Returning Values

[invitro]

1). How do I get a recordset using a stored proc. I figured out how to return parameters, but I have yet to return a recordset. I want to return a recordset through dynamic querys.

CREATE PROCEDURE sp_getTableWhere

	
	@vTable varchar(50),
	@vcharValue1 varchar(50),
	@vcharValue2 varchar(50)
AS
	
	Declare @sql nvarchar(500)

	SET @sql = "SELECT * FROM " + @vTable + "  WHERE " + @vcharValue1 + " = " + '''' + @vcharValue2 + ''''


	EXEC(@sql)

	return
GO

I would make a command object, pass in my 3 parameters, and then what... .Execute wont return me much... im lost

...god i need some sleep.


2). How do I return a count of rows using dynamic SQL?
Heres my function:

CREATE PROCEDURE sp_searchResults 

	
	@vTable varchar(50),
	@vcharValue1 varchar(50),
	@vcharValue2 varchar(50)

WITH RECOMPILE AS
	
	Declare @sql nvarchar(500)



	SET @sql = "SELECT  Count(*) AS ??? FROM " + @vTable + "  WHERE " + @vcharValue1 + " = " + '''' + @vcharValue2 + ''''

	EXEC(@sql)

	return ???
GO

What do I have to change ??? to
How would i get the count if the statement was not dynamic?


Help greatly appriciated,
Alek

[dj4uk]

1.

CREATE PROCEDURE sp_getTableWhere

	
	@vTable varchar(50),
	@vcharValue1 varchar(50),
	@vcharValue2 varchar(50)
AS
	
	Declare @sql nvarchar(500)

	SET @sql = "SELECT * FROM " + @vTable + "  WHERE " + @vcharValue1 + " = " + '''' + @vcharValue2 + ''''


	EXEC(@sql)
GO

This will return a recordset.

2.

CREATE PROCEDURE sp_searchResults 

	
	@vTable varchar(50),
	@vcharValue1 varchar(50),
	@vcharValue2 varchar(50)

WITH RECOMPILE AS
	
	Declare @sql nvarchar(500)



	SET @sql = "SELECT  Count(*) AS rowNumber FROM " + @vTable + "  WHERE " + @vcharValue1 + " = " + '''' + @vcharValue2 + ''''

	EXEC(@sql)
GO

This will return a recordset with one field rsExample("rowNumber").

Hope that makes sense.

What are you accessing the SQL stored procedure through ASP page or VB program?

DJ

[dj4uk]

Whoops just noticed this is posted in ASP, VBScript.