Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Dynamic SQL Expand / Collapse
Author
Message
Posted Wednesday, December 5, 2012 1:26 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, November 27, 2014 2:12 PM
Points: 118, Visits: 623
Hi,

has anyone got an example of a Dynamic SQL and what is it and why it would benefit using this?

Thanks
Post #1393188
Posted Wednesday, December 5, 2012 2:04 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 6:26 AM
Points: 477, Visits: 3,689
All your answers lie here:
http://www.sommarskog.se/dynamic_sql.html


______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
Post #1393201
Posted Wednesday, December 5, 2012 2:18 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, September 19, 2014 5:16 AM
Points: 887, Visits: 1,774
toddasd (12/5/2012)
All your answers lie here:
http://www.sommarskog.se/dynamic_sql.html


Another person who posts the same dynamic sql article.

i highly recommend the suggested reading.



For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw

Need to Split some strings? Jeff Moden's DelimitedSplit8K
Jeff Moden's Cross tab and Pivots Part 1
Jeff Moden's Cross tab and Pivots Part 2

Jeremy Oursler
Post #1393212
Posted Tuesday, December 11, 2012 1:46 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, December 23, 2014 1:44 AM
Points: 1,001, Visits: 3,091




The SQL Guy @ blogspot

@SeanPearceSQL

About Me
Post #1394933
Posted Tuesday, December 11, 2012 11:48 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, November 27, 2014 2:12 PM
Points: 118, Visits: 623
Sorry what is the above meant to be saying to me?
Post #1395268
Posted Tuesday, December 11, 2012 12:05 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, December 23, 2014 1:44 AM
Points: 1,001, Visits: 3,091
SQL_Kills (12/11/2012)
Sorry what is the above meant to be saying to me?

http://en.wikipedia.org/wiki/SQL_injection




The SQL Guy @ blogspot

@SeanPearceSQL

About Me
Post #1395273
Posted Tuesday, December 11, 2012 12:25 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, December 18, 2014 9:58 AM
Points: 13,872, Visits: 9,600
SQL_Kills (12/11/2012)
Sorry what is the above meant to be saying to me?


One of the common flaws in dynamic SQL is that it allows what's called "SQL Injection". Simple version = someone types SQL commands into a form field, and the commands get run in the database. Hence "Robert DROP TABLE dbo.Students" caused the table dbo.Students to be dropped.

It's an important thing to know about if you deal at all with dynamic SQL. Whether that's in the database layer (stored procedures that use dynamic SQL), or in the application (inline SQL), or anywhere in between (Data Access Layer, and so on).

There are methods to avoid this issue. The comic is about that.


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1395276
Posted Tuesday, December 11, 2012 12:58 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, November 24, 2014 5:46 AM
Points: 4,031, Visits: 7,172
@Sean...THAT's AWESOME

______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience"
Post #1395290
Posted Wednesday, December 12, 2012 4:06 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, November 27, 2014 11:02 PM
Points: 251, Visits: 618
See Dyanmic Sql is used where we dont want to hard code the values in
our query.
For example if we wan to run this command against a database
than
Create procedure dynamic_query(
@dbname As nvarchar(25)
)
as
BEGIN

declare @sql_txt nvarchar(MAX)

SET @sql_txt=' Use '+@dbname +' select * from sys.objects'
EXEC(@SQL)
END
Post #1395571
Posted Wednesday, December 12, 2012 6:26 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, December 18, 2014 9:58 AM
Points: 13,872, Visits: 9,600
jeetsingh.cs (12/12/2012)
See Dyanmic Sql is used where we dont want to hard code the values in
our query.
For example if we wan to run this command against a database
than
Create procedure dynamic_query(
@dbname As nvarchar(25)
)
as
BEGIN

declare @sql_txt nvarchar(MAX)

SET @sql_txt=' Use '+@dbname +' select * from sys.objects'
EXEC(@SQL)
END


I would recommend modifying that so that it first checks to see if @dbname is a valid database name (check vs sys.databases), then use QuoteName() to make sure it's going to deal with non-alpha characters correctly.

These two steps will avoid a number of potential errors, and also make it injection-safe.

Also, Exec() won't accept an NVarchar(max) input string.


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1395627
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse