OUTPUT @count =no of records returnd by dynamic where clause

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

OUTPUT @count =no of records returnd by dynamic where clause

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-08-18 : 07:57:22

Arvind writes "i want to create a stored procedure returns an OUTPUT variable containing the no. of records given by a query, the query being dynamic.
Preferrably the query should also be passed as a parameter to the stored procedure...If not,it should be constructed in the SP and a Part of the where clause is dependant on the value of another variable passed to the SP.

How should the query be constructed, executed, and then the Count(*) value returned?

"WHERE <condition1> AND <condition 2> ;

"AND <condition 2> " may exist or may not exist in the query; it is dependant."

Kristen
Test

22859 Posts

Posted - 2005-08-18 : 08:04:05

Hi Arvind, Welcome to SQL Team!

Something like:


CREATE PROCEDURE dbo.MySProc
    @strWhere varchar(8000),
    @intRowCount int OUTPUT
AS
DECLARE @strSelect varchar(8000)

    SELECT @strSelect = 'SELECT * FROM dbo.MyTable '

    EXEC (@strSelect + @strWhere)
    SELECT @intRowCount = @@ROWCOUNT
GO

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources