I am writing a a report and was wondering if there is there any way to find items that join into identical sets? For example in the following query, I want to find all areas that join against the same set of products: SELECT Area.Name, AggregateSetOfProductsId FROM Area INNER JOIN AreaToProduct ON AreaToProduct.AreaId = Area.Id GROUP ...
Hi guys, I need to implement a multi-parented tree (or digraph) onto SQL Server 2005. I've read several articles, but most of them uses single-parented trees with a unique root like the following one. -My PC -Drive C -Documents and Settings -Program Files -Adobe -Microsoft -Folder X -Drive D ...
Hi, When i execute database script, i got errors in stored procedure then it couldn't create that stored procedure that have errors. I want to forcibly create stored procedure even if some error occur in stored procedure. Thanks in advance ...
Lets say I have two subqueries: SELECT Id AS Id0 FROM Table0 => Id0 --- 1 2 3 and SELECT Id AS Id1 FROM Table1 => Id1 --- 4 5 6 How do I combine these to get the query result: Id0 Id1 ------- 1 4 1 5 1 6 2 4 2 5 2 6 3 4 3 5 3 6 ...
I have an SQL Server 2000 db, and I would like to retrieve summary information for all the numerical fields contained in the user tables of the database. I can retrieve the names, datatypes and sizes with the following query: SELECT t.name AS [TABLE Name], c.name AS [COLUMN Name], p.name AS [DATA Type], p.length AS [SIZE] FROM dbo.sys...
I have a SQL query: SELECT e.name as estate_name , g.name as governing_body , count(s.id) as total_stands , count(sp.id) as service_providers FROM estates e LEFT JOIN governing_bodies ON e.governing_body_id = g.id LEFT JOIN stands s ON s.estate_id = e.id LEFT JOIN services sp ...
I am using the DatabasePublishingWizard to generate a large create script, containing both data and schema. The file it produces is ginormous so opening the script to fix any syntax errors is next to impossible and machines with less than 4gb have a hard time getting it to run! What should I do and how should I do it? Thanks everyone for...
Hi, Background: I have a payroll system where leave is paid only if it falls in range of the invoice being paid. So if the invoice covers the last 2 weeks then only leave in the last 2 weeks is to paid. I want to write a sql query to select the leave. Assume a table called DailyLeaveLedger which has among others a LeaveDate and Paid f...
I want to Select tblProperty.ID only when this query returns greater than 0 SELECT COUNT(tblProperty.ID) AS count FROM tblTenant AS tblTenant INNER JOIN tblRentalUnit ON tblTenant.UnitID = tblRentalUnit.ID INNER JOIN tblProperty ON tblTenant.PropertyID = tblProperty.ID AND tblRent...
IN SQL Server 2008 I have a situation where I need to return a dynamically generated table, and the columns are also dynamically generated. Everything in this was generated by queries, as I started with one id, then I get the column names and types, which is why the casting. Following is the final query, that will return the table I wa...
I have a table in Sql Server 2008 Express which contains 18 million records. The structure looks something like this (simplified): Id, GroupId, Value, Created Id is the primary key with a clustered index GroupId is a non-clustered index In this case, every 10 rows get a new groupId meaning that records 1-10 have GroupId 1, records 11-...
Hello, I've found a solution for finding the position of an underscore with PATINDEX : DECLARE @a VARCHAR(10) SET @a = '37_21' PRINT PATINDEX('%_%', @a) -- return 1 (false) PRINT PATINDEX('%!%', REPLACE(@a, '_', '!')) -- return 3 (correct) Have you other ideas ? Like a way to escape the underscore character ...
What is the different between Set Rowcount X And Select Top X * From Z In TSQL? ...
In the end of my function, I have the statement: RETURN @Result What I want to do is something like this: IF (@Result = '') BEGIN @Result = 'Unknown' END RETURN @Result The above does not work though. ...
I'm not talking about doing a "SET NOCOUNT OFF". But I have a stored procedure which I use to insert some data into some tables. This procedure creates a xml response string, well let me give you an example: CREATE PROCEDURE [dbo].[insertSomeData] (@myParam int) AS DECLARE @reply varchar(2048) ... Do a bunch of inserts/updates... SE...
Can someone please identify the functional/performance differences, if any, between SET and SELECT in T-SQL? Under what conditions should I choose one over the other? UPDATE: Thanks to all who responded. As a few people pointed out, this article by Narayana Vyas Kondreddi has lots of good info. I also perused the net after reading the...
Hi, In SQL server How can I transform 1 row with varchar columns into a column? I think I need to use Pivot but I can't find an example without agregators this is the situation I have: create table #tmp ( ac varchar(100), bc varchar(100), cc varchar(100)) insert into #tmp (ac,bc,cc) Values ('test1','test2','test3') insert into #tmp Va...
In my stored procedure I have multiple similar variables @V1, @V2 ... @V20 (let's say 20 of them) FETCHED from a record. How would I use dynamic SQL to make 20 calls to another stored procedure using those variables as parameters? Of course @V[i] syntax is incorrect, but it expresses the intent fetch next from maincursor into @stat...
I am using DATEDIFF in an SQL statement. I am selecting it, and I need to use it in WHERE clause as well. This statement does not work... SELECT DATEDIFF(ss, BegTime, EndTime) AS InitialSave FROM MyTable WHERE InitialSave <= 10 It gives the message: Invalid column name "InitialSave" But this statement works fine... SELECT DATEDIF...
I'm having problems figuring out a query for this scenario. I have two tables I want to use in this query, they are like this: Units ID Other Data People ID UnitID <-- fk to Units Other Data This is what I want to do: I want to select all the units that do NOT have a row in the People table linked to them. How can I do this...