executing dynamic SQL

  • kdfischer

    SSC Veteran

    Points: 217

    I'm stumped.

    I want to write a function where in I pass a value that is looked up. The looked up value is a SQL statement that I then want to execute. I tried using dynamic SQL but am told that I can't do that within a function. For instance, one of the values returned is 'SELECT REPLACE(CONVERT(char(8), CURRENT_TIMESTAMP, 10), '-', '/')' or another may be 'SELECT LOB FROM EligDW.dbo.tblClientList WHERE Div = @Div' and then I want to execute that statement, get my result and return it to my calling stored procedure.

    I want it to be a function so that I can use it for other applications down the road.

    Any ideas?

    This is what I have been playing with. The 2nd part doesn't work. Maybe I'm pursuing the wrong approach.

    ALTER FUNCTION [dbo].[Func_DocumentGeneratorReplaceVariable] (@VariableName NVARCHAR(100), @DocType NVARCHAR(25), @Parm1 NVARCHAR(20))

    RETURNS NVARCHAR(MAX) AS

    BEGIN

    DECLARE @VariableType NVARCHAR(20)

    DECLARE @EpicID NVARCHAR(10)

    DECLARE @Div NVARCHAR(3)

    DECLARE @SystemRule NVARCHAR(4000)

    DECLARE @Result NVARCHAR(MAX), @EndResult NVARCHAR(MAX)

    SET @VariableType = (SELECT VariableType FROM EpicDW.dbo.tblDocumentGeneratorVariables WHERE VariableName = @VariableName)

    IF @VariableType = 'FET'

    BEGIN

    IF @DocType = 'SOW'

    BEGIN

    SET @Result = (SELECT t2.FETVariableAnswer

    FROM EpicDW.dbo.tblOSMFiestaProjects AS o1 INNER JOIN

    EpicDW.dbo.tblOSMProjectFETs AS o2 ON o1.Project_ID = o2.Project_ID INNER JOIN

    EpicDW.dbo.tblDocumentGeneratorVariables AS t1 INNER JOIN

    EpicDW.dbo.tblDocumentGeneratorFETRules AS t2 ON t1.VariableID = t2.VariableID ON o2.Table_Type = t2.FETType AND o2.FET_Ctrl_Nbr = t2.FETCtrl AND o2.FET_Option = t2.FETOption

    WHERE t1.DocType = @Doctype

    AND t1.VariableName = @VariableName

    AND o1.EPIC_ID = @Parm1)

    END

    END

    ELSE

    IF @VariableType = 'System'

    BEGIN

    IF @DocType = 'SOW' BEGIN SET @Div = (SELECT Div FROM EpicDW.dbo.tblOSMFiestaProjects WHERE Epic_ID = @Parm1) END

    SELECT @SystemRule = REPLACE(t2.SystemRule,'@Div', '''' + @Div + '''')

    FROM dbo.tblDocumentGeneratorSystemRules AS t2 INNER JOIN

    dbo.tblDocumentGeneratorVariables AS t1 ON t2.VariableID = t1.VariableID

    WHERE t1.VariableName = @VariableName

    exec sp_executesql @systemrule

    END

    RETURN @Result

    END

  • praveen_vejandla

    SSCrazy

    Points: 2066

    I think this is possible only through stored procedures. Function can't provide such functionality.

  • Mahesh Bote

    SSCrazy Eights

    Points: 9107

    [font="Verdana"]your t-sql code is not a dynamic sql. in @SystemRule variable you are ftching column's value and in the next line you are tryingto execute it. It wont work this way. let us know what exactly you are trying to do with expected o/p.

    Thanks,

    Mahesh[/font]

    MH-09-AM-8694

  • kdfischer

    SSC Veteran

    Points: 217

    The intent is to retrieve a SQL command stored in a table, i.e. SELECT REPLACE(CONVERT(char(8), CURRENT_TIMESTAMP, 10), '-', '/') or SELECT PermAnalyst FROM EligDW.dbo.tblClientList WHERE Div = @Div and then execute that command to bring back one value.

    The first part of the function I can query tables to get a value, the 2nd part is if it's what I call a system rule and it could be against anything, therefore the thought was that I'd lookup the sql string and execute it for the result.

    The function is to take a variable in and get one result back. I.E, CURRENTDATE in would give me back 08/24/10.

  • kdfischer

    SSC Veteran

    Points: 217

    Like I said, maybe there's a different approach to this. Let me try to explain my idea.

    I have a table that stores a document's text. It's what we call a Statement of Work. This is html text. Within the text are what I term variables. These variables need to be replaced depending upon the type of project that it pertains to. There are two types of variables: table driven and system. The table driven is simple: query the table for a match on this variable and these columns. The system variable are things that are common to a document, like currentdate, client name, analyst name, type of business. And these values come from different tables, even different databases. My thought was that because it could really be anything, I'd store the SQL that would retrieve that value. I'd get the SQL and then I'd execute it and return my answer.

    I query the table that has the document text, which is stored in sections. The query calls a function that parses out any variables that are enclosed in {} (i.e. {currentdate}). That function finds a variable and then calls a function that does the replacement. This is the one we are talking about. So the variable {CONTRACT ID} may be found, it goes into the function determines that it is an FET type variable and the result is table driven. However, if the variable is {currentdate} or {clientname} it determines that it is NOT an FET type variable and therefore is a SYSTEM variable and then it queries the system table and finds the SQL to derive how to get the answer. Therefore, it finds SELECT REPLACE(CONVERT(char(8), CURRENT_TIMESTAMP, 10), '-', '/') as the command to execute. It then returns to my stored procedure and I get a nice HTML formatted return.

    The problem is, I can't actually execute that within that function. Maybe my idea of a dynamic SQL is skewed, but to me it is that I built a SQL command, put it in a variable and execute it. I've done stuff in stored procedures where I'll build a SQL string and do just that with exec sp_executesq @sqlstring. Isn't that dynamic SQL?

    Anyway, the real point is that I have a variable that I want to look up and replace. I thought I'd do a function so that I could reuse the code.

    Finally, the reason I am not doing anything on the client side because it will run from SharePoint via Reporting Services. The intent is to chose the client id from a drop down and get the document.

    Simple, sample document text in:

    <div align=right><font size=6><strong>{currentdate}</strong></font></div><div align=right><font size=6><strong>{DIV} - {CLIENTNAME}</strong></font></div><div align=right><font size=5><strong>{projecttype} - Statement of Work</strong></font></div><div></div><div><font size=2><strong>Prepared and Approved by: {PermAnalyst}</strong></font></div><div></div>

    Desired result:

    <div align=right><font size=6><strong>08/24/10</strong></font></div><div align=right><font size=6><strong>ABC - My Client Name</strong></font></div><div align=right><font size=5><strong>Implementation - Statement of Work</strong></font></div><div></div><div><font size=2><strong>Prepared and Approved by: Kevin Fischer</strong></font></div><div></div>

    Thanks. And I realize there are things I can learn, but this is the approach that has come to my mind.

    Kevin

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply