I have 3 ways I want to filter: by name by list and show all I'm using ASP.NET 3.5 and SQL Server 2008. Using ADO.NET and stored procs. I'm passing my list as a table valued parameter (but I'm testing with a table variable) and the name as a nvarchar. I have "show all" as ISNULL(@var, column) = column. Obviously the way I'm query...
Hey, I'd like to do something like this with PL/SQL: for ACCOUNT in account_cursor loop for related_data in (select something from table where some_column = ACCOUNT.column) loop endloop; endloop; (caps for emphasis) I'm away from my dev environment so I can't test this out, so please ignore any minor syntactical...
I would like to call an "update" stored procedure which won't necessarily include all columns. There is probably a better way to handle this.... As you can see, if I do not pass in the column parameters their value is NULL. Then, using the ISNULL, I set the columns either to their new values or their existing values. CREATE PROCEDURE [d...
I have a database where all access is controlled by stored procedures. The DBA would like to avoid giving users direct read/write access to the underlying tables, which I can understand. Hence all updating and selecting of data is done via stored procedures. Basically he has created one role that has EXECUTE permissions to all the stored...
Hi I need to put a trigger on Delete on Table1. On delete of record from Table1, I need to update Table2 in trigger, but i need the value of deleted record in the trigger. Example:- IF OBJECT_ID ('UpdateLog','TR') IS NOT NULL DROP TRIGGER UpdateLog; GO CREATE TRIGGER UpdateLog ON Table_1 AFTER DELETE AS UPDATE Table_2 SET D...
I'm a SQL Server 2005 newb and I have to do something that should be easy but I'm having difficulties. For the time being my stored procedure is storing csv in one column in my db. I need to break apart the csv into multiple columns. The number of values in a the CSV parameter is static, there is always gonna be 8 values and they are al...
hi there imagine I have a table like this Id | Name | City 1 | Tom york | xx 2 | Jim york | xy 3 | tony | new York 4 | Sam york | xz and I would like to search records with name like '%york%' or city like '%york%' BUT i want to give more priority to name, so my rresult would be something like: Id | Name |...
I want to do this query: SELECT * FROM mail WHERE tag_draft = 0 AND (target_id = 2 OR source_id = 2) GROUP BY created DESC` ...but it returns no rows. Now, if I do this query: SELECT * FROM mail WHERE tag_draft = 0 AND target_id = 2 GROUP BY created DESC ...then it works fine - every row with a ...
Hi all, I'm trying to query a table that has a varchar(100) "VALUE" column. This column can hold anything from a letter, a number or, in this case, a date. The date will always be entered in the table as 'YYYY-mm-dd'. However, when I run the following query: select * from myTable where VALUE = '2009-12-11' (Date, Format 'yyyy-mm-dd')...
I try to add a namespace on xml using WITH XMLNAMESPACES. When I execute my queries, the namespace is added with the root element but with the second element I have xmlns="" as well... and I would like to remove that... I provided an example: Queries for creating the table and the data: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON G...
I'm using MySQL, but I think this is a basic SQL question. I don't know how else to ask but to give an example. Say I have this data in my table: id date_time foreign_key key value 1 2010-01-01 00:00:00 1 'temperature' 84 2 2010-01-01 00:00:01 1 'humidity' 34 3 ...
Ok at this point, I am pretty confused on how to construct my pagination system in an efficient manner. The problem is that the system is not typical and does not go in regular intervals such as 10 per page. The problem is, messages can have replies and thus share the same reply_chunk_id (reply_id). I do not want messages to get cut off ...
I have the following query: SELECT M.movieId, COUNT (*) AS mcount FROM Movies M, Rentals R WHERE M.movieId = R.movieId GROUP BY M.movieId I have a Movies DB and a Rentals DB, the resulting table currently shows the movie ID and how many times it has been checked out but i just can't figure out how to incorporate a MAX call on the m...
I'm using VBA excel 2003,SQL 2005 to make a sql query call and inside my sql statement I'm using '+' operator to concatenate two strings. dim query as string query = "Select distinct ', '+emailaddress1 " query = query & "from contact " would this work inside vba? My query returns too many records in excel but not in SQL? Please just...
Hello, I have a need to generate SQL queries against a variety of database providers, for database schemas that are not known at compile time. I see that Entity Framework has already done the hard work of providing a SQL dialect called Entity SQL that is translated to native SQL before execution, and I was hoping to take advantage of th...
Following linq statement generates multiple trips to the database, is there a way to change this so it does it in one trip? db.Members.Distinct().Select( m => new { Id = m.Id, PlayTimeSchedules = m.PlayTimeSchedules.Select(pts => pts.DayOfWeek) } ).ToList(); FYI: in this example Distinct is redundant. There is a...
How can I find out the default value of a column in a table using a SQL query? Using the sp: sp_columns @tablename I get some info on the columns of a particular table but the default value of the columns is missing, How can I get it? ...
I have a form that creates a new brand. The user chooses a name, image, and website url for the brand and hits submit. The edit scenario works the same way. The issue I'm having is when creating a new brand, the correct imageId is being posted, and right up to SaveChanges() in my action its correct. When I check the new row in the databa...
I have a syntax error in this subquery that I cannot seem to figure out why it won't work. All the parens are matched select min(max_s) from (select max(salary) from instructor group by dept_name) as s(max_s); Error: near "(": syntax error ...
We had a Stored Procedure written and it had a GRANT written at the last line of the SP.. Our DBA recommended that there should be a GO before the GRANT statement, else it will be executed every time. What does this mean? I am not sure how GO will prevent GRANT executing "every time". ...