Results 1 to 2 of 2

Thread: dynamic sql

  1. #1
    Join Date
    Jul 2003
    Posts
    70

    Unanswered: dynamic sql

    Hi All,
    Following is the situation:


    DECLARE @iCount INT
    DECLARE @strQry AS VARCHAR(4000)
    DECLARE @strEmp AS VARCHAR(200)

    SET @strEMP = '993,1040,1061'

    SET @strQry = 'SELECT @iCount =COUNT(id) FROM tableA WHERE status=91 AND Employee IN ('+ CONVERT(VARCHAR(4000),@strEmp)+')'

    EXECUTE (@strQry)

    PRINT @iCount

    This statement does not execute, I tried to declare @iCount as VARCHAR(10), still it does not work. Actually I wanted to get the count in @iCount and manipulate later.
    Can anyone help?

    Regards
    qa

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    @iCount only exists within the scope of the script that creates it. It is not visible to dynamic SQL queries, as each runs in a separate scope. Temp tables, however, belong to the connection and thus can be referenced in dynamic SQL. So try this:
    Code:
    DECLARE @iCount INT
    DECLARE @strQry AS VARCHAR(4000)
    DECLARE @strEmp AS VARCHAR(200)
    
    SET @strEMP = '993,1040,1061'
    
    create table #MyiCount (iCount int)
    
    SET @strQry = 'INSERT INTO #MyiCount (iCount) SELECT COUNT(id) FROM tableA WHERE status=91 AND Employee IN ('+ CONVERT(VARCHAR(4000),@strEmp)+')'
    
    EXECUTE (@strQry)
    
    set @iCount = select top 1 iCount from #MyiCount
    
    drop table #MyiCount
    
    PRINT @iCount
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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