August 23, 2010 at 3:03 pm
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
August 23, 2010 at 10:42 pm
I think this is possible only through stored procedures. Function can't provide such functionality.
August 24, 2010 at 4:38 am
[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
August 24, 2010 at 7:41 am
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.
August 24, 2010 at 8:56 am
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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply