Hello, I remember back in the day I would make a whole wack of nvarchar(4000) vars, check the length of them as they grew, switch them out as they filled up and then concatenate the whole mess together for the exec call. I was wondering if there was an easier way of doing it. Thanks! Edit: Code Sample, shows me screwing up the case s...
I have two tables A and B, with dynamic columns where I have no idea what columns are key inside them, except from another table called C. The C table specifies which column/s is a key column in tables A and B. There can be 1 or more key columns. My question is, how would I generate such a query where I select all rows from A where the...
I'm playing with some code from an article written by Peter Brawley found here on page 6 of the pdf. I'm trying to figure out how to automate it so that the result of the procedure is automatically placed in the select query. Right now what I am doing is calling the procedure, exporting the result into a text file, going to the text fi...
Hello, I generate an sql string select statement that is around 25k characters long. Unfortunately the production server is SQL 2000. Is my only option to break the string up into 4k nvarchars? --ex. DECLARE @s1 NVARCHAR(4000) DECLARE @s2 NVARCHAR(4000) DECLARE @s3 NVARCHAR(4000) DECLARE @s4 NVARCHAR(4000) DECLARE @s5 NVARCHAR(4000) DE...
I have an ASP.NET project and would like to create a page where my system admins can modify database table data (insert, update, and delete rows). First, I have a drop down that is databound based on the tables in the database: DdlTable.DataSource = from x in dc.Mapping.GetTables() orderby x.TableName.Replace("d...
Building on Tony's answer on this question: If I want to do something like this, CREATE PROCEDURE A(tab IN VARCHAR2) IS tab.col_name <column> --static declaration (column name always remains the same) BEGIN EXECUTE IMMEDIATE 'INSERT INTO ' || tab(col_name) || 'VALUES(123)'; END A; How can I use Dynamic SQL in the above case? ...
Hi, I have data in the SQL 2005 table as below. Table Name: FilterData CategoryID ColumnID AnswerCode -------------------------------- 0349 Q15 02 0349 Q15 03 0349 Q16 04 Table Name: TransactionData CategoryID Q15 --------------------------------- 0349 01 0349 02 0349 03 0349...
I need to build a very dynamic Linq query over a varying number of tables. For example, I have the related tables: Table_A - ID - Name - Desc Table_B - ID - Table_A_ID - Name - Desc Table_C - ID - Table_B_ID - Name - Desc I have a dictionary with information about the table dependencies containing: tableName, parentTable...
I have a requirement in which in order to make an application extensible and reusable, I have to create a provision through which a user would be able to provide a business object structure (the fields, their types, etc.) through an XML file and using that structure the UI (i.e. the controls and the complete page), the data updation meth...
In my stored procedure I have to pass a table and column name which may change everytime. So I build command and execute it. I want the output in another variable @curr_id. This store procedure will be called by second stored procedure by using @curr_id as input. My problem is populating the @curr_id vriable. It is returned as zero. If ...
What is the best way to write a dynamic parametrized query for sql server 2005 where passed parameter value may be null? ...
Hi! I need to check, if procedure parameters are null, and if not, to use it in WHERE clause. For example: sqlquery := 'SELECT * FROM table WHERE a_col = a AND'; IF b IS NOT NULL THEN sqlquery := sqlquery || ' b_col = :b'; END IF; IF c IS NOT NULL THEN sqlquery := sqlquery || ' c_col = :c'; END IF; And so on. Then I need to use O...
Hello, I have this query: SELECT userlist.USERID, (case when (sum( CASE WHEN track.OFFER_ID = 221 THEN 1 ELSE 0 END) > 1) then 1 else 0 end) offer_211 FROM userlist INNER JOIN track ON userlist.USERID = track.USERID group by userid This is the output: +------------+---------- | USERID | offer_211 | +-...
I have a dynamic PL/SQL that will construct the SELECT statement based on what the searching criteria input from the users,likes: l_sql := 'SELECT * INTO FROM TABLEA WHERE 1=1 '; IF in_param1 IS NOT NULL THEN l_sql := l_sql || 'AND column1 = in_param1 '; END IF; IF in_param2 IS NOT NULL THEN l...
Hi Need help with this SQL Server 2000 procedure. The problem is made difficult because I'm testing procedure via Oracle SQL Developer. I'm running the procedure to iterate column with new sequence of numbers in Varchar format for those who have null values. But I keep getting error, so a) I may have done a wrong approach b) syntax i...
Hey everyone, I have a database which contains my product info, which you can see connected to a catalog page here: http://www.marioplanet.com/catalog.asp I would like now, to have product pages created, with the URLs being generated by a certain name in my database. For example, if you point your browser to the link above, and look a...
Let's say I have two objects: public class Person{ string Name { get; set;} Address Home { get; set;} } public class Address{ string Street{ get; set;} string City { get; set;} } string sql = "Select Name, Home_street, Home_city from user"; var results = dc.ExecuteQuery<Person>(sql); The problem here is ...
UPDATE : This is what I did - set @dyn_sql = ' select @UserName=UserName from ( select E.ID as EmployeeID, E.UserName as Username ...
I'm building a dynamic query in my ASP.NET MVC project by the following: Dim queryString As String = "SELECT VALUE userInfos FROM MyDBEntities.UserInformations AS userInfos" If strWhere <> "" Then queryString = queryString & " WHERE " & strWhere End If ' Call the constructor with the specified query and the ObjectContext. Dim Sear...
My co-worker is being unsafe with his code and is allowing a user to upload an SQL file to be run on the server. He strips out any key words in the file such as "EXEC", "DROP", "UPDATE", "INSERT", "TRUNC" I want to show him the error of his ways by exploiting his EXEC ( @sql ) My first attempt will be with 'EXEXECEC (N''SELECT ''You D...