I'd like a script to drop all tables whose name begins with a given string. I'm sure this can be done with some dynamic sql and the INFORMATION_SCHEMA tables. If anyone has a script, or can knock one up quickly, please post it. If no-one posts an answer before I figure it out myself, I'll post my solution. ...
I'm looking for a pattern for performing a dynamic search on multiple tables. I have no control over the legacy (and poorly designed) database table structure. Consider a scenario similar to a resume search where a user may want to perform a search against any of the data in the resume and get back a list of resumes that match their se...
I've spent a good amount of time coming up with solution to this problem, so in the spirit of this post, I'm posting it here, since I think it might be useful to others. If anyone has a better script, or anything to add, please post it. Edit: Yes guys, I know how to do it in Management Studio - but I needed to be able to do it from wi...
Say I have a Student table, it's got an int ID. I have a fixed set of 10 multiple choice questions with 5 possible answers. I have a normalized answer table that has the question id, the Student.answer (1-5) and the Student.ID I'm trying to write a single query that will return all scores over a certain pecentage. To this end I wrote...
We are trying to update our classic asp search engine to protect it from SQL injection. We have a VB 6 function which builds a query dynamically by concatenating a query together based on the various search parameters. We have converted this to a stored procedure using dynamic sql for all parameters except for the keywords. The proble...
Why would someone use WHERE 1=1 AND <conditions> in a SQL clause (Either SQL obtained through concatenated strings, either view definition) I've seen somewhere that this would be used to protect against SQL Injection, but it seems very weird. If there is injection WHERE 1 = 1 AND injected OR 1=1 would have the same result as injected O...
We've just been given the following code as a solution for a complicated search query in a new application provided by offshore developers. I'm skeptical of the use of dynamic SQL because I could close the SQL statement using '; and then excute a nasty that will be performed on the database! Any ideas on how to fix the injection attack?...
I'm using Java - Ibatis and mySQL with Flex/Flash on the front-end. I have one requirement that is to be able to dynamically add creterias and table to a query depending on the user role. here is an example Same object calling same SQL but different result based on role Role 1 : Fully Access to employees SELECT * FROM Employee A ...
The DBA here at work is trying to turn my straightforward stored procs into a dynamic sql monstrosity. Admittedly, my stored procedure might not be as fast as they'd like, but I can't help but believe there's an adequate way to do what is basically a conditional join. Here's an example of my stored proc: SELECT * FROM table WHERE ( ...
Is it possible via script/tool to generate a delete statement based on the tables fk relations. i.e. I have the table: DelMe(ID) and there are 30 tables with fk references to its ID that I need to delete first, is there some tool/script that I can run that will generate the 30 delete statements based on the FK relations for me ? (btw I...
I'm trying to write a stored procedure and depending on a certain column value, I want to be able to change what table I select from. I'll try to give an example: SELECT ItemNumber, ItemType, Description FROM CASE ItemType WHEN 'A' THEN TableA ELSE TableB END WHERE CASE ItemType WHEN 'A' THEN ItemNumber = @itemNumber...
[Update: Using SQL Server 2005] Hi, what I want to do is query my stored procedure with a comma-delimited list of values (ids) to retrieve rows of data. The problem I am receiving is a conversion error: Conversion failed when converting the varchar value ' + @PassedInIDs + ' to data type int. The statement in my where-clause and e...
I would like to run a search with MSSQL Full text engine where given the following user input: "Hollywood square" I want the results to have both Hollywood and square[s] in them. I can create a method on the web server (C#, ASP.NET) to dynamically produce a sql statement like this: SELECT TITLE FROM MOVIES WHERE CONTAINS(TITLE,'"holly...
I am trying to do a many different queries on a result set which has a very large creation time. To get performance gains I wish to use a temp table and just do many queries on this temp table. Seems pretty standard. Yet I am struggling to share this temp table in dynamic sql. As I understand it, each SqlCommand object executes in its o...
We need to generate dynamic SQL in our Java app. Does anyone know a simple library to do this? In our Java app we have a bunch of where clause filter criteria (database column, operand, value). In other words, we could have a Date instance that we need to use to filter a given datetime Oracle column, a String instance that we need to...
This is a sample query for a dataset in my Microsoft Reporting Services Report (.rdl) SELECT ORDNO FROM INTERFACET.DOPPRMAH This is connecting to the iSeries via the OLE DB driver from IBM. How do I "parameterize" the 'INTERFACET' part. ...
Table Name : RM_master Fields : cust_no acct_no acct_code Question is, I want to make the table RM_master as a variable in the parameters in the stored procedure? This has no syntax error but when I execute this in the query analyzer by right clicking on the stored procedure name the variable table name (RM_master) ...
The code is as follows: ALTER PROCEDURE dbo.pdpd_DynamicCall @SQLString varchar(4096) = null AS Begin create TABLE #T1 ( column_1 varchar(10) , column_2 varchar(100) ) insert into #T1 execute ('execute ' + @SQLString ) select * from #T1 End The problem is that I want to call different procedures that can give back differe...
Is it possible to find out runtime using t-sql if a type (e.g. nvarchar or int) is fixed-length or not by querying some system-table? The reason I need to do this is that I need to generate sql-code runtime and need to generate some declarations (DECLARE @foo SOMETYPE(LENGTH) or DECLARE @foo SOMETYPE) depending on the type of some colum...
Consider the following TSQL: SET @WhereClause1 = 'where a.Date > ' + @InvoiceDate I get a date/string conversion error. @InvoiceDate is a datetime variable. What is the right syntax? ...