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

SQL Server 2005 Forums

Transact-SQL (2005)

dynamic SQL

Author

Topic

jgrant
Yak Posting Veteran

69 Posts

Posted - 2008-03-10 : 21:17:04

I have this stored procedure that I am trying to pass a table name as a parameter.

This is my first experiments with dynamic sql and here is my code where I am building the command:

declare @cmd nvarchar(4000)
select @cmd = (N'update ' + @table + N' set totalproductsales = ' + cast(@ppa as nvarchar(50)) +
N', unitssold = ' + cast(@ppaLotionsSold as nvarchar(50)) +
N' where recordid = ' + cast(@recordid as nvarchar(50)))

EXEC sp_executesql @cmd

for some reason @cmd is empty when it hits the sp_executesql statement and I am not sure why. Does anybody have any thoughts?

The Yak Village Idiot

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-03-10 : 21:19:23

quote:
@cmd is empty

Empty string or NULL ?

Check all your variables @table, @ppa etc. Any one of them NULL ?

KH
[spoiler]Time is always against us[/spoiler]

jgrant
Yak Posting Veteran

69 Posts

Posted - 2008-03-10 : 21:24:16

nope, I checked them all and they all have data in them.

The Yak Village Idiot

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-03-10 : 21:30:35

do a print on all your variable to verify.

DECLARE @cmd 		nvarchar(4000),
	@TABLE		varchar(10),
	@ppa		int,
	@ppaLotionsSold	int,
	@recordid	int

SELECT	@TABLE 		= 'myTable',
	@ppa		= 1,
	@ppaLotionsSold	= 2,
	@recordid	= 3

SELECT @cmd = (N'UPDATE ' + @TABLE + N' SET totalproductsales = ' + CAST(@ppa AS nvarchar(50)) +
		N', unitssold = ' + CAST(@ppaLotionsSold AS nvarchar(50)) +
		N' WHERE recordid = ' + CAST(@recordid AS nvarchar(50)))

print @cmd
/*
UPDATE myTable SET totalproductsales = 1, unitssold = 2 WHERE recordid = 3
*/

KH
[spoiler]Time is always against us[/spoiler]

jgrant
Yak Posting Veteran

69 Posts

Posted - 2008-03-10 : 21:36:45

here was the output from my print statements:

0.00
0
kpi..performance

@cmd is still blank

Whats unusual is that i was getting errors when I didnt use the cast. But after I fixed the errors with the cast statements cmd was empty.

The Yak Village Idiot

jgrant
Yak Posting Veteran

69 Posts

Posted - 2008-03-10 : 21:41:20

ok, you were right. There was a null statement that was in the where clause that I was not printing. I am sorry for wasting your time. But the good news is that I executed my first dynamic sql statement. Thank you soooooooo much for everyones help

The Yak Village Idiot

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-03-10 : 21:41:42

can you change the data type of the variables in the testing script i posted to the actual ?

And do a select @table, @ppa, @ppaLotionsSold, @recordid to display out the values

quote:
Whats unusual is that i was getting errors when I didnt use the cast.

You are doing string concatenation, you will need to explicitly using cast or convert to convert your value to string

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-03-10 : 21:43:19

Good that you got it resolved.

KH
[spoiler]Time is always against us[/spoiler]

jgrant
Yak Posting Veteran

69 Posts

Posted - 2008-03-10 : 21:43:34

how is it in malaysia. I heard it was really pretty there.

The Yak Village Idiot

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-03-10 : 21:57:19

Yes it is. http://www.visit-malaysia.com/

KH
[spoiler]Time is always against us[/spoiler]

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-03-11 : 02:28:24

quote:
Originally posted by jgrant

But the good news is that I executed my first dynamic sql statement.

The bad news is that dynamic SQL
a) Sucks
b) Leaves you open to SQL injection
c) Means loads of SQL parsing giving you a performance hit
d) Usually means your data model is broken.

I'd look at what you are doing and try to fix it properly. My guess is that you have tables called something like sales2000, sales2001, sales2002 etc, or Product1, Product2, Product3

If you post more I'm sure you'll get plenty of advice on how to do this properly.

No matter how excited about it you are, dynamic SQL is a habit you want to get out of.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-03-13 : 06:47:13

All about dynamic sql
www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2008-03-13 : 16:31:25

quote:
Originally posted by khtan

Yes it is. http://www.visit-malaysia.com/

khtan,

Are you moonlighting as a tour guide?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-03-13 : 16:48:47

quote:
Originally posted by LoztInSpace
c) Means loads of SQL parsing giving you a performance hit

I don't want to start the stored procedure vs dynamic sql debate. And I don;t like dynamic sql either. But, what makes you think that dynamic sql is any slower than "regular" sql? (hint, it's not)

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-03-13 : 22:07:48

quote:
Originally posted by Lamprey

quote:
Originally posted by LoztInSpace
c) Means loads of SQL parsing giving you a performance hit

...what makes you think that dynamic sql is any slower than "regular" sql?

Let me clarify what I meant. The overhead comes from the additional parsing and non-reuse of the query plan not the execution, which will remain the same assuming the engine comes up with the same plan.

My rationale behind this stance:

Parsing is expensive in terms of CPU and can affect throughput in highly concurrent systems due to the serial nature of compilation (i.e. compilation needs to acquire exclusive locks on certain system resources).

Trivial case of dynamic SQL:

SELECT * from blah where wibble=23
followed by
SELECT * from blah where wibble=54

The query engine will not be able to find the second copy in the cache because it is actually different SQL with a different string/hash. Compare this to

SELECT * from blah where wibble=@wib

Becuase you have parameterised @wib, the SQL will be identical in both cases making it likely it will be found (and retained) in the cache and thus reused.

That said, there are now smarts to strip out the constants, effectively taking certain classes of SQL, parameterising it and then checking. This cannot be used in all cirmumstances and you can lose any advantage you gained by using specific constants by falling foul of unwanted parameter sniffing.

Much more detail here
[url]http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx[/url]

But to summarise - Dynamic queries will often be slower because of the parsing overhead. Assuming identical execution plans, the actual extraction of data will naturally be comparable no matter how the plan was established.

Hope this clears my point up.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-03-14 : 01:25:20

quote:
Originally posted by KenW

quote:
Originally posted by khtan

Yes it is. http://www.visit-malaysia.com/

khtan,

Are you moonlighting as a tour guide?

Would like to but i can't. I am back in Singapore now

KH
[spoiler]Time is always against us[/spoiler]

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-03-14 : 12:19:46

quote:
Originally posted by LoztInSpace

quote:
Originally posted by Lamprey

quote:
Originally posted by LoztInSpace
c) Means loads of SQL parsing giving you a performance hit

...what makes you think that dynamic sql is any slower than "regular" sql?

But to summarise - Dynamic queries will often be slower because of the parsing overhead. Assuming identical execution plans, the actual extraction of data will naturally be comparable no matter how the plan was established.

Hope this clears my point up.

Thanks for the clarification and the link. :)

I'll have to give that article a read this weekend when I have some time to dedicate to it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources