Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2011
    Posts
    2

    Lightbulb Unanswered: dynamic query issue

    Need help on following:
    @strSql is a dynamic query in while loop which can return single record, single row of records, multiple rows of records.
    So only if it returns single record then I have to store it otherwise convert to xml before storing.

    1) If it returns 1 record(1row and 1 column) then save as it is.
    2) if it returns a row with more than 1 columns then convert to xml before saving.
    2) if it returns data rows then convert to xml before saving.

  2. #2
    Join Date
    Feb 2004
    Location
    Bangalore, India
    Posts
    250
    Quote Originally Posted by sushilkumargiri View Post
    Need help on following:
    @strSql is a dynamic query in while loop which can return single record, single row of records, multiple rows of records.
    So only if it returns single record then I have to store it otherwise convert to xml before storing.

    1) If it returns 1 record(1row and 1 column) then save as it is.
    2) if it returns a row with more than 1 columns then convert to xml before saving.
    2) if it returns data rows then convert to xml before saving.

    I think you choose whether to save /convert to XML before saving depending on whether the query returns a result set with single column / multiple columns ( you've mentioned that if query returns 1 row with multiple columns or it returns multiple rows, then save to XML)

    1) "If it returns 1 record(1row and 1 column) then save as it is."

    Since the query is prepared dynamically based on some logic, won't you know whether it will return a single column??? I assume you are not using SELECT *

    Please post the prepared dynamic sql
    Cheers....

    baburajv

  3. #3
    Join Date
    Nov 2011
    Posts
    2

    Thanks for the propmpt reply...

    the query is as below.. dynamic query is taken from some other table.. Please suggest what logic can be applied before deciding on to convert to xml.. I am not able to find the solution..
    DECLARE @istrType VARCHAR(8);
    DECLARE @strSQL NVARCHAR(MAX);
    DECLARE @strParam VARCHAR(MAX);
    DECLARE @intRows INT;
    DECLARE @intLoop INT;
    DECLARE @ItemCode VARCHAR(50);

    SET @istrType='daily';
    DECLARE @tblQuery TABLE
    (RunId INT IDENTITY(1,1),
    ID VARCHAR(20),
    ItemCode VARCHAR(50),
    Query VARCHAR(MAX));
    INSERT INTO @tblQuery (ID, ItemCode, Query)
    SELECT ID, VariableCode, SelectQry FROM [dbo].[Tb_ReportQuery];
    SELECT @intRows = COUNT(*) FROM @tblQuery;
    SET @intLoop = 1;

    WHILE @intLoop <= @intRows
    BEGIN
    SELECT @ItemCode = ItemCode FROM @tblQuery WHERE RunId = @intLoop;
    SELECT @strSQL = Query FROM @tblQuery WHERE RunId = @intLoop;

    IF(Output of @strSQL returns more than 1 record)
    BEGIN
    SELECT @strSQL = '(' + @strSQL + ' FOR XML PATH (''Element''), ROOT(''Root''))';
    END
    ELSE
    BEGIN
    SELECT @strSQL = '(' + @strSQL + ')';
    END
    SET @strSQL = 'Insert Into Tb_Process_Values
    (CurDate, Code, ReportType, Value)
    Select ''' + GETDATE() + ''',''' + @ItemCode + ''',''' + @istrType + ''',' + @strSQL;
    EXEC (@strSQL);
    SET @intLoop = @intLoop + 1;
    END

Tags for this Thread

Posting Permissions

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