tsql

How to force sql server to execute one command at a time without using GO?

I would like to make sure that a series of commands are executed in a serial way, like in update TABLE1... update TABLE2... update TABLE3... I would like that update TABLE2 starts only when update TABLE1 has completed. Of course I can do this with GO: update TABLE1... GO update TABLE2... GO update TABLE3... GO But in this case i w...

Continue statement in while loop in TSQL?

Hi, In Sql Server 2000 and 2005 I am running a select statement in a while loop. JFYI, this select statement is connecting to many linked servers and getting some values. IF there is any error, i want it should still execute next statement in the loop (similar to continue statement in c#) Example:- while @rowcount < 10 begin set @sq...

SQL server schema and default schema

I have a schema define in my database. Except now everytime I do a sql statement I have to provide the schema ... SELECT * FROM [myschema].table I set the default schema for my user using management studio and also ran the ALTER USER myUser WITH DEFAULT_SCHEMA [myschema] and I still get the invalid object 'table' when writing a query...

help with TSQL IN statement with int

I am trying to create the following select statement in a stored proc @dealerids nvarchar(256) SELECT * FROM INVOICES as I WHERE convert(nvarchar(20), I.DealerID) in (@dealerids) I.DealerID is an INT in the table. and the Parameter for dealerids would be formatted such as (8820, 8891, 8834) When I run this with parameters provided ...

is this stored procedure correct?

I have a stored procedure below, and I don't know if it's correct. I am inserting records in table PlanFinder.InvalidAwps, and deleting the same records from the PlanFinder.NdcAwp table. Also, can anybody help me with adding try catch in the same stored procedure? Alter procedure PlanFinder.InsertInvalidRecords as Truncate table [P...

sql 2005 grouping data that is dynamic

I have data that looks like this Investor Contact IBM James IBM Dean IBM Sean Microsoft Bill Microsoft Steve I need the data to look like this Investor Contact IBM James,Dean,Sean Microsoft Bill,Steve OR if the above is impossible Investor Contact1 Cont...

Is there any way to index a varchar column as a datetime?

I'm querying a varchar column that contains all valid datetimes. In the query, I cast the column to datetime. Keep in mind, changing the column's datatype definition to datetime is not an option. Just trust me, it's not an option. Through query analysis, I see that the query would be much faster if I could add an index on this column....

Convert tabular data to XML using SQL Server

I have a flattened table that contains columns that represent groups that need to be displayed in XML. Example data: Market, Label, Style, Type XXX, YYY, JJJ, 111 XXX, YYY, JJJ, 222 XXX, YYY, JJJ, 333 XXX, YYY, JJJ, 444 XXX, YYY, LLL, 111 XXX, YYY, LLL, 222 XXX, YYY, LLL, 333 ...

rank items in sql by dates

I have movies table with movieID,movieName and movieDate. I want to select first all movies which added in last 24 hours and order them by date and the rest order by newid(). ie. 1 babylon 28/09/2010 16:00 2.12 monekys 28/09/2010 8:00 3.se7en 25/09/2010 5:00 4.snatch 26/09/2010 18:00 How can i achieve it? Final answer which is a c...

Tsql: can i use a variable as database reference

Hi, i want to accomplish this: update @sourceDatabase.dbo.PredictedPrices and then set @sourceDatabase as a variable. But i'm not allowed? Incorrect syntax near '.'. Is there another way? ...

If statement within switch

I know I'm just thinking of the logic all wrong here but how do I achieve the following: update @table set column1 = case when column1 <> '' then rtrim(column1) + ', ' + rtrim(column2)--if statement here else rtrim(column2) end ...

Consecutive streak of dates

Hi, Hopefully this isn't a dupe of another question, but I couldn't see it anywhere else - also this is a simplified version of another question I asked, hopefully to get me started on working out how to approach it. I am looking to work out consecutive ranges of payments where there has been at least one payment in each month. I have ...

How to get unique rows from TSQL based on date open and closed

I have two tables this is data from one: ID ANS_ID USER_ID Date_Opened 06146723 858735205 55258 2009-02-20 12:59:47.0000000 06146723 481768765 55258 2009-09-16 17:04:22.0000000 and table 2: ID ANS_ID USER_ID Date_Closed 06146723 630993597 5258 2009-04-02 14:35:23.0000000 0614672...

Why does new T-SQL of SQL Server 2008 work on database in compatability mode 80 ?

Experimenting with new features of T-SQL, I've run into a puzzle. Here is some new syntax supported by SQL 2008 and I'd expect it to work on databases set for compatibility mode 100 (i.e. 2008) and not work for compat mode 80 (i.e. 2000). Yet this works for a database set for SQL SERVER 2000 compatibility mode on a SQL 2008 instance of S...

Using T-SQL how do you calcualte a test grade percentage?

It what should be a simple task, or a simple google search, I have come up empty. I am trying to simply calculate a grade percentage. I am trying to use: Select round((133 / 150), 2) * 100 That results in 0. I am trying to calcualte to get a score of 89%. I have tried multiple combinations and I am beginning to think it is either too ...

SQL Server: get all parent child URL's from a single table

I have a table that looks like this: Slug | Parent | MetaTitle | MetaDescription | MetaKeywords The Url's on the site are built by appending the slug to the parent as long as the parent is not null. So if I have data like so: Slug | Parent ----------------------------------------- Home null About n...

T-SQL query to remove non matching value from ColumnA where value doesn't exist in ColumnB

I have two databases, with the following tables: DatabaseA TableA ColumnA (varChar(10) DatabaseB TableB ColumnB (varChar(10) I need a query that: Ignores NULLs or empty strings Finds rows where the value of ColumnA does not exist in columnB In this case, replaces the value of the non matching row in ColumnA with '' (empty string)...

optimizing the DELETE statement with a count

Which of the following two statements would you consider to be the most effective for deleting large number of rows? Statement #1: DELETE TOP (@count) FROM ProductInfo WHERE productId = @productid Statement #2: Derived table DELETE t1 FROM (SELECT TOP (@count) * from ProductInfo WHERE productId = @productId v) t1 ...

How to get value of deleted test in trigger?

Hi I need to put a trigger on Delete on Table1. On delete of record from Table1, I need to update Table2 in trigger, but i need the value of deleted record in the trigger. Example:- IF OBJECT_ID ('UpdateLog','TR') IS NOT NULL DROP TRIGGER UpdateLog; GO CREATE TRIGGER UpdateLog ON Table_1 AFTER DELETE AS UPDATE Table_2 SET D...

SQL Server 2005 seperate stored procedure CSV value into multiple columns?

I'm a SQL Server 2005 newb and I have to do something that should be easy but I'm having difficulties. For the time being my stored procedure is storing csv in one column in my db. I need to break apart the csv into multiple columns. The number of values in a the CSV parameter is static, there is always gonna be 8 values and they are al...