How to add a column in TSQL after a specific column?
Unfortunately you can’t. If you really want them in that order you’ll have to create a new table with the columns in that order and copy data. Or rename columns etc. There is no easy way.
tsql
T-SQL – How to write a conditional join
If I understand correctly it sounds like your join conditions would be the equivalent of ON ((@AddressID IS NOT NULL) AND (alias.column = @AddressID)) and likewise for the group join. I use this conditional join at times.
Extract the first word of a string in a SQL Server query
SELECT CASE CHARINDEX(‘ ‘, @Foo, 1) WHEN 0 THEN @Foo — empty or single word ELSE SUBSTRING(@Foo, 1, CHARINDEX(‘ ‘, @Foo, 1) – 1) — multi-word END You could perhaps use this in a UDF: CREATE FUNCTION [dbo].[FirstWord] (@value varchar(max)) RETURNS varchar(max) AS BEGIN RETURN CASE CHARINDEX(‘ ‘, @value, 1) WHEN 0 THEN @value ELSE … Read more
Return row of every n’th record
This is where ROW_NUMBER can help. It requires an order-by clause but this is okay because an order-by is present (and required to guarantee a particular order). SELECT t.id, t.key FROM ( SELECT id, key, ROW_NUMBER() OVER (ORDER BY key) AS rownum FROM datatable ) AS t WHERE t.rownum % 30 = 0 — or … Read more
How do I use T-SQL’s Case/When?
SELECT CASE WHEN xyz.something = 1 THEN ‘SOMETEXT’ WHEN xyz.somethingelse = 1 THEN ‘SOMEOTHERTEXT’ WHEN xyz.somethingelseagain = 2 THEN ‘SOMEOTHERTEXTGOESHERE’ ELSE ‘SOMETHING UNKNOWN’ END AS ColumnName;
Get current value from a SQL Server SEQUENCE
You can select the current_value from sys.sequences: SELECT current_value FROM sys.sequences WHERE name=”OrderNumberSequence” ; DEMO
How to calculate age in T-SQL with years, months, and days
Here is some T-SQL that gives you the number of years, months, and days since the day specified in @date. It takes into account the fact that DATEDIFF() computes the difference without considering what month or day it is (so the month diff between 8/31 and 9/1 is 1 month) and handles that with a … Read more
When should I not use a semicolon?
T-SQL syntax does not require a semicolon to terminate a statement. Actually, this is deprecated1. I can’t remember for sure, but I think you can still get away with not using them in the upcoming SQL Server 2012, but some version after that will likely require a semi-colon for every statement. Using a semi-colon is … Read more
Using a cursor with dynamic SQL in a stored procedure
A cursor will only accept a select statement, so if the SQL really needs to be dynamic make the declare cursor part of the statement you are executing. For the below to work your server will have to be using global cursors. Declare @UserID varchar(100) declare @sqlstatement nvarchar(4000) –move declare cursor into sql to be … Read more
Return multiple values from a SQL Server function
Change it to a table-valued function Please refer to the following link, for example.