Blogging 4 Life

If somebody wants to use LIKE in sp_executesql if you use syntax as follows it will give an error. Wrong Syntax: LIKE N''%'' @variableName ''%'' Error message received; Msg 102, Level 15, State 1, Line 24 Incorrect syntax near '%'. Correct Syntax: LIKE N''%'' + @variableName + ''%'' Have to double the quotes as well as '+' marks around the variable.

If you want to update a field of one table from another table it can be done as follows: Example: Table A field 2 has foreign key relationship with table B .Need to update table A field 3 with the value from table B. Table : A Field1 Field2 Field3 1 ------1------ Null 2 ------2------ Null Table : B Field1 Field2 1------- 3 2 -------4 Query 1: UPDATE A SET A.Field3 = B.Field2 FROM A a INNER JOIN B b ON A.Field2 = B.Field1 WHERE A.Field3 is null Query 2: update A set A.Field3 = (select B.Field2 from B where B.Field1=A.Field2) where A.Field3 is null Query 1 is faster than Query 2

In sql Server you cant add a column to mid of a table .If you add a new column to existing table then it will append end of the table. But in case of adding a column to to mid of the table you can do it like as follows; Create a new table with the required columns, Copy the data from the old table into the new table Delete the old table Then rename the new table. Sample Code ---Old table Create Table A (col1 varchar(50) null, col2 varchar(50) null) --Add new column col3 Begin Transaction Create Table B ( col1 varchar(50) null, col3 varchar(50) null, -- NEW COLUMN IN MIDDLE (NULLABLE) col2 varchar(50) null ) go if exists(select * from A) exec('insert into B (col1, col2) select col1, col2 from A with (holdlock tablockx)') go drop table A go execute sp_rename 'B', 'A', 'object' go commit