Results 1 to 2 of 2

Thread: Check This

  1. #1
    Join Date
    Apr 2004
    Posts
    7

    Question Unanswered: Check This

    DECLARE @Temp int
    DECLARE @FullQry varchar(50)

    set @FullQry='select @Temp=Emp_ID from Employee where.....'
    Exec(@FullQry)
    select @@ROWCOUNT

    My Employee table has 3 records and this query sholud return me @@ROWCOUNT=1
    but it will return 0 why this i am not able to find out.Exec function return ROWCOUNT or not?

  2. #2
    Join Date
    Nov 2003
    Posts
    94
    The domain of the selection "@Temp=" is outside the domain of the executed statement so the query as you have written it will return:

    Server: Msg 137, Level 15, State 1, Line 1
    Must declare the variable '@Temp'.

    (1 row(s) affected)


    The correct way to return values from a dynamically executed SQL section is to use sp_executesql with output semantics:

    DECLARE @Temp int
    DECLARE @Rows int
    DECLARE @sql nvarchar(4000)

    SET @sql=N'SELECT @Rows=@@ROWCOUNT, @Temp=EMp_ID FROM ....'

    EXEC sp_executesql @sql, N'@Temp int OUTPUT, @Rows int OUTPUT', @Temp OUTPUT, @Rows OUTPUT

    SELECT @Rows, @Temp

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •