Stored Procs - Returning Values
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.
Code:
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:
Code:
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
