Example Schema: RowID Quantity ModifiedPrice GroupPrice CustomPrice SalePrice ---------------------------------------------------------------------------- 1 5 20.00 0 15.00 17.00 2 2 14.00 7.00 22.00 0 3 9 10.00...
Scenario: Need to pass n arguments to a stored procedure. One of the arguments is of type varchar(x). That varchar argument needs to be constructed from a handful of other varchar variables. This problem uses SQL Server 2005, but this behaviour applies to all versions of SQL Server. Setup: DECLARE @MyString varchar(500), @MyBar varchar...
I need a different random number for each row in my table. The following seemingly obvious code uses the same random value for each row. SELECT table_name, RAND() magic_number FROM information_schema.tables I'd like to get a INT or a FLOAT out this. The rest of the story is I'm going to use the random number to create an random dat...
There are two things here i want to accomplish. List I want to pass a datetime into my stored procedure in the following format ('2007-05-28 00:00:00'), but that doesn't seem to work unless i use {ts '2007-05-28 00:00:00'} The next thing is I want to increase the @SEAL_DATE by a second so it can be used to check the dates between the e...
What are the scoping rules for transaction isolation levels in SQL Server 2005? I know what the different levels mean, but not how to properly apply them outside of a manually run script. I can't find a guide for practical use in production-quality code. Obviously, the scope begins when you use a command like this: SET TRANSACTION ISOL...
I have dynamic sql statement I've created in a stored proc. I need to iterate over the results using a cursor. I'm having a hard time figuring out the right syntax. Here's what I'm doing SELECT @SQLStatement = 'SELECT userId FROM users' DECLARE @UserId DECLARE users_cursor CURSOR FOR EXECUTE @SQLStatment --Fails here. Doesn''t like th...
Hi, I have a script that connects to SQL Server 2005 named instance using osql. But the script throws the following errors: [SQL Native Client]Named Pipes Provider: Could not open a connection to SQL Server [53]. [SQL Native Client]Login timeout expired [SQL Native Client]An error has occurred while establishing a connection to the se...
At the moment I use DATEPART(yy, MY_DATE) = DATEPART(yy, GETDATE()) to get Year-To-Date details and have to convert it into the revolving last 12 months. How would I do that? ...
What is the TSQL syntax to format my output so that the column values appear as a string, seperated by commas. Example, my table CARS has the following: CarID CarName 1 Porsche 2 Mercedes 3 Ferrari How do I get the car names as : 'Porsche, Mercedes, Ferrari' Thanks, Murali ...
Hello, i'm trying to come up with a way to combine rows in a table based on the longest string in any of the rows based on a row key example CREATE TABLE test1 (akey int not null , text1 varchar(50) NULL, text2 varchar(50) NULL, text3 varchar(50) NULL ) INSERT INTO test1 VALUES ( 1,'Winchester Road','crawley',NU...
I'm executing a INSERT to a sql 2008 db. How do I specify in T-SQL to insert NOW in a DATETIMEOFFSET column? GETDATE()? ...
For my SQL queries, I usually do the following for SELECT statements: SELECT ... FROM table t WHERE 1=1 AND t.[column1] = @param1 AND t.[column2] = @param2 This will make it easy if I need to add / remove / comment any WHERE clauses, since I don't have to care about the first line. Is there any performance hit when using this pat...
I've got a query that looks a bit like this: select records.id, contacts.name + ' (' + contacts.organization + ')' as contact, from records left join contacts on records.contact = contacts.contactid Problem is - contacts.organization is frequently empty, and I get contacts like "John Smith ()". Is there a way to only concatenate the o...
I am creating a status board module for my project team. The status board allows the user to to set their status as in or out and they can also provide a note. I was planning on storing all the information in a single table ... and example of the data follows: Date User Status Notes -----------------------------------...
Hello sql gurus, Is there a way to limit the number of rows in a SQL Server 2000 database, such that older rows are deleted when new ones come in? I have some data that I'd like to keep around for a about thirty days - after that, I don't care whether the data lays around or is deleted - as long as the table doesn't become huge. Any o...
Hi, Simple query, possibly impossible but I know there are some clever people out there :) Given a boolean parameter, I wish to define my where clause to either limit a certain column's output - or do nothing. So, given parameter @bit = 1 this would be the result: where column = 1 given parameter @bit = 0 this would be the result: ...
I want to split a column into 4 columns based on column. Eg: column value includes 'City_Name' , 'State' ,Zipcode' ,'Country' I want to split it into 4 different columns like City_Name, State, Zipcode, Country. How can I do this using T-SQL? ...
I understand that T-SQL is not object oriented. I need to write a set of functions that mimics method overloading in C#. Is function overloading supported in T-SQL in any way? If there is a hack to do this, is it recommended? ...
I found the solution for this question in C#, but I can't translate it to a single query T-SQL, since my C# implementation requires branching (if then else). I also found the following C# solution, which could be translated to a single query T-SQL but it doesn't produce the correct results public static double GetAzimuth(WGSCoord c1, W...
this code basically translates characters based on position in one string to the character at the same position in another string and it runs for all rows in the table. when I run this (simplified version): DECLARE @R char(40) DECLARE @U char(40) SET @R=' abcdefghijklmnopqrstuvwxyz!@#$%^&*()_+'+char(181) SET @U=REVE...