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
 Site Related Forums
 Article Discussion
 Article: Using Dynamic SQL in Stored Procedures

Author  Topic 

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2011-03-07 : 09:35:02
Dynamic SQL allows stored procedures to “write” or dynamically generate their SQL statements. The most common use case for dynamic SQL is stored procedures with optional parameters in the WHERE clause. These are typically called from reports or screens that have multiple, optional search criteria. This article describes how to write these types of stored procedures to they execute well and resist SQL injection attacks.

Read Using Dynamic SQL in Stored Procedures

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2011-03-08 : 11:29:03
I didn't see anything about the parameter sniffing issue?

Article mentions parameters are type checked to avoid SQL injection - yet they just happen to be integers!

I like the '1=1' trick.
Go to Top of Page

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2011-03-08 : 12:00:29
If the URL has an integer as a parameter, that is type checked when it is passed into the stored procedure. Probably should have added the caveat that you need to call using RPC calls rather than straight SQL.

=================================================
Creating tomorrow's legacy systems today. One crisis at a time.
Go to Top of Page

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2011-03-08 : 13:52:19
quote:
Originally posted by graz

If the URL has an integer as a parameter, that is type checked when it is passed into the stored procedure.


Just raising the concern that, although in the article the Dynamic parameters are ints, sometimes one may need to pass varchars to the Dynamic SQL.
Go to Top of Page

yiangos
Starting Member

1 Post

Posted - 2011-03-09 : 08:16:32
It doesn't matter whether you want to pass in ints, varchar's or datetimes or whatnot. As long as the type mentioned in the parameterlist string is the same as the type of the variable holding the value, it will work.

So for instance, the following code is valid, and shoud run without issues:



--create a table

create table #test(
id bigint identity(1,1) Primary key,
someText nvarchar(50) not null,
someInt int not null
)



--fill in some values

insert into #test
SELECT 'foo',1
UNION ALL SELECT 'foo',2
UNION ALL SELECT 'bar',2
UNION ALL SELECT 'bar',2
UNION ALL SELECT 'baz',1



