locked
Dynamic SQL/TVF Problem RRS feed

  • Question

  • Hey Guys,

    Trying to design a query for a program I'm writing (VB.NET using LINQ) which would basically hop the first valid record available as a resultset, but the query needs to be stored and easily callable (Function/Stored Proc/TVF) and return the data in such a way that my program can utilize it. Currently, I've tried creating an SP that grabs and prints the resultset, but doesn't return it as row-shaped data, so all the program sees is a 0.

    CREATE FUNCTION dbo.GetNextARow(@Audit_ID int)
    RETURNS TABLE
    AS
    BEGIN
    DECLARE @cols varchar(1000)
    DECLARE @colIDs varchar(1000)
    DECLARE @sql varchar(1000)
    DECLARE @pkey varchar(1000)
    SELECT @colIDs = COALESCE(@colIDs + ', ', '') + cast(ID as varchar) FROM Audit_Fields WHERE A_ID = @Audit_ID and Type <> 0 ORDER BY Ord
    SELECT @cols = COALESCE(@cols + ', ', '') + Quotename(Name) FROM Audit_Fields WHERE A_ID = @Audit_ID and Type <> 0 ORDER BY Ord
    SELECT @pkey = [NAME] FROM Audit_Fields WHERE Ord = 0 AND A_ID = @Audit_ID
    --SELECT @cols
    --SELECT @colIDs
    SET @sql = 'SELECT R_ID, E_ID, U_Info as ' + Quotename(@pkey) + ', ' + @cols + '
    FROM
    (SELECT R_ID, Audit_Fields.Name as Name, CASE Type 
    WHEN 1 THEN cast(Cast(Value as bigint) as Varchar)
    WHEN 2 THEN cast(Cast(Value as bit) as Varchar)
    WHEN 3 THEN cast(Value as varchar)
    WHEN 4 THEN cast(Cast(Value as int) as Varchar)
    END AS DATA
    FROM Audit_Data 
    LEFT JOIN Audit_Fields ON Audit_Data.C_id = Audit_Fields.ID 
    WHERE C_ID in (' + @colIDs + ')) p
    LEFT JOIN Audit_records ON R_ID = Audit_Records.ID
    pivot( max(data) for Name in (' + @cols + ') )o
    WHERE R_ID = (SELECT Min(ID) FROM Audit_Records WHERE E_ID is NULL AND A_ID = ' + cast(@Audit_ID as varchar) + ')'
    RETURN
    EXEC (@sql)
    END

    This attempt at creating a TVF keeps throwing back a "Msg 102, Level 15, State 31, Procedure GetNextARow, Line 35 Incorrect syntax near 'BEGIN'." and I haven't figured out why.

    I need to get to a point where I can Simply call "SELECT * FROM GetNextARow(X)" and get back the result. Any ideas?

    Tuesday, April 24, 2012 10:00 PM

Answers

  • The stored procedure is the answer. Perhaps you don't get result as in your code you put RETURN before EXECUTE, so execute never happens. You will not be able to make it as a function, so concentrate on getting SP to work correctly for your scenario.

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


    My blog

    • Marked as answer by Stephanie Lv Tuesday, May 1, 2012 5:12 AM
    Wednesday, April 25, 2012 12:02 PM
    Answerer

All replies

  • There are several things wrong here. First of all, you can not use dynamic SQL in a function. And secondly, your declaration is for inline table valued function and then you provide multi-line code.

    I suggest to change the above to be a stored procedure instead. You can use dynamic SQL in the stored procedure. In order to get result you will do

    execute dbo.mySP @Audit_ID

    Also, don't embed parameters into the dynamic code, keep them as parameters and use sp_executeSQL stored procedure. Check this blog post

    Avoid Conversions In Execution Plans By Using sp_executesql Instead of Exec


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


    My blog

    Wednesday, April 25, 2012 2:31 AM
    Answerer
  • I realize what I'm going for may be impossible bordering on the ridiculous, but a Stored Procedure (as far as I'm aware) has to have it's outputs defined during creation of the SP, and the purpose of the query that my unwieldy T-SQL script at the core of the above-posted code (between the BEGIN and the END) puts together is to be able to dynamically pull together a virtual table based on the contents of other tables, which could feasibly have any number of columns. The SP I already made, which is quite similar to the code above, just returns a 0 all the time to my app, and it can't extract the row that it creates from the dynamic query.

    Thank you for that link, it'll help move me forward a lot, but I'm still at the impasse I was, unless I missed something right in front of my face.

    Wednesday, April 25, 2012 4:50 AM
  • The stored procedure is the answer. Perhaps you don't get result as in your code you put RETURN before EXECUTE, so execute never happens. You will not be able to make it as a function, so concentrate on getting SP to work correctly for your scenario.

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


    My blog

    • Marked as answer by Stephanie Lv Tuesday, May 1, 2012 5:12 AM
    Wednesday, April 25, 2012 12:02 PM
    Answerer