Duplicate of: http://stackoverflow.com/questions/712443/tsql-varchar-string-manipulation-problem/712453#712453 I'm building a dynamic SQL statement out of parameters from a reporting services report. Reporting services passes MutiValue Parameters in a basic CSV format. For example a list of states may be represented as follows: AL,CA...
I want to create dymamic SQL code to automatically create a table-replication. I'm stuck on how to make the following code dynamic, so that it's possible to pass in SQL variables and use them in the code. I help myself at the moment with search and replacing the 'TODO:' parts, which is not very nice... Here is the code: DECLARE @sql VA...
I am presenting to a final authority evaluation scores for employees. Each row is an employee’s data and since the categories to be evaluated can change from period to period the column names cannot be hardcoded in the Stored Procedures. I have already devised the following solution. 1 Create a temp table 2 Dynamically use the Alter...
Hi, I'm hoping someone can provide some advise for an easier way to deal with this problem. I am working on creating a flattened view of a highly normalized set of data. The goal of flattening is to provide a view which normal people can use to develop reports from. The source data contains a couple of tables as shown: CREATE TABLE ...
I am writing a report to return details about an object ('files') in my database. My application lets users create their own flags for use against file objects. Flags basically consist of a name, then flag instances store a bit value to indicate whether it is set for the parent file object. I want to write a query that returns one row p...
I have a piece of dynamic SQL I need to execute, I then need to store the result into a variable. I know I can use sp_executesql but can't find clear examples around about how to do this. ...
I'd need to know how to parse a query to know if it is well build before executing it. If the parsing is correct then execute it, if not discard it. I haven't been able to accomplish this using SET NOEXEC, SET PARSEONLY, TRY/CATCH. I m using dynamic sql inside a loop. Sometimes the dynamic sql is incorrect not for my fault but for the ...
IN SQL Server 2008 I have a situation where I need to return a dynamically generated table, and the columns are also dynamically generated. Everything in this was generated by queries, as I started with one id, then I get the column names and types, which is why the casting. Following is the final query, that will return the table I wa...
I am writing a PL/SQL procedure that needs to to dynamically generate some queries, one of which involves creating a temporary table using results from a query taken as a parameter. CREATE OR REPLACE PROCEDURE sqlout(query IN VARCHAR2) IS BEGIN EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE tmp_tab AS (' || query || ');'; END; It c...
I have a bunch of functions with signatures like: FUNCTION func1 (par1 IN VARCHAR2, par2 IN NUMBER) RETURN my_rec; and I have a function for retrieving data from this bunch of functions: FUNCTION get_result (func_name IN VARCHAR2, par1 IN VARCHAR2, par2 IN NUMBER) RETURN my_rec; IS rec1 my_rec; BEGIN EXECUTE IMMEDIATE 'SE...
I use MS SQL 2008 and I want to create a trigger in a database that is created dynamic. Creating the database is called within a stored procedure of an other database and runs perfectly, but when I want to add a trigger or a stored procedure, the executing fails. If I try to run the dynamiy SQL with an EXEC('USE dbase GO CREATE TRIGGE...
I have an ASP.NET 3.5 application that I want to allow the user to select a table and allow CRUD operations on that table. The user will be restricted to a number of tables to edit however the tables and even database won't be known until after deployment; the web.config will setup the connection and tables. So I need to build a framewo...
Is there a way to select a database from a variable? Declare @bob as varchar(50); Set @bob = 'SweetDB'; GO USE @bob ...
I have a trigger that gets inserts and updates to a view. I need to find the columns that are being modified and get the value into the correct table. INSERT INTO TempTableAttr_Lot(ID, [% Num]) VALUES(3, 24.0) I am trying to figure out how, in my trigger, to get the value of ID and [% Num] columns. The problem is that there can be 32...
I want to run a query like this: SELECT * FROM Studio WHERE Id IN (134, 144, 132, 138, 7432, 7543, 2566) but the amount of Id's passed to the IN clause is only determined at runtime. Do I have to use dynamic SQL or can this be done with a stored procedure? UPDATE: If either option is available, which one is better? Thanks. ...
Given a table name and column name in a pair of variables, can I perform a select query without using dynamic sql? for example, I'd like something nicer than this: CREATE PROCEDURE spTest (@table NVARCHAR(30), @column NVARCHAR(30)) AS DECLARE @sql NVARCHAR(2000) SELECT @sql = N'SELECT ' + @column + N' FROM ' + @table PRINT @sq...
I need to use dynamic SQL execution on oracle where I do not know the exact number of bind variables used in the SQL before runtime. Is there a way to use a variable number of bind-arguments in the call to EXECUTE IMMEDIATE somehow? More specifically, I need to pass one parameter into the unknown SQL but I do not know how often it will...
All, I am very new to stored procedures in general but I am struggling especially with those in Oracle. I have created a very simple example of what I am trying to accomplish and I am still getting the same error with this simplified version. The example stored procedure is as follows: CREATE OR REPLACE PROCEDURE ashish_test AUTHID C...
I currently have a 'Filter' object which corresponds to a business object. This object has properties that relate to the different ways that I want to be able to filter/search a list of such business objects. Currently these Filter objects have a method that builds the contents of a where-clause that is then passed to a SQL Server 2000...
I have the following dynamic query which is working fine without the WHERE clause, which is expecting UNIQUEIDENTIFIER. When I pass it in, I don't get a result. I tried CAST and CONVERT, but no result. I might be doing it wrong, can anybody help? CREATE PROCEDURE [dbo].[sp_Test1] /* 'b0da56dc-fc73-4c0e-85f7-541e3e8f249d' */ ( @p_Creat...