 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / Programming / SQL / December 2008using sp_executeSQL to create views
I have a situation where I have a SQL job that needs to create some views and sp's. The problem is that I need basically to have an sp create these views. I have thought about using sp_executesql, but how would I deal with the following sql command: select * from tblA where lname in ('Jones','Smith'). Will the single quotes cause an issue? Need to know ASAP. >I have a situation where I have a SQL job that needs to create some views >and [quoted text clipped - 5 lines] > quotes > cause an issue? Need to know ASAP. Single quotes are not a problem within variables. When putting literal strings in code you need to "escape" them by using two single quotes together. The problem is exactly how you construct a statement like the example you posted, ie what bits are dynamic? If only the names ('Jones','Smith') are dynamic then you'd probably be better off making this a table-valued function rather than a view. Functions can have paramers whereas views cannot. The concept of dynamic DDL is not something to be undertaken lightly and frequently there are better alternatives available. The main problems with the dynamic approach are maintainability, testability and especially the risks of creating a SQL injection vulnerability.  SignatureDavid Portas Smith and Jones are not the only names are things I am searching on, but this is what I was doing. I just wanted to confirm that it was correct. thnx > >I have a situation where I have a SQL job that needs to create some views > >and [quoted text clipped - 18 lines] > the dynamic approach are maintainability, testability and especially the > risks of creating a SQL injection vulnerability. > I have a situation where I have a SQL job that needs to create some views and > sp's. The problem is that I need basically to have an sp create these views. > I have thought about using sp_executesql, but how would I deal with the > following sql command: > select * from tblA where lname in ('Jones','Smith'). Will the single quotes > cause an issue? Need to know ASAP. Table valued UDFs might be a better choice for you than views. > I have a situation where I have a SQL job that needs to create some > views and sp's. The problem is that I need basically to have an sp > create these views. That sounds less than palatable. There are definitely better options. If you are do this in a job, using an Active-X task that runs a VB script that reads files from disk is a much better task. Using SQL to create views and procedures is a very poor choice. SignatureErland Sommarskog, SQL Server MVP, esquel@sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx I was going to do this, but it is a permissions issue, so not allowed to. > > I have a situation where I have a SQL job that needs to create some > > views and sp's. The problem is that I need basically to have an sp [quoted text clipped - 5 lines] > > Using SQL to create views and procedures is a very poor choice. > I was going to do this, but it is a permissions issue, so not allowed to. Then look for a different solution. Or make sure that you have the permissions. No matter whether you are employed or a consultant, it is a waste of money that you spend time on develop a complex and solution which also will be difficult to maintain, rather than a straightforward solution. If anyone tells you that you must do this in SQL, just answer "No, this can't be done in SQL, not without a serious increase in complexity and development time". SignatureErland Sommarskog, SQL Server MVP, esquel@sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2010 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.