--create sql command and parameter list
--(sql 2005 and forward can use nvarchar(MAX) instead of nvarchar(4000)

DECLARE @sql NVARCHAR(4000)
DECLARE @sqlList NVARCHAR(4000)
SET @sql=N'SELECT * FROM #test WHERE id=@RequiredId OR (someText=@SearchText AND someInt=@SearchValue)'
SET @sqlList=N'@RequiredId BIGINT, @SearchText NVARCHAR(50), @SearchValue INT'



--These could be the input parameters of the
--stored procedure where you've put the dynamic sql

DECLARE @ActualSearchIdValue BIGINT
DECLARE @ActualTextValue NVARCHAR(50)
DECLARE @ActualIntValue INT

SET @ActualSearchIdValue =1
SET @ActualTextValue ='bar'
SET @ActualIntValue =2


--execute the dynamic sql

EXEC sp_executeSQL
@sql,
@sqlList,
@RequiredId = @ActualSearchIdValue ,
@SearchText = @ActualTextValue ,
@SearchValue = @ActualIntValue


--clean up

drop table #test


the above should yield 3 records, the following:


id | someText | someInt
------------------------------
1 | foo | 1
3 | bar | 2
4 | bar | 2


Regarding the 1=1 trick mentioned above, as long as you do not have interpolated values (e.g. pass the table names dynamically), using sp_executesql is pretty much safe against such types of sql injection attacks. To pass the 1=1 trick, one usually has to pass something like the following

' or 1=1


to an interpolated sql string. Try this approach even with the above trivial example and you will immediately notice that either (a) SQL throws an error due to type mismatch (if you try to assign that to an int variable) or (b) the 'or 1=1 string is taken LITERALLY, as the nvarchar variable value, and not parsed by the SQL engine.

However, if you're using dynamic SQL to pass table names, that's an entirely different ball game...

HTH

Yiangos

Feu! Edome8a upo rhnnosxhmwn lukwn!
(Alas! We're devourde by lamb-guised wolves!)
Go to Top of Page

pDaleC
Starting Member

1 Post

Posted - 2011-03-22 : 10:02:39
Thanks for the informative article! In repayment, might I suggest replacing your PRINT debug statement with:

RAISERROR( '%s
@CustomerParameter = %d,
@ContactParameter = %d', 0,1,
@SQL, @CustomerID, @ContactID) WITH NOWAIT;

This way, you'll see the statement(s) as it's running (rather than them all showing up when it is done),
and know what the parameters are.

><(((`> - - - - - - - - - - - - - - - - - - - - - - - - - - <')))><
pDaleCampbell
"Don't ping my cheese with your bandwidth!" - Dogbert
Go to Top of Page

dsutherland
Starting Member

1 Post

Posted - 2011-03-22 : 10:50:21
Although Dynamic SQL is a tool that is valuable in certain circumstances, I do believe it is a tool of *last* resort. For example, we could have written the query in the article without dynamic sql as follows:

SELECT [SalesOrderID], [OrderDate], [Status],
[CustomerID], [ContactID]
FROM [Sales].[SalesOrderHeader]
WHERE CustomerID = ISNULL(@CustomerID, CustomerID)
AND ContactID = ISNULL(@ContactID, ContactID)


Another common reason I have seen for using dynamic sql is for "dynamic top rows" as in 'select top X from table where ... order by ...'. This can easily be solved with common table expressions, as in:

with DynamicTopRows
(
select row_number() over (order by <...>) as RowNum, *
from <table>
where <criteria>
)
select <...>
from DynamicTopRows
where RowNum <= @X

My point is simply to remind us to look first to non-dynamic sql solutions, so you don't have to worry about sql-injection.

As you can tell, dynamic-sql kind of scares me and I tend toward locking down sp_executesql. That said, I truly enjoyed the article. Thank you for taking the time to write it.
Go to Top of Page

patrick2525
Starting Member

1 Post

Posted - 2011-03-22 : 15:43:09
Wouldn't adding the 'OPTION(RECOMPILE)' to the @sql string be a good idea, or is this not necessary?
Go to Top of Page

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2011-04-05 : 01:25:19
quote:
Originally posted by dsutherland

SELECT [SalesOrderID], [OrderDate], [Status],
[CustomerID], [ContactID]
FROM [Sales].[SalesOrderHeader]
WHERE CustomerID = ISNULL(@CustomerID, CustomerID)
AND ContactID = ISNULL(@ContactID, ContactID)




This is the exact type of code I'm trying to avoid. Using ISNULL around the column prevents the use of indexes on those columns so this will always generate table scans. That's why you use dynamic SQL in these cases.

=================================================
Creating tomorrow's legacy systems today. One crisis at a time.
Go to Top of Page

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2011-04-05 : 01:27:13
quote:
Originally posted by patrick2525

Wouldn't adding the 'OPTION(RECOMPILE)' to the @sql string be a good idea, or is this not necessary?



It isn't necessary. In fact I don't want it to recompile. There are two possible parameters yielding 4 possible combinations. I expect it will generate a reusable query plan for each combination. That's exactly what I want so that each query plan will use indexes properly.

=================================================
Creating tomorrow's legacy systems today. One crisis at a time.
Go to Top of Page

simkin2004
Starting Member

2 Posts

Posted - 2011-07-22 : 10:28:17
quote:
Originally posted by graz

quote:
Originally posted by dsutherland

SELECT [SalesOrderID], [OrderDate], [Status],
[CustomerID], [ContactID]
FROM [Sales].[SalesOrderHeader]
WHERE CustomerID = ISNULL(@CustomerID, CustomerID)
AND ContactID = ISNULL(@ContactID, ContactID)




This is the exact type of code I'm trying to avoid. Using ISNULL around the column prevents the use of indexes on those columns so this will always generate table scans. That's why you use dynamic SQL in these cases.

=================================================
Creating tomorrow's legacy systems today. One crisis at a time.



The indexes can be added back to this query (without using Dynamic SQL) with a simple OR statement.

SELECT [SalesOrderID], [OrderDate], [Status],
[CustomerID], [ContactID]
FROM [Sales].[SalesOrderHeader]
WHERE (CustomerID IS NULL OR CustomerID = @CustomerID)
AND (ContactID IS NULL OR ContactID = @ContactID)
Go to Top of Page

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-07-22 : 11:48:32
quote:
Originally posted by simkin2004

quote:
Originally posted by graz

quote:
Originally posted by dsutherland

SELECT [SalesOrderID], [OrderDate], [Status],
[CustomerID], [ContactID]
FROM [Sales].[SalesOrderHeader]
WHERE CustomerID = ISNULL(@CustomerID, CustomerID)
AND ContactID = ISNULL(@ContactID, ContactID)




This is the exact type of code I'm trying to avoid. Using ISNULL around the column prevents the use of indexes on those columns so this will always generate table scans. That's why you use dynamic SQL in these cases.

=================================================
Creating tomorrow's legacy systems today. One crisis at a time.



The indexes can be added back to this query (without using Dynamic SQL) with a simple OR statement.

SELECT [SalesOrderID], [OrderDate], [Status],
[CustomerID], [ContactID]
FROM [Sales].[SalesOrderHeader]
WHERE (CustomerID IS NULL OR CustomerID = @CustomerID)
AND (ContactID IS NULL OR ContactID = @ContactID)



Not a good idea -- this is still a potential time bomb of a performance problem. More so when you add multiple additional clauses.

Also the where clause should have been:

WHERE
(@CustomerID IS NULL OR [CustomerID] = @CustomerID)
AND (@ContactID IS NULL OR [ContactID] = @ContactID)

Gail wrote a great article called "Catch All Queries"
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

I recommend you read it.

This is one of the few areas where I think dynamic SQL is the best choice.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

simkin2004
Starting Member

2 Posts

Posted - 2011-08-08 : 12:40:41
Thank you, Transact Charlie.

WOO HOO! Safe Performant Dynamic SQL, here we come!
Go to Top of Page

jbreslow
Starting Member

16 Posts

Posted - 2011-08-25 : 15:26:07
I am trying to create a dynamic/ad-hoc query/reporting tool and somebody told me that the best way to do this is to create a dynamic Stored Procedure. I am fairly new to SP's and very new to dynamic queries in a SP.

The last like of this article worries me, "It’s best not to write code that accepts table and column names as parameters. That is an easy approach for SQL injection to attack. If that code must be written, pay careful attention to sanitizing those parameters before using them."

Isn't that what I need to do to create a dynamic query... pass in table and column names? How would I 'sanitize' those params?

Is there an example of a dynamic query that has column names, table names and joins as params?
Go to Top of Page

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-08-26 : 06:02:51
You should read this first:
http://www.sommarskog.se/dynamic_sql.html

Then start a new post describing what is is that you want to do (in as much detail as possible).

The dynamic sql described here (in this post) wouldn't take in a column or table name -- it would only build a tuned query for a certain permutation of parameters.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2011-09-07 : 15:03:03
quote:
Originally posted by simkin2004

Thank you, Transact Charlie.

WOO HOO! Safe Performant Dynamic SQL, here we come!




What have you done Bill!!!!!

I still like my Join hack

http://weblogs.sqlteam.com/brettk/archive/2004/05/05/1312.aspx


Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/


Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-09-07 : 15:36:35
Or-clauses most often doesn't give good plan. With the new CROSS APPLY, you can do the same logic like this and get a better plan.
SELECT		o.* 
FROM dbo.Orders AS o
CROSS APPLY (
SELECT CASE WHEN o.OrderDate = @OrderDate THEN 1 ELSE 0 END
+ CASE WHEN o.RequiredDate = @RequiredDate THEN 1 ELSE 0 END
+ CASE WHEN o.ShippedDate = @ShippedDate THEN 1 ELSE 0 END
) AS f(Ranks)
WHERE f.Ranks > 0
ORDER BY f.Ranks DESC



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2011-09-08 : 03:08:15
Isn't that providing a match if one of the parameters matches?

Whereas the original query was that any non-NULL @Parameter should match? Not sure the best (i.e. query optimiser friendly) way of doing that though.
Go to Top of Page

hajaghareza
Starting Member

1 Post

Posted - 2012-12-14 : 11:53:16
how can i do if i want to get data in dynamic sql and set it in a variable like this :
declare @sql nvarchar(50)
declate @test int
set @sql ='select @test= min(my col)from my table'
execute(sql)
i try it but not insert the result of select in @test ??
if it's wrong please help me
Go to Top of Page

garyjohn
Starting Member

7 Posts

Posted - 2013-03-05 : 04:14:35
I'm not quite familiar with stored procedure, but I heard about an easier to replace stored procedure, esProc. It act as a database for multiple data sources as a business computing tools, without complex SQL or R scripting, but more visualized step by step computing.

Hope it can help you solve the problem with stored procedures more agilely.

business analyst, marketing director
Go to Top of Page

valeria
Starting Member

6 Posts

Posted - 2013-03-15 : 05:59:02
But How it works? I am trying it too hard but still not getting any way for the solution.
Go to Top of Page
    Next Page

- Advertisement -