Dynamic SQL - 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

Author

Topic

ingineu
Yak Posting Veteran

89 Posts

Posted - 2005-09-07 : 21:44:53

I am passing a FILTER string to a stored procedure and am therefore using dynamic SQL. Is there a better way of handling the following code. I am thinking it would be difficult for someone else to apply changes to the sproc.

@XFilter varchar(1000)AS

Declare @Sql varchar(1000)
Set @Sql = 'SELECT SHPH.*,SHPD.TransCd, SHPD.Vendor, SHPD.Quantity, SHPD.UnitRate,' +
'SHPD.InvoiceNo, SHPD.AmountPaid, SHPD.DatePaid, SHPD.Adjustment, SHPD.USExchRate,' +
'QShipQty = CASE WHEN SHPD.TransCd = ''F'' then Null ELSE SHPD.Quantity END, ' +
'QCost = CASE WHEN SHPD.TransCd = ''F'' then Null ' +
'WHEN SHPH.ProductTaxable = True then ... calc here ... ELSE ... calc ... END, ' +
'PROD.ProductName, PROD.ProductTaxable, PROD.ProductGLAcct, ' +
' (SELECT PSTRate FROM tblCtrl WHERE CtrlID = ''CTRL'') AS PSTRate ' +
'FROM tblShipmentHdr SHPH ' +
'LEFT OUTER JOIN tblProducts PROD ON SHPH.ProductID = PROD.ProductID ' +
'LEFT OUTER JOIN tblShipmentDet SHPD ON SHPH.ShipmentID = SHPD.ShipmentID ' +
@XFilter + ' ORDER BY SHPH.ProductID, SHPH.ReceivedDate'
Exec (@Sql)

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-09-07 : 22:21:26

I don't see why you feel this need to be dynamic. What kind of values will @XFilter have? Typically, you would have a seperate parameter for each filter criteria and apply that to the WHERE clause.

Also, a couple suggestions:
I agree that people will have difficulty applying changes to this. Some formatting would make it much more readable :)
You should use an explicit column list instead of "*".
Does your PSTRate subquery just return one value? If not, you'll get an error. If so, you should probably just join to that table. Anyway here is how I'd format the statement if it was me:


SELECT	SHPH.<Use explicit Column List>
	,SHPD.TransCd
	,SHPD.Vendor
	,SHPD.Quantity
	,SHPD.UnitRate
	,SHPD.InvoiceNo
	,SHPD.AmountPaid
	,SHPD.DatePaid
	,SHPD.Adjustment
	,SHPD.USExchRate
	,QShipQty =	CASE 
			WHEN SHPD.TransCd = 'F' then Null 
			ELSE SHPD.Quantity 
			END
	,QCost =	CASE 
			WHEN SHPD.TransCd = 'F' then Null
			WHEN SHPH.ProductTaxable = True then ... calc here ... 
			ELSE ... calc ... 
			END
	,PROD.ProductName
	,PROD.ProductTaxable
	,PROD.ProductGLAcct
	,PSTRate = 	(
			SELECT PSTRate 
			FROM	tblCtrl 
			WHERE	CtrlID = 'CTRL'
			)
FROM	tblShipmentHdr SHPH
LEFT JOIN tblProducts PROD 
		ON SHPH.ProductID = PROD.ProductID
LEFT JOIN tblShipmentDet SHPD 
		ON SHPH.ShipmentID = SHPD.ShipmentID
--@XFilter + 
ORDER BY SHPH.ProductID, SHPH.ReceivedDate

Be One with the Optimizer
TG

ingineu
Yak Posting Veteran

89 Posts

Posted - 2005-09-07 : 23:35:40

@XFilter contains the WHERE clause that allows the user to filter records by fieldnames and values. Since we are not just talking about a few options, I thought that dynamic SQL was the way to go.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-09-08 : 00:31:53

Since you will only be joining 3 tables I don't think you need to build a dynamic statement. Use seperate parameters for each option. That will make efficient and secure sql that can be optimized from within the procedure instead of needing to change you application when you need to tweek the statement. You still have the option to build dynamic sql in the sp if you need to but I don't think you will. If you want to see an example of how to do it, post the options.

Be One with the Optimizer
TG

ingineu
Yak Posting Veteran

89 Posts

Posted - 2005-09-08 : 02:02:15

Not possible as the filters can contain the following:
(Left(ProductID,4) = '1245' And ProductAcct = '1010') Or (ProductID = '123900')

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-09-08 : 09:36:28

>>Not possible
Oooh, you really know how to motivate me

You can probably tell, I like to avoid building complete statement clauses outside of the procedure that will execute them. I hope someone will chime in to agree/disagree with me.

Looks like you want to apply boolean logic to your where clause which is entirely possible in straight sql. If you're interested in seeing if I (or anyone else) can suggest some solutions how, post the business rules as best you can that discribes the logic you need.

Otherwise, except for the suggestions I made above, the only other thing I would suggest is to use sp_executesql instead of exec. (see Books Online about sp_executesql)

Be One with the Optimizer
TG

Kristen
Test

22859 Posts

Posted - 2005-09-08 : 11:09:07

If you are after performance you need to do a couple of things:

Use sp_ExecuteSQL - this will cache query plans

Make sure that all tables have their owner explicitly named (otherwise unlikely to be cached)

As TG said:

