sql server, get output value sp_executesql

Question

SET ROWCOUNT @startRowIndex
     SELECT @first_id = id FROM Question q ORDER BY q.id

this is straight forward enough to get the first id from a table, however in case the sql statement is in string, how can i get the same result like above

i mean how to get output q.id from EXEC sp_executesql @sql?

IF  @sortCommand like 'sortAsAnswerCount' OR 
    @sortCommand like 'sortAsNonAnswer'
BEGIN

    set @sql=N'WITH LikeCounts
        AS
        (
        SELECT     TOP (100) PERCENT q.Id, q.Title, c.CustomerId, c.CreatedOnUtc,
                                  (SELECT     COUNT(*)
                                    FROM          dbo.CustomerLikeQuestion
                                    WHERE      (QuestionId = q.Id)) AS LikeCount
        FROM         dbo.CustomerContent AS c INNER JOIN
                              dbo.Question AS q ON c.Id = q.Id
        ORDER BY likecount DESC
        )
        SELECT *, row_number() over (order by likecount desc) as RowNum
        fROM LikeCounts'

END

Related or may be a duplicate: [**SELECT FROM stored procedure**](http://stackoverflow.com/questions/1492411/sql-server-select-from-stored-procedure). — Mahmoud Gamal, Oct 14 '12 at 11:54

score 0 · Answer 1 · answered Oct 14 '12 at 19:44

declare @params nvarchar(max), @sql nvarchar(max), @first_id int

select @params = "@first_id int output"

IF  @sortCommand like 'sortAsAnswerCount' OR 
    @sortCommand like 'sortAsNonAnswer'
BEGIN

    set @sql=N'WITH LikeCounts
    AS
    (
    SELECT     TOP (100) PERCENT q.Id, q.Title, c.CustomerId, c.CreatedOnUtc,
                              (SELECT     COUNT(*)
                                FROM          dbo.CustomerLikeQuestion
                                WHERE      (QuestionId = q.Id)) AS LikeCount
    FROM         dbo.CustomerContent AS c INNER JOIN
                          dbo.Question AS q ON c.Id = q.Id
    ORDER BY likecount DESC
    )
    SELECT @first_id = id
    fROM LikeCounts'

END

exec dbo.sp_executesql
    @stmt = @sql,
    @params = @params,
    @first_id = @first_id output

sql server, get output value sp_executesql

1 Answers1