Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » Dynamic SQL
Add to briefcase
19 posts, Page 1 of 2
12»»

Dynamic SQL Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
SQL_Kills
Posted Wednesday, December 05, 2012 1:26 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 9:53 AM
Points: 51, Visits: 398
Hi,

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

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

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

Group: General Forum Members
Last Login: Thursday, May 09, 2013 2:42 PM
Points: 477, Visits: 3,649
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
CapnHector
Posted Wednesday, December 05, 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: 2 days ago @ 10:07 AM
Points: 935, Visits: 1,709
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
Sean Pearce
Posted Tuesday, December 11, 2012 1:46 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 11:42 PM
Points: 339, Visits: 1,299


http://thesqlguy.blogspot.com/
Post #1394933
SQL_Kills
Posted Tuesday, December 11, 2012 11:48 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 9:53 AM
Points: 51, Visits: 398
Sorry what is the above meant to be saying to me?
Post #1395268
Sean Pearce
Posted Tuesday, December 11, 2012 12:05 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 11:42 PM
Points: 339, Visits: 1,299
SQL_Kills (12/11/2012)
Sorry what is the above meant to be saying to me?

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

http://thesqlguy.blogspot.com/
Post #1395273
GSquared
Posted Tuesday, December 11, 2012 12:25 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Monday, May 06, 2013 1:09 PM
Points: 15,439, Visits: 9,569
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
MyDoggieJessie
Posted Tuesday, December 11, 2012 12:58 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 8:50 AM
Points: 2,035, Visits: 3,759
@Sean...THAT's AWESOME

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

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

Group: General Forum Members
Last Login: Thursday, May 16, 2013 11:06 PM
Points: 179, Visits: 380
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
GSquared
Posted Wednesday, December 12, 2012 6:26 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Monday, May 06, 2013 1:09 PM
Points: 15,439, Visits: 9,569
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
19 posts, Page 1 of 2
12»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.