I need a TSQL query which returns all seconds since a given begin date. I am going to use this to left outer join against another table to see how many bytes were downloaded each second. I am using Sql Server 2008. ...
What model of these is better? A or B? Considering I have more common columns (5 actually for now), many more entities (over 60) and many more relations, while most of tables are just simple dictionaries (enumerated sets of names) and have no own columns. ...
I'm interested in returning an empty result set from SQL Server stored procedures in certain events. The intended behaviour is that a L2SQL DataContext.SPName().SingleOrDefault() will result in CLR null value. I'm presently using the following solution, but I'm unsure whether it would be considered bad practice, a performance hazard (I...
hi! I've a table(TableA) with contents like this: Col1 ----- A B B B C C D i want to remove just the duplicate values without using temporary table in Microsoft SQL Server. can anyone help me? the final table should look like this: Col1 ----- A B C D thanks :) ...
I have next block in the end of each my stored procedure for SQL Server 2008 BEGIN TRY BEGIN TRAN -- my code COMMIT END TRY BEGIN CATCH IF (@@trancount > 0) BEGIN ROLLBACK DECLARE @message NVARCHAR(MAX) DECLARE @state INT SELECT @message = ERROR_MESSAGE(), @state = ERROR_STATE() ...
select NV.PHG From Nhanvien NV Group by NV.phg Having count(nv.Manv) >= all (select count(NV.MANV from nhanvien nv group by nv.MANV)) I'm finding a better way to find the 'max count' NV of a PHG ( in this example ) . I think, we meet this case all the time when we do SQL, i should've a better way . Thanks for reading this :) ...
I'd like to get the total count of results and top n rows of some query - is it possible in one statement? I'd expect the results as: count(..) column1 column2 125 some_value some_value 125 some_value some_value Thank you in advance! ...
Hi. I'm a sql newbie, I use mssql2005 I like to do join Action depnding on input parameter. CREATE PROCEDURE SelectPeriodicLargeCategoryData @CATEGORY_LEVEL CHAR(1), @CATEGORY_CODE VARCHAR(9) AS ... JOIN CATEGORY_AD_SYS CAS WITH(NOLOCK) ON CA.CATEGORY_ID = [[[[[ HERE ]]]] above the sql. if @CATEGORY_LEVEL = 'L' the...
A query (SELECT/FUNCTION/VIEW/PROCEDURE) returns a column of varchar records. I need to concatenate them all into one single varchar line. How do I best do it in T-SQL? ...
Hi. I'm a sql newbie, I use mssql2005 I like to do select with the condition which is depending on the input parameter I've tried this. WHERE CF.PROCESS_DATE = '2010-05-05' AND CASE @CATEGORY_LEVEL WHEN 'L' THEN CAS.MCATEGORY_ID = '' AND CAS.SCATEGORY_ID = '' WHEN 'M' THEN CAS.SCATEGORY_ID = '' END but didn't work and ha...
I've got a task which can be only accomplished by construction a QUERY at runtime and executing it with sp_executesql. The result has to be a boolean (0/1 integer) value which I need to return as a result of the function. The only way I found to capture an SP's output is "INSERT INTO [table] EXECUTE [sp]" query, but functions forbid thi...
Hi, I'm newbie in t-sql, and I wonder why this query executes so long ? Is there any way to optimize this ?? update aggregateflags set value=@value where objecttype=@objecttype and objectcode=@objectcode and storagetype=@storagetype and value != 2 and type=@type IF @@ROWCOUNT=0 Select * from aggregateflags where objecttype=@objectty...
Hi. I have a question regarding locking in TSQL. Suppose I have a the following table: A(int id, varchar name) where id is the primary key, but is NOT an identity column. I want to use the following pseudocode to insert a value into this table: lock (A) uniqueID = GenerateUniqueID() insert into A values (uniqueID, somename) u...
SELECT DISTINCT IncreasmentAmount, Name, regionid FROM Pricing.GroupOfRegions WHERE regionid in (6,7) This statement produces this result: 12.80 AB 6 13.00 ABC 6 15.00 AC 6 12.80 AB 7 13.00 ABC 7 I'd like to add more conditions where IncreasmentAmounts are equal. This would result in the rows that...
I need a way to take a resultset of KeyIDs and divide it up as equally as possible and update records differently for each division based on the KeyIDs. In other words, there is SELECT KeyID FROM TableA WHERE (some criteria exists) I want to update TableA 3 different ways by 3 equal portions of KeyIDs. UPDATE TableA SET FieldA...
What is the most effective and flexible way to generate combinations in TSQL? With 'Flexible', I mean you should be able to add easily combination rules. e.g.: to generate combinatories of 'n' elements, sorting, remove duplicates, get combinatories where each prize belongs to a different lottery, etc. For example, Having a set of number...
I have a client supplied file that is loaded in to our SQL Server database. This file contains text based date values i.e. (05102010) and I need to read them from a db column and convert them to a normal date time value = '2010-05-10 00:00:00.000' as part of a clean-up process. Any guidance would be greatly appreciated. ...
Please, the first TSQL works FINE, the second does not. I guess it must be a simple mistake, since I am not used to T-SQL. Thank you for the answers. R Conte. *** WORKS FINE *********************************** (parm hard-coded) ALTER PROCEDURE rconte.spPesquisasPorStatus AS SET NOCOUNT ON SELECT pesId, RTRIM(pesNome), pesStatus, ...
Hi All: I have following TSQL, 3 IDs (@EmpID, @DeptID and @CityID) are passed in and it could all have NULL or any one could be NULL. See following scenario: -- Very Narrow (all IDs are passed in) IF(@EmpID IS NOT NULL AND @DeptID IS NOT NULL AND @CityID IS NOT NULL) BEGIN SELECT e.EmpName ,d.DeptName ...
We have a statement., that is inserting some rows in a temporary table (say e.g., 10 rows), while inserting 5th row, it has some issue with one of the column format and giving an error and then it stopped inserting the rows. What I want is, it should skip the error rows and insert valid rows. For those error rows, it can skip that error...