Dynamic query in sp - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Dynamic query in sp

Author

Topic

rammohan
Posting Yak Master

212 Posts

Posted - 2008-10-23 : 03:14:35

Hi,

I know Stored Procdeure after running first time sores the execution plan and there for stored procedures are fast than simple text queries

I want to ask u that if i have written dynamic Query in stored procedure Will it do the same and stored the execution plan now or it will make the execution

Plan Again and again on calling Stored Procedures .

Also Tell that If iam using Dynamic query in that is performance gets slow by that .

I have certain situations where i have no options left and i have to use dynamic query in Sp

then what happen to the execution Plan I want to know

My performance matter is at the top ie I want to execute the query fast.

Here is test example where I am using dynamic query

Create proc TestProc

@IdList

as
Set NoCount On

Begin

declare @sql as nvarchar(100)

Set @sql='SELECT * from TestTable WHERE testId Not IN ('+@IdList+')'
exec sp_executesql @sql

End

Set NoCount off

One can never consent to creep,when one feels an impulse to soar
RAMMOHAN

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-23 : 03:43:33

no need of dynamic sql in above case. you can just use

SELECT * from TestTable WHERE ','+@IdList+',' NOT LIKE  '%,'+cast(testId as varchar(10)) + ',%'

rammohan
Posting Yak Master

212 Posts

Posted - 2008-10-23 : 04:48:45

I have given test example

There are cases when i have to used dynamic queries no other option

So please Consider my Problem i had asked above

One can never consent to creep,when one feels an impulse to soar
RAMMOHAN

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-23 : 05:03:35

http://www.4guysfromrolla.com/webtech/sqlguru/q120899-2.shtml

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-10-23 : 05:19:35

That's a very concise link Visakh.

Rammohan, I've always considered this page (http://www.sommarskog.se/dynamic_sql.html) to be one of the most useful info pages on dynamic SQL that exists.

If you have any questions about dynamic SQL I'd be very surprised if that doesn't answer them.

Just in case you haven't bothered to check out the links posted then the really short answer is:

Yes -- you can cache a query plan for a piece of dynamic sql but only if you call it with EXEC sp_executeSql rather than EXEC(). However, depending on the nature of the dynamic sql, the execution plan might be completely pointless. Check the links.

Regards

-------------
Charlie

rammohan
Posting Yak Master

212 Posts

Posted - 2008-10-24 : 07:15:42

hi visakh16 & Transact charlie

i had read u r articles, those r excellent.
I have a query

that in my above procedure in dynamic query

instead of using

Set @sql='SELECT * from TestTable WHERE testId Not IN ('+@IdList+')'

exec sp_executesql @sql

if i use in this way

Set @sql='SELECT * from TestTable WHERE testId Not IN (@IdList)'

exec sp_executesql @sql, N'@IdList varchar(50)',@IdList

Then I want to ask in which Case the execution plan will not change if i@IdList value changes.

My query is that i want to store the execution pln in cache and not to recompile again and again.

Plz Help Me.

Thanks.

One can never consent to creep,when one feels an impulse to soar
RAMMOHAN

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-10-24 : 08:17:47

Hi Rammohan,

This doesn't look like a good candidate for dynamic sql in the first place. How are you generating / getting passed @idList? I'm particularly worried that this might leave you open to a form of sql injection

I'm assuming it's a string that looks like
'12,32,32,1,2,4,7,45,6756' ?

If it is then it is far, far better to parse the list (using a table value function if you want) storing the values in a temp table or table variable (or joining the function direct)

Example


IF EXISTS (
	SELECT * from dbo.sysobjects
	WHERE 
		id = object_id(N'[dbo].[fn_Split]')
		AND xtype IN (N'FN', N'IF', N'TF')
	)
	DROP FUNCTION [dbo].[fn_Split]
GO


CREATE FUNCTION fn_Split (@text VARCHAR(MAX), @delimiter VARCHAR(20) = ' ')

	RETURNS @Strings TABLE (
		[position] INT IDENTITY PRIMARY KEY
		, [value] INT
		)

AS BEGIN
	DECLARE @index int
	SET @index = -1

	WHILE (LEN(@text) > 0) BEGIN
		-- Find the first delimiter
		SET @index = CHARINDEX(@delimiter , @text)

		-- No delimiter left?
		-- Insert the remaining @text and break the loop
		IF (@index = 0) AND (LEN(@text) > 0) BEGIN  
			INSERT INTO @Strings VALUES (CAST(@text AS INT))
			BREAK 
		END 

		-- Found a delimiter
		-- Insert left of the delimiter and truncate the @text
		IF (@index > 1) BEGIN
			INSERT INTO @Strings VALUES (CAST(LEFT(@text, @index - 1) AS INT))
			SET @text = RIGHT(@text, (LEN(@text) - @index))
		END
		-- Delimiter is 1st position = no @text to insert
		ELSE SET @text = CAST(RIGHT(@text, (LEN(@text) - @index)) AS INT)
	END
	RETURN
END
GO


DECLARE @list VARCHAR(MAX)

SELECT @list ='12412,123,123,123,52341,23124,5231,12312,31,62,1'

DECLARE @testTable TABLE (
	[testId] INT
	, [value] VARCHAR(255)
	)

INSERT @testTable ([testId], [value])
      SELECT 1, 'foo'
UNION SELECT 12412, 'bar'
UNION SELECT 22222222, 'should not appear'

-- Find matches
SELECT
	tt.*
FROM
	@testTable tt
	JOIN fn_split(@list, ',') filt ON filt.[value] = tt.[testId]

-- Find where there are no matches
SELECT
	tt.*
FROM
	@testTable tt
WHERE
	tt.[testId] NOT IN (SELECT [value] FROM fn_split(@list, ','))

This eliminates the need for any dynamic sql (which completely protects you from any injection attacks).

It's also very efficient.

If you've got any questions about the SQL just ask.

-------------
Charlie

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources