Sql - goto statement
Hi folks, is it good practise to use 'goto' statements in Sql queries? ...
tsql
Need some serious help with self join issue.
Well as you may know, you cannot index a view with a self join. Well actually even two joins of the same table, even if it's not technically a self join. A couple of guys from microsoft came up with a work around. But it's so complicated I don't understand it!!! The solution to the problem is here: http://jmkehayias.blogspot.com/2008/12...
help with delete where not in query
I have a lookup table (##lookup). I know it's bad design because I'm duplicating data, but it speeds up my queries tremendously. I have a query that populates this table insert into ##lookup select distinct col1,col2,... from table1...join...etc... I would like to simulate this behavior: delete from ##lookup insert into ##lookup sele...
How to enable automatic transaction scoping on SQL Server Management Studio?
I'm used to work with tools like Toad or PL/Sql Developer for Oracle and every SQL statement I run on the query window runs automatically under a trasaction scope. So, if my SQL statement is not correct, or even if I miss something on the SQL script I can rollback only by clicking on a button at the UI. If everything worked as expected, ...
Need a set based solution to group rows
I need to group a set of rows based on the Category column, and also limit the combined rows based on the SUM(Number) column to be less than or equal to the @Limit value. For each distinct Category column I need to identify "buckets" that are <=@limit. If the SUM(Number) of all the rows for a Category column are <=@Limit then there w...
Getting filtered results with subquery
I have a table with something like the following: ID Name Color ------------ 1 Bob Blue 2 John Yellow 1 Bob Green 3 Sara Red 3 Sara Green What I would like to do is return a filtered list of results whereby the following data is returned: ID Name Color ------------ 1 Bob Blue 2 John Yellow 3 Sara Red i.e. I would ...
How to Calculate longest streak in SQL?
I have TABLE EMPLOYEE - ID,DATE,IsPresent I want to calculate longest streak for a employee presence.The Present bit will be false for days he didnt come..So I want to calculate the longest number of days he came to office for consecutive dates..I have the Date column field is unique...So I tried this way - Select Id,Count(*) from...
TSQL: How can I update the value of an xml tag with the value of an xml tag from another related table?
Hello, How can I update the value of an xml tag with the value of an xml tag from another related table? something like this: UPDATE v2 SET [xml].modify ('replace value of (//TAG1/text())[1] with "CAST(v1.[xml].query(''//TAG2'') AS NVARCHAR(MAX))"') FROM table2 v2, table1 v1 WHERE v2.id = v1.id Thanks ...
NullPointerException with CallableStatement.getResultSet()
Hello, I have a stored proc in SQL Server 2005, which looks like the following (simplified) CREATE PROCEDURE FOO @PARAMS AS BEGIN -- STEP 1: POPULATE tmp_table DECLARE @tmp_table TABLE (...) INSERT INTO @tmp_table SELECT * FROM BAR -- STEP 2: USE @tmp_table FOR FINAL SELECT SELECT abc, pqr FROM BAZ JOIN @tmp_table...
TSQL, How to get smalldatetime's time between two smalldatetime's times ?
I have Table with two smalldatetime columns, where one is startTime and other one is endTime. I need to select all values from table which between times of both columns compared to getdate()' time. I'm using SQL-Server 2005. example startTime endTime value1 2/2/01 16:00 2/2/01 18:00 1 2/2/01 21:00 2/2/01 22:00 2 2/2/01 05:...
TSQL, select values from large many-to-many relationship
I have two tables Publishers and Campaigns, both have similar many-to-many relationships with Countries,Regions,Languages and Categories. more info Publisher2Categories has publisherID and categoryID which are foreign keys to publisherID in Publishers and categoryID in Categories which are identity columns. On other side i have Campai...
Optimzing TSQL code
My job is the maintain one application which heavy use SQL server (MSSQL2005). Until now middle server stores TSQL codes in XML and send dynamic TSQL queries without using stored procs. As I am able change those XML queries I want to migrate most of my queries to stored procs. Question is folowing: Most of my queries have same Where con...
SQL Server NOT EXISTS from more than one table
Hi: I need to return rows if exists, if not return which one of the passed value is NOT EXISTS: DECLARE @INPUT1 BIGINT DECLARE @INPUT2 BIGINT DECLARE @INPUT3 BIGINT SELECT e.Name, d.Name, c.Name FROM Employee e JOIN Department d ON e.DeptID = d.DeptID JOIN City c ON e.CityID = c.CityID WHERE e.EmpID = @INPUT1 AND d.Dept...
Want to check fields for data fast.
We have a database setup that consists of two parts: a static structure, and dynamic additions. For each database, the dynamic can be different, and sometimes we don't have data for all the dynamic fields. Rigt now, we check for empties by looking at the total count of records in the entire table, but we want to move to a more refined...
TSQL Help with simple query
I'm using SQL-SERVER 2005. I have two tables as you can see on diagram, CellularUsers and CellularPayments. I need to get users who needs to be charged when there are few rules: if last CellularPayments.paymentSum of userID was 8 then select all userID and userCellularNumbers where CellularPayments.date>getdate() if last CellularPay...
TSQL Check Database Instance Online
What is the best way to check if an instance of Microsoft SQL Server is up? I have a view that spans two instances of Microsoft SQL Server but before querying it in a stored procedure I would like to verify that the second instance of the server is up with a fallback option of just querying local data. The second instance is a linked s...
SQL Query to get largest datatype in schema
What is the query to get the top 5 ...data types ...used in a DB by size? blob > int ...
Executing T-Sql View from ADO.NET
Is this possible to execute a View from C# code. If it is then I would like to know if parametrized views exists and how should I use them. Parametrized means same parameters we use in Stored Procedures for stating where conditions. ...
How to fix incorrect syntax near 'LOGIN' in SQL Server 2005
ALTER USER [myuser] WITH LOGIN =[myuser] This query returns error in one of the SQL Server 2005 SP1 installation. Error is Incorrect syntax near 'LOGIN' ...
How can I pivot these key+values rows into a table of complete entries?
Maybe I demand too much from SQL but I feel like this should be possible. I start with a list of key-value pairs, like this: '0:First, 1:Second, 2:Third, 3:Fourth' etc. I can split this up pretty easily with a two-step parse that gets me a table like: EntryNumber PairNumber Item 0 0 0 1 0 ...