tsql

t-sql get all dates between 2 dates

Possible Duplicate: Getting Dates between a range of dates Let's say I have 2 dates (date part only, no time) and I want to get all dates between these 2 dates inclusive and insert them in a table. Is there an easy way to do it with a SQL statement (i.e without looping)? Ex: Date1: 2010-12-01 Date2: 2010-12-04 Table should h...

stored procedure returns nothing

This stored procedure doesn't work. I've checked the SQL and it returns the correct value when parsed directly to the DB. It's really strange! It just returns 0 rows. What could be wrong? ALTER PROCEDURE dbo.GetSaltOfUser ( @eMail nvarchar ) AS DECLARE @result nvarchar /* SET NOCOUNT ON */ BEGIN SELECT @result = salt FROM ...

override identity column

How do I override the identity column in MSSQL? I tried set identity_insert GeoCountry on update GeoCountry set CountryID = 18 where CountryID = 250 but I get back a Line 2: Cannot update identity column 'CountryID'. ...

Query a Subquery

I have to display the Rank of a user for which I plan to use the Rank() function in SQL Server. The Sql query to get the rank is as follows select a.user_id, RANK() OVER (ORDER BY (a.quant_points) DESC) AS QRANK, RANK() OVER (ORDER BY (a.verbal_points) DESC) AS VRANK, RANK() OVER (ORDER BY (a.dilr_points) DESC) AS DRANK, RAN...

Multiple inserts and selects in one stored procedure

I want to create a procedure that will A) check to see if TicketNumberInput is in table Tix_number.TicketNumber. B) if it does exisist, update the record to set UID to @uid and Set the Claimdate to GetDate() AND ... C) INSERT A RECORD INTO ANOTHER TABLE (TRANSACTION LOG TABLE) If the record does exist, simply add a record to the tr...

What is the need for !< and !> operators in WHERE clause ?

Could any one please brief the need for the not less than (!<) and not greater than (!>) operators in T-SQL ? I know we can replace the use of this operator with >= and <= operators ? Thanks in advance. ...

Should I use the largest string data type when creating a user defined function which operates on strings?

When creating a user defined function is it "bad" to just automatically use the largest string possible? For example, given the following UDF, I've used nvarchar(max) for my input string, where I know perfectly well that the function currently isn't going to need to accept nvarchar(max) and maybe I'm just forward thinking too much, but ...

Find and replace a string in sql server 2005

I am supposed to remove the following from the address field. Replace all words named as 'Flat' to empty space, also remove the number that comes along with it. eg. I have word called as 'Flat 234 5th Street'. It should be replaced as 5th Street. I gave the query as select ltrim(rtrim( substring ('Flat 123 5th Street', cha...

Stored procedure that (doesn't) returns hashed string

I'm trying to write a Stored Procedure which'll get a string, hash it with SHA1 and then return the hash. I can't seem to make it return @hashedString. I'll admit I'm a total beginner with T-SQL. I'm writing the T-SQL directly in the db. This is what I've gotten up to now: ALTER PROCEDURE dbo.ConvertToHash ( @stringToHash nvarcha...

Is there a way to set default value of field as other field in SQL Server 2005?

I have a table with 3 columns. One is sessionID as int, other one sessionLength t as int and other sessionActualLength as int. sessionLength is value calculated for time being in online chat in seconds. sessionActualLength comes in place where in certain hours users get bonuses for being online and each second of their time actually mea...

Getting value from stored procedure in another stored procedure

Sorry, lots of code coming up.. I saw another question like this that used output parameters. I'm using the RETURN statement to return the value I want to use. I have one stored procedure InsertMessage that looks like this: ALTER PROCEDURE dbo.InsertNewMessage ( @messageText text, @dateTime DATETIME, @byEmail bit, ...

How should I migrate this data into these Sql Server tables?

Hi folks, I wish to migrate some data from a single table into these new THREE tables. Here's my destination schema: Notice that I need to insert into the first Location table .. grab the SCOPE_IDENTITY() .. then insert the rows into the Boundary and Country tables. The SCOPE_IDENTITY() is killing me :( meaning, I can only see a w...

column to row in sql server ?

Table: CREATE TABLE Table1 ( col1 INT, col2 nvarchar(10), col3 INT, col4 INT ); INSERT INTO Table1 (col1, col2, col3, col4) VALUES (1, 'welcome', 3, 4); My table have different data type , col2 is nvarchar h can i do this ... result: col value --------------- col1 1 col2 welcome col3 3 col4 4 ...

about full text query in SQL

Hello everyone, I have a nvarchar column which contains English and Japanese text. I want to make full text search on this column. When configure full text search, we need to specify language option for word breaker (e.g. using English work breaker or using Japanese word breaker). I am wondering in this case, what language should I assi...

How to find the Second largest value from a table.?

One table with EmpSalary in Employee Table. I need to find the second largest Salary what is paid by the company.? How to find the Second largest value(Salary) from a table.? ...

SQL SUM on aggregate function

Perhaps I'm trying to be too clever for my own good, and I could possibly use a scalar variable to solve this. I'm trying to add 1 to my output from the subquery; INSERT INTO bookrevisiontbl (revisionnum, bookdate) SELECT SUM(MAX(revisionnum) + 1), GETDATE() FROM bookrevisiontbl_tbl However the following error occurs Cannot perfo...

TSQL, Ordering of ORDER BY.

Given a table of Name Age Bob 30 Paul 35 John 35 and a statement select * from table ORDER BY Age DESC What is the ordering of the resulting table? Name Age Paul 35 John 35 Bob 30 or Name Age John 35 Paul 35 Bob 30 or undefined behaviour? ...

Where Is the Syntax Error in this SQL?

I've got a query that is pretty much the same as many others which are used in the same library... but I did a lot of copy&paste on the SQL to add features to each one which are all similar but slightly different. Just below is the section which gives me the SQL Parser error. It fires at the Set rs = line. dim sql, rs sql = "DECLARE @st...

SQL Server TSQL Debugging - Massive View - Any Tips?

Hi, I've been asked to debug a view that's producing split rows where they should be grouping. In trying to debug, I'm trying testing all of the joined sub tables in isolation. The thing is, there's so many dependancies (11 joins in all), that once you get all of the dependencies required to run the query, you basically have the origina...

T-Sql group/sum query question

It's been a crappy Monday AM and I can't think straight. Can someone help me figure out how to group/sum the rows returned so that there is only ONE instance of the AssessorParcelNumber? So, instead of the following result set: 140-31-715-164 3545 2004-09-14 00:00:00.000 1665.00 0.00 0.00 1665.00 140-31-715-164 3545 2004...