Dynamic SQL in function - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Dynamic SQL in function

Author

Topic

kdfischer
Starting Member

2 Posts

Posted - 2010-08-24 : 09:44:23

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.

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.

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, maybe there's a better way.

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

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-08-24 : 09:54:23

Sounds like a terrible idea.

You shouldn't worry about formatting the data inside you SQL. that's a job for the front end. There are cases where dynamic SQL is a good answer (catch all queries where the query plan should be different for each iteration of the query for example) but this doesn't sound good from the little you've described.

I think you should take a step back and explain what this is all *for* rather than what you've tried to implement.

What's the purpose of the dynamic SQL stored inside the tables / how is it entered / what should be done with it?

Welcome to SQLTEAM.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-08-24 : 09:58:05

Also -- you are not using variable substitution for your dynamic SQL (you don't pass in variables with sp_executeSql) -- if you have to use dynamic SQL then this will make your life a *whole* lot easier and will result in much faster queries.

You should read this:
http://www.sommarskog.se/dynamic_sql.html

Best resource I've ever come across for dynamic sql.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

kdfischer
Starting Member

2 Posts

Posted - 2010-08-24 : 10:55:00

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

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-08-24 : 12:32:04

quote:

I'll build a SQL string and do just that with exec sp_executesq @sqlstring. Isn't that dynamic SQL?

Well yeah -- pretty much the definition. Please read the link I posted though. What you are doing right now (which is to change the text of the dynamic call every time isn't very efficient. you can pass paramaters to sp_executeSql so that the text doesn't change but the value of sql variables inside the dynamic sql does change. Anyway, that's a little off topic.

I'm not very experienced in Reporting Services, but I don't think it was designed to do the kind of things you want to do with it.

The reason you can't use dynamic sql inside functions is because functions aren't allowed to change data (they have to be deterministic) and part of the way that's enforced is that it can't call procs and it can't use dynamic sql.

I definitely wouldn't advise trying to do this in SQL. It's just not suited to this kind of work.

at most the sql should probably jsut be used to fetch the 'rules' for each variable for a front end client to do work with.

Sorry I couldn't be more help -- maybe someone else will have an idea.

Best of luck.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Kristen
Test

22859 Posts

Posted - 2010-08-24 : 13:24:09

Notwithstanding that you need to use Sharepoint / Reporting Services - this is the sort of thing that CMS's do well.

We have a system that does much like what you describe. Out Sprocs retrieve a TEMPLATE (the HTML containing {TAG} markers) and then a resultset from a query and our "CMS rendering engine" merges the two together - and deals with all the fall out of, for example, INPUT fields that need the content Encoding; caching - but knowing if a cached copy becomes stale relative to the Template / Data; handling Store-front and Language variations, and so on.

Dunno if that is any help, but hopefully food-for-thought at least.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources