Archive for the ‘SQL’ Category

Today after having so many days effort, I finally able to find out how we can manage multiple stoplists for a database?

“Why MS-SQL Server 2008 is providing facility to create multiple custom stoplists?”, I was thinking over this and wondering how we can manage any specific stoplist out of multiple stoplist created? I have tried to find out this over internet but finally found in MSDN only. May be because I was searching for my content with inappropreate keywords. What so ever, Finally I found what I wanted.

I wanted to create multiple stop-lists and then wanted to use each one of them in my different requirements for the same database.

For example, There is a Job portal where applicants are searching for their appropreate Jobs, whereas Employers/Companies are searching for the right applicants with some keywords. Now consider that there are two different stop-lists that I would like to manage, one Stop-List will be specific to Job Search only and will be used by applicants for searching appropreate jobs and another stop-list will be specific to applicant’s CV/Resume Search and will be used by Companies to find out right candidate.

How can I manage this?

Well its really simple. You need to follow below steps and some scripts.

1. Create Fulltext Index for a database table that you wanted to include in a search query (e.g. job details or CV details tables)
How to?: http://msdn.microsoft.com/en-us/library/ms187317.aspx

2. Create StopLists of your choice (Custom Stoplist)
How to?: http://msdn.microsoft.com/en-us/library/cc280405.aspx

3. Associate any Stoplist to any fulltext indexed table of your choice
How to?: http://msdn.microsoft.com/en-us/library/ms188359.aspx

(E.g. for 3rd Step you can use a simple SQL script as below one to associate any stoplist to any fulltext indexed table:
ALTER FULLTEXT INDEX ON table_name
SET STOPLIST stoplist_name WITH NO POPULATION ;)

Now if you apply any fulltext search on the tables say for example “Job Details” then fulltext search will ignore the words you have defined into associated stoplist that you have created.

To find out the association between the stoplist and associated fulltext catalog you can execute following SQL query:
select fulltext_catalog_id,stoplist_id, * from sys.fulltext_indexes;

To find out catalog details, you can execute following SQL query:
select * from sys.fulltext_catalogs

Happy Programming.

Advertisements

DECLARE @SQLQuery VARCHAR(4000)
SET @SQLQuery = 'SELECT @Query_Result_Count = Count(Column_1) FROM table_A'
DECLARE @Outer_Variable AS INT
EXEC Sp_executesql
@query = @SQLQuery,
@params = N'@Query_Result_Count INT OUTPUT',
@Query_Result_Count = @Outer_Variable OUTPUT

If you can see, we have used “@Query_Result_Count” variable into @SQLQuery which is not declared anywhere before into the declared variable script. This is because we are passing this variable as a parameter into SP_ExecuteSQL statement the same way we are executing the stored procedure. You can check this with “@params = N’@Query_Result_Count INT OUTPUT’,” line of statement.

You can get the output of any dynamic SQL into your local SQL variable by declaring that variable first like we did “DECLARE @Outer_Variable AS INT” and then get this variable filled with the values return by executing the SP_ExecuteSQL statement this way “@Query_Result_Count = @Outer_Variable OUTPUT”. Here “@Query_Result_Count” is the variable that we are passing and using into dynamic SQL.

You can also pass multiple parameters to Sp_ExecuteSQL this way,
@params = N'@Para1 INT, @Para2 Varchar(200), @Query_Result_Count INT OUTPUT '

Also you Need to provide values for input parameters like @Para1, and @Para2 by declaring an outer variable or any static values this way:


DECLARE @Outer_Para1 INT
SET @Outer_Para1 = 10
DECLARE @Outer_Para2 VARCHAR(200)
SET @Outer_Para2 = 'Test'
EXEC Sp_executesql
@query = 'SELECT @Query_Result_Count = Count(Column_1) FROM table_A WHERE Column_2 = @Para1 AND Column_3 = @Para2 ',
@params = N'@Para1 INT, @Para2 Varchar(200), @Query_Result_Count INT OUTPUT',
@Para1 = @Outer_Para1 , @Para2 = @Outer_Para2, @Query_Result_Count = @Outer_Variable OUTPUT

I found some problem using Union and Group By clause together, and I later realized that my technique of using these words together is not proper.

Most of the users are making the same mistakes when they deal with Union and Group By clause.

You can use like below:

SELECT x FROM (
SELECT x FROM table_A
UNION
SELECT x FROM table_B
)
GROUP BY x;

You can retrieve comma separated values from a database table using following type of SQL scripts

DECLARE @varList VARCHAR(1000)
SET @varList = SPACE(0)
SELECT @varList = @varList + ', ' + ContactName
FROM Customers
SELECT @varList
SELECT SUBSTRING(@varList, 3, 1000)

You can see that the statement “SELECT @varList” will return list like “ , Maria Anders, Ana Trujillo, Antonio Moreno, Thomas Hardy….etc ”

Here you can see the result will have extra comma at the start of the string result. To remove this I have added another select statement “SELECT SUBSTRING(@varList, 3, 1000)”. This will return string upto 1000 characters only, you can set this value as per your requirements.

After a very long time I am posting this stuff…

I have noticed that on day to day activity with MS SQL, one requires to split any string constant every now and then and so I have decided to make a post of it.

Please create below SQL function into your database and then you can use this to split any string constant with any separator you specified.


CREATE function [dbo].[fun_Split]
(
@String nvarchar(4000),
@Delimiter char(1)
) Returns @Results Table (Items nvarchar(4000))
As
Begin
Declare @Index int
Declare @Slice nvarchar(4000)
Select @Index = 1
If @String Is NULL Return
While @Index != 0
Begin
Select @Index = CharIndex(@Delimiter, @String)
If (@Index != 0)
Select @Slice = left(@String, @Index - 1)
else
Select @Slice = @String
Insert into @Results(Items) Values (@Slice)
Select @String = right(@String, Len(@String) - @Index)
If Len(@String) = 0 break
End
Return
End

You can use this function like example given below:
SELECT Items FROM dbo.fun_Split('1,2,3,4',',')

This will return a table with Column “Items” where you can find each comma separated values in different rows.

Happy Scripting!

I have two tables for e.g. tblStudents and tblSchools. Both are having different columns. For a reason I need to merge the two tables into one temporary table.

For example “tblStudents” contains the following design.

StudentID FirstName LastName Address DOB

Where as “tblSchools” contains the following design.

SchoolID Name Address IsSecodarySchool

Now, you would like to merge this two table columns into one temporary table then you can use the following sql script.

SELECT * INTO #tbl_Temp
FROM tblStudent, tblSchool where 1>2

Here, I have mentioned the “where” clause because I wanted to copy only the columns and not the data. You can ignore this clause if you want to copy the entire data too.

You can add as many tables you want to merge by separating with comma (,) between the table names.

You can add specific columns from specific tables by using table alias and column name combination.
For example
SELECT tblStudent.FirstName,tblStudent.LastName,tblSchool.Name INTO #tbl_Temp
FROM tblStudent, tblSchool where 1>2

Happy Programming!

Today I came to know how to search for a specific keyword or any object like tables or columns of any tables used in a procedure. This is really useful when you have a bunch of procedures and you need to findout the procedure that have some specific tables, columns or even any keyword you wanted to search.

Here is the SQL Query that will be useful to get appropriate result set of stored procedures which contains your provided search keywords.

SELECT Name
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%your keyword%'