I have following table (SQL Server) Table name is LandParcels
Blockid ParcelNo Stateorprivate
========================
11001901 30 Deemana
11001901 35 Deemana
11001901 41 State
11001901 45 State
11001901 110 Private
11001901 111 Private
11001902 1 Deemana
11001902 11 Sta...
I am creating a stored procedure in Sql Server 2008 database. I want to return the number of rows affected. Which is a better option SET NOCOUNT OFF or RETURN @@ROWCOUNT?
ALTER PROCEDURE [dbo].[MembersActivateAccount]
@MemberId uniqueidentifier
AS
BEGIN
-- Should I use this?
SET NOCOUNT OFF;
UPDATE [dbo].Members SET acc...
I have two tables:
Customers(Id, Name, TownId) T
Towns(Id, Name)
I have an SQL statement like this:
SELECT *
FROM Customers
INNER JOIN Towns ON Towns.Id = Customers.TownId
WHERE Customers.Id > 5
What would happen first?
Would it filter the Customers table and then join the selected records with Towns table?
Would it join all Custom...
Does T-SQL accomodate for array values as parameters for stored procedures? If so how can this be achieved.
...
Both queries below translates to the same number
SELECT CONVERT(bigint,CONVERT(datetime,'2009-06-15 15:00:00'))
SELECT CAST(CONVERT(datetime,'2009-06-15 23:01:00') as bigint)
Result
39978
39978
The generated number will be different only if the days are different. There is any way to convert the DateTime to a more precise number, a...
Hello!
In SQL Server I frequently use "FOR XML AUTO", which allows something like "SELECT id FROM car FOR XML AUTO" to return "<car><id>1</id></car><car><id>2</id></car>". Is there a way to do this in MySQL?
What I want is to be able to store this XML in another table so I can have complete log of changes made in the database. The hist...
I have a table like this
Event ID | Contract ID | Event date | Amount |
----------------------------------------------
1 | 1 | 2009-01-01 | 100 |
2 | 1 | 2009-01-02 | 20 |
3 | 1 | 2009-01-03 | 50 |
4 | 2 | 2009-01-01 | 80 |
5 | 2 | 2009-...
I have a table in SQL Server that stores statistics for a piece of hardware, rows in the table represent data for a given second. It contains for example, these columns:
timestamp (DateTime)
value (int)
What I want to do is select the data from the table for a given date/time range but return it in such a way that it averages for a gi...
Given 2 tables called "table1" and "table1_hist" that structurally resemble this:
TABLE1
id status date_this_status
1 open 2008-12-12
2 closed 2009-01-01
3 pending 2009-05-05
4 pending 2009-05-06
5 open 2009-06-01
TABLE1_hist
id status date_this_status
2 open 2008-12-24
2 pending 2008-12-26
3 open 2009-04-24
4 open...
I am building a one off query to iterate through a set of joined tables. The select statement is using "SELECT *". Since it's is a table with lots of fields, I don't want to specify each column as a variable. Is there a way to FETCH INTO an array or SET of some sort and just grab the values I want?
...
OK, here's what I'm trying to do. I'm using a CTE query in MSSQL2005. The objective of the query is to recurse through Parent child relationships of product categories and return the number of products under each category (this includes any products contained in children categories)
My current version only returns the product count for ...
I have a query that UNIONs two somewhat similar datasets, but they both have some columns that are not present in the other -- i.e. the columns have NULL values in the resulting UNION. The purpose of the UNION is to get the data in a friendly format for the software-side.
The problem is, I need to ORDER the resulting data using those c...
Hi folks,
I have a simple Indexed View. When I query against it, it's pretty slow. First I show you the schema's and indexes. Then the simple queries. Finally a query plan screnie.
Update: Proof of Solution at the bottom of this post.
Schema
This is what it looks like :-
CREATE view [dbo].[PostsCleanSubjectView] with SCHEMABINDING ...
Hi,
I have a query that counts the price of all items between two dates. Here is the select statement:
SELECT SUM(Price) AS TotalPrice FROM InventoryWHERE (DateAdded BETWEEN @StartDate AND @EndDate)
You can assume all of the tables have been set up properly.
If I do a select between two dates and there are no items within that date r...
Hi,
Suppose I have a recursive table (e.g. employees with managers) and a list of size 0..n of ids. How can I find the lowest common parent for these ids?
For example, if my table looks like this:
Id | ParentId
---|---------
1 | NULL
2 | 1
3 | 1
4 | 2
5 | 2
6 | 3
7 | 3
8 | 7...
Hello,
I have an order header table called "OrderH". In this table there is a column called "OrderDate". I am trying to retrieve the orders with a date within a certain range. I thought that I could accomplish this with the "between" keyword but I am not having any luck. This is this SQL I have been fidgiting with:
select
*
from
...
I have a stored procedure that accepts a date input that is later set to the current date if no value is passed in:
CREATE PROCEDURE MyProc
@MyDate DATETIME = NULL
AS
IF @MyDate IS NULL SET @MyDate = CURRENT_TIMESTAMP
-- Do Something using @MyDate
I'm having problems whereby if @MyDate is passed in as NULL when the stored ...
I've oversimplified this a bit, because I'm looking for a general-purpose answer. Let's say I've got a table setup like this:
Parent
recno int (unique, pk)
date datetime
stuff varchar(50)
Child
parentrecno (int, fk) --- PK
sequence (int) --- PK
data varchar(50)
And in my C# program I've gone through a lot of...
Input:
Name Id
N1 1
N1 3
N1 4
N1 7
N2 2
N2 1
N2 8
N2 5
N3 4
N3 8
N3 5
N3 3
N4 7
N4 7
N4 7
N4 8
Output:
Name1 Name2 Name3 Name4
---------------------...
In trying to solve:
Linq .Contains with large set causes TDS error
I think I've stumbled across a solution, and I'd like to see if it's a kosher way of approaching the problem.
(short summary) I'd like to linq-join against a list of record id's that aren't (wholly or at least easily) generated in SQL. It's a big list and frequently ...