Dynamic Columns - SQL Server - Months as Columns
DB: SQL Server 2005 We have a table that has data in this manner: Project Year Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov ...
tsql
How to write stored procedure to do this?
I would like to create a stored procedure that takes in a string of comma separated values like this "1,2,3,4", and break it apart and use those numbers to run a query on a different table. so in the same stored procedure it would do something like select somefield from sometable where somefield = 1 select somefield from sometable wher...
SqlCommand.Parameters.AddWithValue Not Returning Correct Results
I'll admit that I'm a bit of a newbie (though learning fast!) when it comes to using parameterized queries in C#, so I'm probably just overlooking something here, but I can't seem to figure out how to get a parameterized query to work for me. Here is a much simplified example. If more information is needed, I am certainly willing to sup...
Dateformat mismatch in sql server and C# code
I have a problem. I have an application in C# with SQL SERVER 2005 as backend. The problem is in fetching the correct record based on the date. The frontend code is if (string.IsNullOrEmpty(txtFromDate.Text)) SelectCmd.Parameters[0].Value = DBNull.Value; else SelectCmd.Parameters[0].Value = txtFromDate.Text; Now if I ru...
Should I run VACUUM in transaction or after?
I have a mobile application sync process. The transaction does a lot of modification on the database. Since this is done on mobile I need to issue a VACUUM to compact the database. I am wondering when should I issue a VACUUM in the transaction, as final statement or after the transaction? I am currently looking for SQLite, but if i...
T-SQL is it possible to use a variable in a select statement to specify the database
Is there a way to do something like this without converting the sql to a string and calling exec DECLARE @source_database varvhar(200) SELECT @source_database = 'wibble' SELECT * FROM SELECT @source_database.dbo.mytable ...
Is there an equivalent to typedef in Transact-SQL?
Is there any way of aliasing datatypes in T-SQL (SQL Server 2005), a bit like a C typedef? I'd like to be able to alias, say, VARCHAR(20) as 'my_datatype' so that wherever I wish to use VARCHAR(20) I could instead use my_datatype. Synonyms allow you to do this sort of thing for tables, and there are built-in synonyms for some datatypes ...
tsql - delete last row
I want to delete the last row from the table that satisfies some condition. DELETE TOP 1 FROM SOME_TABLE WHERE SOME_COULMN = @VALUE ORDER BY 1 DESC ...
select for xml, nest on order by fields
SELECT [Represantative] ,[log_date] ,[customer] ,[type] ,[log_type] ,[zone_code] FROM [dbo].[ilc_ziyaret_plani_gun_degisiklikleri_v] gun_logu order by Represantative, log_date, customer for xml path , root('log_details') I need the xml nested as in the order by clause: I tried using for xml...
Counting Rows between dates...
I'm using a CTE to generate a range of dates. 12/02/2010 10:00:00 12/02/2010 10:59:59 12/02/2010 11:00:00 12/02/2010 11:59:59 12/02/2010 12:00:00 12/02/2010 12:59:59 I then left join this to a indexed view containing huge amounts of date. I have 2 options for counting between the date ranges 1) i would SUM(case) test the log_date t...
Sql Server query with date filter
I have a table like this; ID int, OrderedDate DateTime I want to select only records of followed month. For example result set: ID OrderedDate 110 January 110 February 200 January 200 February How can I write this query? ...
Ranking before grouping problem in SQL Server 2005
HI, This should be easy but I don't understand enough about how grouping works. Basically I have 2 tables "Categories" and "Items" Categories ID CategoryName Items ID CategoryID ItemName Photo Score All I want to do is get 1 row for each category which contains the Category ID, the Category Name and the photo that belongs to the h...
SQL Server 2005: dynamically adding parameters to a stored procedure
Scenario I have a stored procedure that takes a single parameter. I want to update this stored procedure to take a VARIABLE NUMBER OF PARAMETERS - a number that I will never know. I currently use SQLConnections through a C# interface in order to pass in a single parameter to the stored procedure and return a result. The SQL Part Lets ...
TSQL - latest entry
I have a contract table with id and index_id where the max index_id per id represents the latest entry. How could i get the latest contract in tsql? ...
Automated generation of database change scripts
I'm looking for a way to automated the generation of change scripts for our databases. Currently we use Visual Studio 2008 database edition, which has the Schema Compare option, but I don't see a way to automated those actions. What I'd like to do is create an old and a new database during my daily builds and have schema compare gener...
SQL Pivot and formatting Grid
I used pivot to get data in format Store department Employee Q1 Q2 Q3 ---------------------------------------------------------- abc d1 u1 1 Null Null abc d1 u1 Null 3 Null abc d1 u1 Null Null 2 abc ...
Newbie T-SQL dynamic stored procedure -- how can I improve it?
I'm new to T-SQL; all my experience is in a completely different database environment (Openedge). I've learned enough to write the procedure below -- but also enough to know that I don't know enough! This routine will have to go into a live environment soon, and it works, but I'm quite certain there are a number of c**k-ups and gotch...
Geo Fencing Code - pure SQL server 2008 implemenation
Hi all. I need help with this. I am looking for a complete TSQL code. (no c++/C#) define 32 points around a central point forming a quasi circle with a given radius in miles. Check the 32 above points against a given point to see if it is included inside the geo-fence. Thanks JJ ...
Re-usable SQL Server stored procedures; nesting; global variables
I want to make some re-useable, somewhat-dynamic TSQL code that can be called within many other stored procs, but I'm struggling with how to implement this with SQL Server. The environment is that many distributed source systems databases which will have their own wrapper stored procedure which will call a few of these modular stored pr...
When to use what functions vs Sprocs
Hi t-sql I tried to read the difference between UDF and sprocs, here http://realfantasy.wordpress.com/2009/01/05/sql-server-user-defined-function-udf-vs-stored-procedure-sp/ Do u know more differences? When to use what? Thanks ...