SQL : Passing local variables to dynamic SQL statements.

Declare @DSQL nvarchar(4000), <@inputLocalParameter> , <@outLocalParameter>

Set @DSQL = N'Select <@outLocalParameter> = from where like '''+<@inputLocalParameter>+''''

Exec sp_executesql @query = @DSQL, @params = N'<@outLocalParameter> output', <@outLocalParameter> = <@outLocalParameter> output

Print <@outLocalParameter>


<@inputLocalParameter> - Parameter / Local variable that is an input to the dynamic query session.
<@outLocalParameter> - Parameter that is set and retrieved from the dynamic query session.
- Table name that the data is fetched from.
- Column Name referred in the table.
@query, @params - would be wondering wr they jumped from, they are inbuilt parameters of
the system stored procedure "sp_executesql", so don't u panic!!!

Remove duplicate records (using a self join)!

Delete from MyTable where IdentCol not in
(
Select * from MyTable A where 1 >
( Select Count(*) from MyTable B where A.Name = B.Name and A.IdentCol > B.IdentCol )
)

Note: The table should have an identity column or some column with unique integers.

Values of a column in comma separated format

This is a slightly tricky quey. Don get confused, just replace the column & table names and execute it.

DECLARE @str VARCHAR(5000)

SELECT
@str = COALESCE(@str+',','')+
(SELECT CAST(ColumnA as varchar(50)) FROM TableA A WHERE A.ColumnA = B.ColumnA) FROM
TableA B

Select @Str

Note: The tables used are the same tables with different aliases. And the whole query should be executed in a single batch(ie., no "Go" in between the these set of statements)

Split Function for MS SQL Server

A custom MS SQL Server function to split a string input and return an array:

Just run the below function in ur database:

CREATE function ReturnArray (@String varchar(4000), @Identifier nvarchar(5))
Returns @tbl Table(Value varchar(4000))
as
Begin
Declare @Index int, @Str nvarchar(4000), @TempStr nvarchar(4000)
Select @Str = @String
Select @Index = 0
Select @Identifier = '%'+@Identifier+'%'

While (PatIndex(@Identifier, @Str) > 0)
Begin
Select @Index = PatIndex(@Identifier, @Str)
Select @TempStr = Left(@Str, @Index-1)
Insert into @tbl Select @TempStr
Select @Str = SubString(@Str, PatIndex(@Identifier, @Str) + 1, Len(@Str) - @Index)
End
Insert into @tbl values(@str)
Return
End

This function expects 2 parameters as input :
1. string -- string to be split
2. seperator -- separator used ie(in 'D,i,n,e,s,h', ',' is the separator)

Select value from dbo.ReturnArray('D,i,n,e,s,h', ',') will return all the split values.