tsql

Maximum amount of where clauses in stored procedure

Me and my colleagues have a question regarding SQL Server 2008 query length and the SQL Server Optimizer. We are planning to generate some stored procedures that potentially have a lot of parameters. Inside of our stored procedure we will simply select some values from a table joining other tables. Our stored procedures will look like ...

SELECT and UPDATE table so there is no overlap of Threads

Hi, Say I have the following table: ID|Read ------- 1|true 2|false 3|false 4|false ... and I need to read the smallest ID, that has [Read] == false; plus, update that I have now read it. So if i execute my Stored Procedure dbo.getMinID, it will return ID: 2, and update [Read] -> true. CREATE PROCEDURE [dbo].[getMinID] ( @Quer...

SQL Server: GROUP BY Aggregation semantics with the PIVOT operator

I am on SQL Server 2008 and I have a table containing WA metrics of the following form : CREATE TABLE #VistitorStat ( datelow datetime, datehigh datetime, name varchar(255), cnt int ) Two days worth of data in the table looks like so: 2009-07-25 00:00:00.000 2009-07-26 00:00:00.000 New Visitor 221 2009-07-25 00:00:00.000...

How to get if a string is a number in T-SQL

Hello. Is there an easy way to get if string is an integer number (consists only of digits) in MS SQL 2005? Thank you for your help. ...

T SQL Convert a row to a string

Is there a valid way in SQL Server 2005 to read a row of data into a string? For example if my table looked like the following: ID | First Name | Last Name | Color ----------------------------------- 1 | Timmy | Jones | pink 2 | Martha | Fisher | green That I could get back as a result: '1 Timmy Jones pink' '2 Mart...

Querying for a unique value based on the aggregate of another value while grouping on a third value entirely

So I know this problem isn't a new one, but I'm trying to wrap my head around it and understand the best way to deal with scenarios like this. Say I have a hypothetical table 'X' that looks like this: GroupID ID (identity) SomeDateTime -------------------------------------------- 1 1000 1/1/01 1 1001 2/2/02 ...

SQL Table linking... is it better to have a linking table, or a delimited column?

My database has two tables, one contains a list of users, the other a list of roles. Each user will belong to one or more roles, and of course each role will have multiple users in it. I've come across two ways to link the information. The first is to add a third table which contains the ID's from both tables. A simple join will the...

Why not "Invalid column name XYZ" error in subquery; although column name is not in subquery table?

When I run this query SELECT CustomerId FROM Stocks.dbo.Suppliers It gives me this error. Invalid column name 'CustomerId'. This error is valid as there is no column CustomerId in Suppliers table; but when I use same query in subquery it does not give any error E.g. SELECT * FROM SomeOtherDb.dbo.Customer WHERE CustomerId In( SEL...

SQL to find first non-numeric character in a string

I inherited a table with identifiers in a format [nonnumericprefix][number]. For example (ABC123; R2D2456778; etc). I was wondering if there was a good way to split this in SQL into two fields, the largest integer formed from the right side, and the prefix, for example (ABC, 123; R2D, 2456778; etc). I know I can do this with a cursor,...

TSQL - Help with UNPIVOT

I am transforming data from this legacy table: Phones(ID int, PhoneNumber, IsCell bit, IsDeskPhone bit, IsPager bit, IsFax bit) These bit fields are not nullables and, potentially, all four bit fields can be 1. How can I unpivot this thing so that I end up with a separate row for each bit field = 1. For instance, if the original table...

String processing during table creation and inserting from another table

I have a table that contains file paths, like so: -------------------- |Files | -------------------- |path nvarchar(500)| -------------------- I want to split it into two tables, one containing unique directories and one containing filenames: --------------------------- |Files | ------------------------...

Can't create table relationship - SQL Server 2005

I am getting an error when trying to create a relationship within two tables in Sql Server 2005. I am attempting to create the relationships using the database diagram feature. I have a Player table and a Message table. I want to create two relationships from the Message table to the Player table. I can successfully create the first rela...

SQL [Conversion to bool]

C++Builder ADOQuery SQLServer I'm using a stored procedure with this select SELECT Name, COALESCE( ( SELECT TOP 1 0 FROM TbUserParam WHERE TbUserParam.ID_User = @ID_User AND TbUserParam.ID_Param = CfgListParIzm.ID_ListParIzm ), 1) Visi FROM CfgListParIzm WHERE ...

Modifying a function used as a column default in SQL Server

Hi, We're having an issue with a poorly coded SQL function(which works fine in live, but not in our test environment). This function is used to provide a default value in many tables, trying to ALTER the function returns a "Cannot ALTER ### because it is being referenced by object" error. Is there any way round this error message? T...

Correct sql/hql query (aggregate in where clause)

I want to do query as below. Query is wrong but describes my intentions. SELECT name, dateTime, data FROM Record WHERE dateTime = MAX(dateTime) Update: Ok. The query describes intentions not quite good. My bad. I want to select latest record for each person. ...

Performant way to get the maximum value of a running total in TSQL

We have a table of transactions which is structured like the following : TranxID int (PK and Identity field) ItemID int TranxDate datetime TranxAmt money TranxAmt can be positive or negative, so the running total of this field (for any ItemID) will go up and down as time goes by. Getting the current total is obviously simple...

Are there any automated white box testing tools for T-SQL stored procedures and/or functions?

I was wondering if there are any tools similar to Pex that analyze T-SQL stored procedures and functions (instead of managed code) in order to generate meaningful and interesting input values for parameterized unit tests. ...

SQL help: find rows in one table which don't exist in second table based on values in two columns

I have two tables. I am trying to find rows in one table which do not exist in second table based on values in two columns. (I have simplified the tables to include the two columns only). There are no primary/foreign keys between the two tables. Seems simple enough but I am having a brain block now! DDL: CREATE TABLE [dbo].[Table_1]( ...

SQL Server 2005 SSIS - How to get special information from the first line of a file

Say I have a text file that looks like this: date 1/1/2010 a,b,c a,b,d ... I want to import it into a table so it looks like this: 1/1/2010,a,b,c 1/1/2010,a,b,d ... What is an elegant way to do that? My best idea so far is to use a data flow package, and use a flat file source to read in the file (ignoring the first line) and lo...

SQL Buckets Determine Age Grouping

I have a database with legacy data that stores transactions and uses a "bucket" method for determining account balances. I need a way to get aged past due for accounts. Table Transactions TransactionId TransactionType (CHARGE,RECEIPT) Amount PostDate To get the current balance: SELECT SUM(CASE TransactionTypeId WHEN RECEIPT THEN Amoun...