Hello guys. This should be fairly simple, though I can't seem to find a single example. I want to create a query looking like this: SELECT column_name FROM table_name WHERE column_name IN (value1,value2,...) As an option I could append OR-clauses to the end of the query. The code I have written so far keeps blowing up with a Nullpoi...
So, I've been searching around and I've found things similar to my problem, but I need more help to get a real solution. I'm trying to construct a query that will return 2 columns of data, the first column should be a list of the column names themselves and the second should be the value of that column. Visually it would look like this...
I am writing a stored procedure for which I need to populate a table based on the data being reported on. In this situation, I will be pulling in three values per day for a certain code in a date range. Say on a certain run of this stored procedure, I have code values X, Y, and Z for a date range as so: select abc.code, abc.da...
I have a PL/SQL statement that uses EXECUTE IMMEDIATE to execute a query. However, I'm having difficulty figuring out how to even get the text of the query that's being executed. I can't use dbms_output as the query is greater than 255 characters. Is there any way to make sqlplus echo the string that's passed in to EXECUTE IMMEDIATE? ...
I have a query where not all conditions are necessary. Here's an example of what it looks like when all conditions are used: select num from (select distinct q.num from cqqv q where q.bcode = '1234567' --this is variable and q.lb = 'AXCT' --this is variable and q.type = 'privt' --this is variable ...
I have 3 tables (tblPreference, tblCustomer, tblCustomerPreference) that look something like the following: tblPreference: ID | Name | DefaultValue (int PK) | (nvarchar(100)) | (nvarchar(100)) ------------------------------- 1 | Preference1 | 1 2 | Preference2 | Yes 3 | Preference3 | 1 ...
I have the following dynamic sql statement where I want to add @StartRowIndex + @MaximumRows and subtract 1 from it. I am unclear on where to put the single quotes in the statement. Here it is: SET @sql = @sql + ' SELECT * FROM LicenseInfo WHERE RowNum BETWEEN ' + @StartRowIndex + ' AND ' + '(' + @StartRowIndex + @MaximumRows + ')'...
I've been refactoring a huge dynamic SQL statement, and think I may have hit a roadblock. The goal has been to parameterize it. I'm using SQL Server 2005. I have an int variable (@i) that determines what column I need to update. Here's what I've been doing: if @i = 1 begin set @updateClause = 'Column1 = @newValue'; set @updateClau...
I'm trying to update a table on several remote servers by iterating over a list of server names and executing some dynamic SQL. See below. DECLARE @Sql NVARCHAR(4000) DECLARE @Server_Name VARCHAR(25) SET @Server_Name='SomeServer' SET @Sql='UPDATE ' + @Server_Name + '.dba_sandbox.dbo.SomeTable SET SomeCol=''data''' PRINT @Sql EXEC @Sql ...
I need to execute three dynamic SQL statements synchronously on a linked server (SQL Server 2005) like this: declare @statement nvarchar(max); set @statement = 'exec ' + @server_name + '.' + @database_name + '.dbo.Foo;exec ' + @server_name + '.' + @database_name + '.dbo.Bar;exec ' + @server_name + '.' + @database_name + '.dbo.BigTime';...
Can I run a dynamic sql in a transaction and roll back using EXEC: exec('SELECT * FROM TableA; SELECT * FROM TableB;'); Put this in a Transaction and use the @@error after the exec statement to do rollbacks. eg. Code BEGIN TRANSACTION exec('SELECT * FROM TableA; SELECT * FROM TableB;'); IF @@ERROR != 0 BEGIN ROLL...
hi guys, I have following query. declare @Prm_CourseId int declare @Prm_SpecializationId int set @Prm_CourseId=5 set @Prm_SpecializationId=0 declare @WhrStr varchar(500) set @WhrStr = case @Prm_CourseId when 0 then 'e.CourseId is null or e.CourseId is not null' when -1 then 'e.CourseId is null o...
a) What do we consider as a dynamic sql statement? Any sql statement that dynamically adds a clause(s) or even just a part of a clause to a SQL string? b)Aren’t then parameterized strings that use placeholders for dynamically supplied values also considered dynamic sql statements? thanx ...
I am soon going to be writing a component that takes metadata and generates dynamic SQL from it. Mostly we're talking SELECT, INSERT, UPDATE, DELETE stuff but I suppose it's possible to have some CREATE/ALTER TABLE statements in there too. I'm assured that no existing ORM solution fits the bill but otherwise the details on what where a...
What is a dynamic SQL query, and when would I want to use one? I'm using SQL Server 2005. ...
I'm using Sql Server 2008 and have a proc that uses sp_executesql inside. I keep getting the following error when executing the proc through ASP.NET: The SELECT permission was denied on the object 'MyTable', database 'MyDatabase', schema 'dbo'. I've done lots of research on this and most people point to the fact that I need to grant se...
When I dynamically create a datastore using SyntaxFromSQL (in order to generate datastore source code, based on a SQL SELECT statement), with syntax like this string ERRORS, sql_syntax, dwsyntax_str, presentation_str dwsyntax_str = trans_object.SyntaxFromSQL ( sql_syntax, presentation_str, ERRORS) ds_1.Create( dwsyntax_str, ERRORS) ho...
I am trying to query a database (SQLServer) with multiple tables of identical structure and with related names i.e. [TABLE 01 $TRANSACTIONS] [TABLE 02 $TRANSACTIONS] ... [TABLE (n) $TRANSACTIONS] I have a query returning the desired records on one table at a time and can manually select multiple tables with SELECT {QUERY01} FROM [TAB...
Hella all, What I want to do is something like that, I will have an SQL table depending on my parameter, DECLARE @find varchar(30) SET @find = 'no' SELECT * FROM ( if @find = 'yes' ( SELECT * FROM myTable WHERE ID= '5882' ) ELSE ( SELECT * FROM myTable WHERE OLD_ID= '5882' ) ) X This is ...
I am taking a database class and at the beginning of the lab section of the class we usually have to drop all the tables in the database created previously. I wanted to be able to run a script that does this dynamically, but cannot seem to get it to work. Here is the code I have so far. declare tname string(50); cursor ctable is select ...