I'm trying to perform a group by action on an aliased column (example below) but can't determine the proper syntax. SELECT LastName + ', ' + FirstName AS 'FullName' FROM customers GROUP BY 'FullName' What is the correct syntax? EDIT Extending the question further (I had not expected the answers I had received) wou...
Are there any inherent problems with calling an ole object from tsql? I have been posed with a problem that I would like to solve solely using a stored procedure, is there anything I need to worry about with using a ole object as opposed to writing it in a clr based language? ...
I'd like to update a set of rows based on a simple criteria and get the list of PKs that were changed. I thought I could just do something like this but am worried about possible concurrency problems: SELECT Id FROM Table1 WHERE AlertDate IS NULL; UPDATE Table1 SET AlertDate = getutcdate() WHERE AlertDate IS NULL; If that is wrapped i...
I want to create a SQL Select to do a unit test in MS SQL Server 2005. The basic idea is this: select 'Test Name', foo = 'Result' from bar where baz = (some criteria) The idea being that, if the value of the "foo" column is "Result", then I'd get a value of true/1; if it isn't, I'd get false/0. Unfortunately, T-SQL doesn't like the ex...
I'm trying to find out the best practice when removing characters from the start of a string. In some languages, you can use MID without a length parameter however, in TSQL the length is required. Considering the following code, what is the best practise? (The hex string is variable length) DECLARE @sHex VARCHAR(66) SET @sHex = '0x7E2...
I have a TSQL script that is used to set up a database as part of my product's installation. It takes a number of steps which all together take five minutes or so. Sometimes this script fails on the last step because the user running the script does not have sufficient rights to the database. In this case I would like the script to fail ...
I'm writing a SQL query in SQL Server in which I need to replace multiple string values with a single string value. For example Product Quantity ------- -------- Apple 2 Orange 3 Banana 1 Vegetable 7 Dairy 6 would become Product Quantity ------- -------- Fruit 2 Fruit 3 Fruit ...
What am I doing wrong here? My use of the INTO clause seems to match the example I found in Microsoft's Transact-SQL reference pages, but I must be missing something. DECLARE @rowtemp table(TestRunID int, RunOrder int) SELECT TestRunID, ROW_NUMBER() OVER (ORDER BY TestRuns.TestTime ASC) AS 'RunOrder' INTO @rowtemp FROM TestRuns WHERE ...
I would like to do the following. Basically have a stored procedure call another stored procedure that returns a table. How is this done? ALTER PROC [GETSomeStuff] AS BEGIN @table = exec CB_GetLedgerView @accountId, @fromDate, @toDate, @pageSize, @pageNumber, @filter, @status, @sortExpression, @sortOrder, @virtualC...
From this question, a neat answer about using COALESCE to simplify complex logic trees. I considered the problem of short circuiting. For instance, in functions in most languages, arguments are fully evaluated and are then passed into the function. In C: int f(float x, float y) { return x; } f(a, a / b) ; // This will result in ...
Let's say I have an SQL statement that's syntactically and sematically correct so it executes. How can I in managements studio (or any other sql tool I guess) test sql-statements and if I notice that they broke something rollback? (in a sepparate query). ...
How could I convert a year-week (eg 0852 or 200852) into a date (eg 2008-12-31 or specifically a week-ending day ie Saturday 2008-12-27 or a week-beginning day ie 2008-12-21) ? Any day of the week-ending will do, Friday, Saturday, Sunday or Monday. ...
I'm securing the DB by only allowing interaction with the DB through a series of Sprocs; pretty common fare. I've dug up and modified a script which loops through and assigns the user EXECUTE permission for all non-system SProcs. It works a treat except that I'd ideally like to add it to the Master DB so that I can easily use it for any...
Hello, Do you know if there's a quick way in sql server (via transact-sql) that I could trim all the database string fields. ...
When executing the following using a smalldatetime constraint on the returned results, I get zero results: Execute sp_proc @DateOfBirth = '01/01/1900' or Execute sp_proc @DateOfBirth = '1900-01-01' But when using the following varchar argument all the sudden i get results which correspond to 1 Jan 2000. Does smalldatetime imp...
Where I'm at we have a software package running on a mainframe system. The mainframe makes a nightly dump into sql server, such that each of our clients has it's own database in the server. There are a few other databases in the server instance as well, plus some older client dbs with no data. We often need to run reports or check da...
I need to create a background job that processes a table looking for rows matching on a particular id with different statuses. It will store the row data in a string to compare the data against a row with a matching id. I know the syntax to get the row data but i have never tried comparing 2 rows from the same table before? How is it do...
Hi, I am trying to perform a simple calculation to some data returned from SQL Server: SELECT val1X, val1Y, val2X, val2Y FROM myTable I want to be able to calculate the following against the returned values and return just a single value - the result of the calculation below (this was originally written in VB6): If IsNull(val1X) O...
Is there any way of tracing user's identity when he/she triggers a a delete trigger on T-SQL 2005, e.g. with UNIQUEIDENTIFIER? If I do record the UNIQUEIDENTIFIER, how do I trace it back to the user? ...
I am trying to retrieve the largest number from a varchar column that includes both numbers and strings. An example of the data I'm working with: BoxNumber 123 A5 789 B1 I need to return the largest number (789 in this case) from the column while ignoring the non-numeric values of A5 and B1. I have found solutions that use custom func...