none
getting result of dynamic query into variables

    Question

  • Hi ,

    I am not able to ge the result into the variables. I want to use this dynamic SQL in SQL Server 2000.

    so how do I get the result into variables.

    

    DECLARE @iCorrectionDays INTEGER,
          @iHoldOption INTEGER,
          @dtHoldFromDate DATETIME,
          @bInspFailOwnerResp NUMERIC(2, 0),
          @bInspFailBothResp NUMERIC(2, 0),
          @bInspTypeInitial NUMERIC(2, 0),
          @bInspTypeAnnual NUMERIC(2, 0),
          @bInspTypeSpecial NUMERIC(2, 0),
          @bInspTypeQC NUMERIC(2, 0),
          @bInspTypeMoveOut NUMERIC(2, 0),
          @bInspTypeLBP NUMERIC(2, 0),
          @bInspTypeHousekeeping NUMERIC(2, 0),
          @bReleaseInspHold NUMERIC(2, 0),
          @sPRHHoldReason VARCHAR(120),
          @iAbateStart INTEGER,
          @iAbateEnd INTEGER,
          @iAbateEndDays INTEGER,
          @bAutoApproveAbate NUMERIC(2, 0),
          @bUsePropAutoHoldAbateOpt NUMERIC(2, 0),
          @bIsAutoHoldAdded NUMERIC(2, 0),
          @hProp NUMERIC(18, 0),
          @hTenant NUMERIC(18, 0),
          @hPointer NUMERIC(18, 0),
          @sResponsibility VARCHAR(10),
          @sStatus VARCHAR(20),
          @sType VARCHAR(20),
          @hOriginalInsp NUMERIC(18, 0),
          @dtInspectedOriginal DATETIME,
          @dtInspected DATETIME,
          @hHoldId NUMERIC(18, 0)
          
    SELECT  @bInspFailOwnerResp = H8P.bInspFailOwnerResp, @bInspFailBothResp = H8P.bInspFailBothResp,
                  @bInspTypeInitial = H8P.bInspTypeInitial, @bInspTypeAnnual = H8p.bInspTypeAnnual,
                  @bInspTypeSpecial = H8p.bInspTypeSpecial, @bInspTypeQC = H8P.bInspTypeQC,
                  @bInspTypeMoveOut = H8P.bInspTypeMoveOut, @bInspTypeLBP = H8P.bInspTypeLBP,
                  @bInspTypeHousekeeping = H8P.bInspTypeHousekeeping, @bReleaseInspHold = H8P.bReleaseInspHold,
                  @sPRHHoldReason = H8P.sPRHHoldReason, @bAutoApproveAbate = H8P.bAutoApproveAbate,
                  @iAbateStart = H8P.iAbateStart, @iAbateEnd = H8P.iAbateEnd
          FROM    h8prop H8P
          WHERE   hProp = @hProp

    getting errors like variables not defined.

    please help

    Monday, April 09, 2012 1:51 PM

Answers

  • SQL Server knows nothing about a variable in that way you are using it

    USE pubs

    DECLARE @RowCount int

    EXEC sp_executesql
        N'SELECT @RowCount = COUNT(*) FROM authors',
         N'@RowCount int OUTPUT',
         @RowCount OUTPUT

    RAISERROR ('Authors rowcount is %d', 0, 1, @RowCount)


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Monday, April 09, 2012 2:03 PM
  • but if this can work in 2K5. There must be something in SQL server 2000

    That code works in SQL 2000 as well. The only limitation is that in SQL 2000, you cannot pass SQL statement longer than 4000 characters.

    If that is you problem, check out my article on dynamic SQL:
    http://www.sommarskog.se/dynamic_sql.html

    There is a section "sp_executesql and Long SQL Strings in SQL 2000" that may help you out.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, April 09, 2012 2:23 PM

All replies

  • Click on the error message and it will point you on the row causes problem.

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Monday, April 09, 2012 1:57 PM
  • Are you running this code as is or where is dynamic part in the query? In any case, if you want to output some variables back to the calling function, you need to define them as OUTPUT and call this dynamic SQL using sp_executeSQL system stored procedure.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Monday, April 09, 2012 1:58 PM
  • Hi,

    Check your variables names, and make sure it will return one row.

    I hope this is helpful.


    Please Mark it as Answered if it answered your question
    OR mark it as Helpful if it help you to solve your problem
    Elmozamil Elamir Hamid

    MyBlog

    Monday, April 09, 2012 1:59 PM
  • SQL Server knows nothing about a variable in that way you are using it

    USE pubs

    DECLARE @RowCount int

    EXEC sp_executesql
        N'SELECT @RowCount = COUNT(*) FROM authors',
         N'@RowCount int OUTPUT',
         @RowCount OUTPUT

    RAISERROR ('Authors rowcount is %d', 0, 1, @RowCount)


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Monday, April 09, 2012 2:03 PM
  • Sorry for the confusion.

    Here is the another example

    Monday, April 09, 2012 2:04 PM
  • Won't work.  The EXEC (@sql)  does not execute in the same context as where your variable is declared.  You will need to exec your SQL into a temp table and read the value into your variable from there.


    Chuck

    Monday, April 09, 2012 2:08 PM
  • but if this can work in 2K5. There must be something in SQL server 2000

    Monday, April 09, 2012 2:11 PM
  • Sorry for the confusion.

    Here is the another example

    you can't declare a variable in the outer scope where you build the string and think the variables will be visible within the EXEC command. instead you have to pass the variables as additional parameters.

    http://msdn.microsoft.com/en-us/library/ms188332%28v=sql.105%29.aspx

    and look at the description of OUTPUT parameter.

    Monday, April 09, 2012 2:14 PM
  • but if this can work in 2K5. There must be something in SQL server 2000

    That code works in SQL 2000 as well. The only limitation is that in SQL 2000, you cannot pass SQL statement longer than 4000 characters.

    If that is you problem, check out my article on dynamic SQL:
    http://www.sommarskog.se/dynamic_sql.html

    There is a section "sp_executesql and Long SQL Strings in SQL 2000" that may help you out.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, April 09, 2012 2:23 PM
  • Please check following link.

    It may help you.

    1. http://stackoverflow.com/questions/3840730/getting-result-of-dynamic-sql-into-a-variable
    2. http://www.devx.com/tips/Tip/14087
    3. http://www.sqlservercentral.com/Forums/Topic600555-145-1.aspx
    Monday, April 09, 2012 5:52 PM
  • Virat,

    Please make sure that when you post links, they are clickable.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Monday, April 09, 2012 5:53 PM