NamBui
NamBui

Reputation: 95

HOW TO SELECT FROM EXEC sp_executeSql?

My stored procedures:

    @currPage int,
    @recodperpage int,
    @name varchar(20) = NULL,
    @type varchar(50) = NULL,
    @size varchar(50) = NULL,
    @country varchar(50) = NULL
AS
BEGIN
    DECLARE @Sql NVARCHAR(MAX);

    SELECT 
        @Sql = N'SELECT ROW_NUMBER() 
            OVER(ORDER BY MatchID Desc) AS RowNum, 
            MatchID,
            NameMatch,
            Images 
        FROM Match WHERE MatchID > 0 '

    IF @type IS NOT NULL
        SELECT @Sql += N' AND Type = ''' + REPLACE(convert(varchar(50),@type),'''','''''')+'''';

    IF @size IS NOT NULL
        SELECT @Sql += N' AND MatchSize = ''' + REPLACE(convert(varchar(50),@size),'''','''''')+'''';

    IF @country IS NOT NULL
        SELECT @Sql += N' AND Country = ''' + REPLACE(convert(varchar(50),@country),'''','''''')+''''

    EXEC SP_EXECUTESQL @Sql 
END

I want to

select * 
from EXEC SP_EXECUTESQL @Sql result 
where RowNum between (@currPage - 1) * @recodperpage + 1 
                 and @currPage * @recodperpage

Upvotes: 8

Views: 32412

Answers (2)

gofr1
gofr1

Reputation: 15977

You can declare a @table with same columns as in stored procedure output and then:

INSERT INTO @table
EXEC SP_EXECUTESQL @Sql

SELECT *
FROM @table
where RowNum between (@currPage - 1) * @recodperpage + 1
                 and @currPage * @recodperpage

Upvotes: 9

Pரதீப்
Pரதீப்

Reputation: 93704

I guess you can parametrize your where clause in dynamic query

DECLARE @Sql          NVARCHAR(MAX),
        @currPage     INT,
        @recodperpage INT

SELECT @Sql = N'select * from(SELECT ROW_NUMBER() 
            OVER(ORDER BY MatchID Desc) AS RowNum, 
            MatchID,
            NameMatch,
            Images 
        FROM Match WHERE MatchID > 0 '

IF @type IS NOT NULL
  SELECT @Sql += N' AND Type = '''
                 + Replace(CONVERT(VARCHAR(50), @type), '''', '''''')
                 + '''';

IF @size IS NOT NULL
  SELECT @Sql += N' AND MatchSize = '''
                 + Replace(CONVERT(VARCHAR(50), @size), '''', '''''')
                 + '''';

IF @country IS NOT NULL
  SELECT @Sql += N' AND Country = '''
                 + Replace(CONVERT(VARCHAR(50), @country), '''', '''''')
                 + ''''

SELECT @sql += ' ) A where RowNum between (@currPage - 1) * @recodperpage + 1 
                 and @currPage * @recodperpage'

EXEC Sp_executesql
  @Sql,
  N'@currPage int,@recodperpage int',
  @currPage =@currPage,
  @recodperpage =@recodperpage 

Upvotes: 3

Related Questions