You are correct. Local variables are not allowed in a VIEW. You can set a local variable in a table valued function, which returns a result set (like a view does.) http://msdn.microsoft.com/en-us/library/ms191165.aspx e.g. CREATE FUNCTION dbo.udf_foo() RETURNS @ret TABLE (col INT) AS BEGIN DECLARE @myvar INT; SELECT @myvar = 1; INSERT INTO @ret SELECT @myvar; … Read more
CONVERT(datetime, ‘24.04.2012’, 104) Should do the trick. See here for more info: CAST and CONVERT (Transact-SQL)
You could use a CURSOR in this case: DECLARE @id INT DECLARE @name NVARCHAR(100) DECLARE @getid CURSOR SET @getid = CURSOR FOR SELECT table.id, table.name FROM table OPEN @getid FETCH NEXT FROM @getid INTO @id, @name WHILE @@FETCH_STATUS = 0 BEGIN EXEC stored_proc @varName=@id, @otherVarName=”test”, @varForName=@name FETCH NEXT FROM @getid INTO @id, @name END CLOSE … Read more
See this blog post on extracting numbers from strings in SQL Server. Below is a sample using a string in your example: DECLARE @textval NVARCHAR(30) SET @textval=”AB ABCDE # 123″ SELECT LEFT(SUBSTRING(@textval, PATINDEX(‘%[0-9.-]%’, @textval), 8000), PATINDEX(‘%[^0-9.-]%’, SUBSTRING(@textval, PATINDEX(‘%[0-9.-]%’, @textval), 8000) + ‘X’) -1)
there are three ways you can use: the RETURN value, and OUTPUT parameter and a result set ALSO, watch out if you use the pattern: SELECT @Variable=column FROM table … if there are multiple rows returned from the query, your @Variable will only contain the value from the last row returned by the query. RETURN … Read more
How to search all columns of all tables in a database for a keyword? http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm EDIT: Here’s the actual T-SQL, in case of link rot: CREATE PROC SearchAllTables ( @SearchStr nvarchar(100) ) AS BEGIN — Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved. — Purpose: To search all columns of all tables for a … Read more
If you are using SQL Server 2012 and above, you can use FORMATMESSAGE. eg. DECLARE @s NVARCHAR(50) = ‘World’; DECLARE @d INT = 123; SELECT FORMATMESSAGE(‘Hello %s, %d’, @s, @d) — RETURNS ‘Hello World, 123’ More examples from MSDN: FORMATMESSAGE SELECT FORMATMESSAGE(‘Signed int %i, %d %i, %d, %+i, %+d, %+i, %+d’, 5, -5, 50, -50, … Read more
I implemented the standard Levenshtein edit distance function in TSQL with several optimizations that improves the speed over the other versions I’m aware of. In cases where the two strings have characters in common at their start (shared prefix), characters in common at their end (shared suffix), and when the strings are large and a … Read more
Best practice it to list your columns so you’re independent of table changes (new column or column order etc) insert into table1 (field1, field3) values (5,10) However, if you don’t want to do this, use the DEFAULT keyword insert into table1 values (5, DEFAULT, 10, DEFAULT)
Do it inline with the column creation: [load_date] SMALLDATETIME NOT NULL CONSTRAINT [df_load_date] DEFAULT GETDATE() I have used square brackets rather than quotes as many readers won’t work with QUOTED_IDENTIFIERS on by default.