You can ditch the string concatenation and lay out the quoted string over multiple lines (the CR/LF and a couple of TABs doesn't take up much more of the varchar than a separating space)

You do need to use explicit column lists instead of "*" to ensure that query plan is cached

You need to sort out the PSTRate subquery - either use an aggregate, like MAX(), or JOIN the table

And then the biggie! Parameterise the @XFilter and pass to sp_ExecuteSQL - what you are aiming for is that query is presented as, say,

SELECT ...
FROM ...
WHERE (Left(ProductID,4) = @Parameter1 And ProductAcct = @Parameter2) Or (ProductID = @Parameter3)

because the query plan will be cached if the same query is presented again - there is a fair chance that your queries will occur again, albeit with different parameters.

The downside is that the parameters have to be pre-defined - but you could just have 10 varchar parameters, say, @Parameter1 ... @Parameter10.

it doesn't matter if some of the @Parameters are not used in [some of] the Query(s)

So what you would wind up with is something like:


CREATE PROCEDURE dbo.MySProc
	@XFilter varchar(8000),
	@Parameter1 varchar(10),
	...
	@Parameter10 varchar(10)
AS

Declare @Sql varchar(8000)
Set @Sql = 
'SELECT	SHPH.*,	-- Need to explicitly name all columns
	SHPD.TransCd, 
	SHPD.Vendor, 
	SHPD.Quantity, 
	SHPD.UnitRate,
	SHPD.InvoiceNo, 
	SHPD.AmountPaid, 
	SHPD.DatePaid, 
	SHPD.Adjustment, 
	SHPD.USExchRate,
	QShipQty = CASE WHEN SHPD.TransCd = ''F'' 
			THEN NULL 
			ELSE SHPD.Quantity 
			END, 
	QCost = CASE WHEN SHPD.TransCd = ''F'' 
			THEN NULL 
			WHEN SHPH.ProductTaxable = True 
			THEN 
... calc here ... 
			ELSE 
... calc ... 
			END, 
	PROD.ProductName, 
	PROD.ProductTaxable, 
	PROD.ProductGLAcct,  
	(
-- If this returns multiple rows the query will fail, no?
		SELECT	PSTRate 
		FROM	dbo.tblCtrl
		WHERE	CtrlID = ''CTRL''
	) AS PSTRate 
FROM	dbo.tblShipmentHdr SHPH 
	LEFT OUTER JOIN dbo.tblProducts PROD 
		ON SHPH.ProductID = PROD.ProductID 
	LEFT OUTER JOIN dbo.tblShipmentDet SHPD 
		ON SHPH.ShipmentID = SHPD.ShipmentID
' + @XFilter + '
ORDER BY SHPH.ProductID, SHPH.ReceivedDate'
Exec (@Sql)
EXEC sp_ExecuteSQL @Sql, 
	N'@Parameter1 varchar(10), ... @Parameter10 varchar(10)',
	@Parameter1, ... @Parameter10

Kristen

ingineu
Yak Posting Veteran

89 Posts

Posted - 2005-09-08 : 12:06:28

That would work if my WHERE clause was consistent, however, I allow users to go after Dollar fields as well, such as:
(ProductID = '121000' AND SHPD.Quantity >= 200) ... just one example
I have created Filter options that operate in much the same way as Quicken, in that they can select a fieldname, operator and value + and/or conditions. I cannot see how to avoid the @XFilter. I have run the query, and performance was not an issue (response in seconds). And yes, there is only 1 'CTRL' record.

Kristen
Test

22859 Posts

Posted - 2005-09-08 : 12:14:51

"if my WHERE clause was consistent"

Its not confined to requiring the WHERE clause to be consistent, but it does require that the same "template" crops up again - so if each and every one is different then there is no gain. But if, as I suspect, your users re-use/re-create templates (with different parameter values of course) then it will be faster.

So if I do:


SELECT @Parameter1 = '121000', @Parameter2 = 200,
       @XFilter = '(ProductID = @Parameter1 AND SHPD.Quantity >= @Parameter2)'

then that gets cached for the next person who does a specific ProductID and Quanty >= SomeValue search.

Of course if they do Quantity < SomeValue then that's a different query, and will get into the cache separately. Any that are "frequently" used will stay in the cache.

All this only makes a difference if there is likely to be a performance issue - which usually means that either you have a "big" database, very complex queries [i.e. which take some time for the parser to prepare a query plan] or a shared server.

Kristen

Kristen
Test

22859 Posts

Posted - 2005-09-08 : 12:18:10

"And yes, there is only 1 'CTRL' record"

In that case I think it would be more efficient to JOIN it (it doesn't matter that it doesn't have any joining "keys"):


SELECT ...
	CTRL.PSTRate
FROM	dbo.tblShipmentHdr SHPH 
	LEFT OUTER JOIN dbo.tblProducts PROD 
		ON SHPH.ProductID = PROD.ProductID 
	LEFT OUTER JOIN dbo.tblShipmentDet SHPD 
		ON SHPH.ShipmentID = SHPD.ShipmentID
	JOIN dbo.tblCtrl as CTRL
		ON tblCtrl.CtrlID = ''CTRL''

Kristen

ingineu
Yak Posting Veteran

89 Posts

Posted - 2005-09-08 : 14:19:37

You can ditch the string concatenation and lay out the quoted string over multiple lines
Thank you Kristen, this simplifies the coding, also I have replaced the SELECT Ctrl with a JOIN.
Thank you TG, for the Statement layout, much easier to read.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources