problem in pattern matching..

I store jobs in table and display them as per conditions selected by user.

my table has 4 columns.

company role Location postdate

abc Fresher Ny 9/10/2008 12:00:00 AM

xyz Vp-operations , LA 9/10/2008 12:03:00 AM

Now, this is my select query

SELECT Companyname, role, Location, date FROM callcenter where Location Like '%' + @city + '%' and DATEDIFF(Day, postdate, getdate( ) ) <= @datediff and Role Like '%' + @role + '%'

my problem is , for searching jobs, i allow user to select multiple roles. I get results when choose just single role. But if i select multiple roles, I don't get any results. Is there something wrong with

Role Like '%' + @role + '%' ???

I have defined @role varchar(100) and I am passing value to it as

cmd.Parameters.AddWithValue("@role", moreabout)

If i select 2 roles, Freshers and Vp-operations , variable moreabout holds "Fresher,Vp-operations"

then where is the problem ? ( I am using pattern matching , so it should show results. It shows result

if i select just Vp-operations or just Freshers )

Re: problem in pattern matching..

SELECT Companyname, role, Location, date FROM callcenter where Location Like '%' + @city + '%' and DATEDIFF(Day, postdate, getdate( ) ) <= @datediff and Role IN (Fresher,Vp-operations)

Re: problem in pattern matching..

anonymouswrites

SELECT Companyname, role, Location, date FROM callcenter where Location Like '%' + @city + '%' and DATEDIFF(Day, postdate, getdate( ) ) <= @datediff and Role IN (Fresher,Vp-operations)

But there are not only 2 roles viz. Fresher and Vp-operations . There are more than 10 roles.

and i cannot determine which roles user will select , so passing those values to stored procedure is again difficult task.

But whats wrong with my query? I am using pattern matching ..Do i have to modify pattern matching?

Re: problem in pattern matching..

in that table, in 1 row there will be only role or multiple roles.

if it is one role i am giving query now. if there will be multiple roles, tomorrow i will give you solution as i am not at my system now.

if you have split userdefined function fn_split(), then we can do like

select * from (select value,c.* from company c cross join fn_split(@Roles,','))t where charindex (value,role) > 0

Re: problem in pattern matching..

ramireddyindia

in that table, in 1 row there will be only role or multiple roles.

if it is one role i am giving query now. if there will be multiple roles, tomorrow i will give you solution as i am not at my system now.

if you have split userdefined function fn_split(), then we can do like

select * from (select value,c.* from company c cross join fn_split(@Roles,','))t where charindex (value,role) > 0

Thanks rami reddy ..

Each row of table holds just single role...

Please modify my original query if possible as I want to use it in stored procedure..

Also, fn_split() is in-built function?

Re: problem in pattern matching..

I hope you already have fn_split() user defined function. you need that function before you run this query. if it is not at you i will give you tomorrow as i don't have development environment now.

select Companyname, role, Location, date from (SELECT Companyname, role, Location, date,value FROM callcenter cross join fn_split(@role,',')) t where Location Like '%' + @city + '%' and DATEDIFF(Day, postdate, getdate( ) ) <= @datediff and charindex(value,role) > 0

Re: problem in pattern matching..

Create the comma separated string of role from the client side and use dynamic sql to achieve this.

Let us assume your roles string contains the value 'VP-Operations,Fresher,Administrator'

DECLARE @SQL NVARCHAR(1000)

SET @SQL = 'SELECT Companyname, role, Location, date FROM callcenter where Location Like '''%''' + @city + '''%''' and DATEDIFF(Day, postdate, getdate( ) ) <= @datediff and Role in (' + @role + ')'

EXEC SP_EXECUTESQL @SQL

Re: problem in pattern matching..

ramireddyindia
I hope you already have fn_split() user defined function.

Ok..i will wait..as I don't know whats that...In fact I am hearing it for the first time..

Thanks anyways..

Re: problem in pattern matching..

vijayakrishna

Create the comma separated string of role from the client side and use dynamic sql to achieve this.

Let us assume your roles string contains the value 'VP-Operations,Fresher,Administrator'

DECLARE @SQL NVARCHAR(1000)

SET @SQL = 'SELECT Companyname, role, Location, date FROM callcenter where Location Like '''%''' + @city + '''%''' and DATEDIFF(Day, postdate, getdate( ) ) <= @datediff and Role in (' + @role + ')'

EXEC SP_EXECUTESQL @SQL

Actually I am using this query to bind datalist dynamically. In such case I dont know from where to call

EXEC SP_EXECUTESQL @SQL

Dim cmd As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand("dbo.bporesults")
        cmd.CommandType = Data.CommandType.StoredProcedure

        cmd.Parameters.AddWithValue("@datediff", beforedays)
        cmd.Parameters.AddWithValue("@city", city1)
        cmd.Parameters.AddWithValue("@role", moreabout)

        cmd.Connection = objConn

        Dim ds As New System.Data.DataSet
        Dim da As System.Data.SqlClient.SqlDataAdapter = New System.Data.SqlClient.SqlDataAdapter(cmd)

        da.Fill(ds, "callcenter")
        DataList1.DataSource = ds
        DataList1.DataBind()

Re: problem in pattern matching..

anybody know this?...

Re: problem in pattern matching..

cmd.Parameters.AddWithValue("@role", moreabout)

I assume that moreabout is a comma separated value of roles like "VP-Operations, Fresher".

Your dbo.bporesults stored procedure can look something like

CREATE STOR PROC dbo.bporesults datediff int, city varchar(32), role varchar(200)
AS
BEGIN
--Other code required if any
DECLARE @SQL NVARCHAR(1000)
SET @SQL = 'SELECT Companyname, role, Location, date FROM callcenter where Location Like '''%''' + @city + '''%''' and DATEDIFF(Day, postdate, getdate( ) ) <= @datediff and Role in (' + @role + ')'
EXEC SP_EXECUTESQL @SQL
END

Re: problem in pattern matching..

vijayakrishna
cmd.Parameters.AddWithValue("@role", moreabout)

I assume that moreabout is a comma separated value of roles like "VP-Operations, Fresher".

Your dbo.bporesults stored procedure can look something like

CREATE STOR PROC dbo.bporesults datediff int, city varchar(32), role varchar(200)
AS
BEGIN
--Other code required if any
DECLARE @SQL NVARCHAR(1000)
SET @SQL = 'SELECT Companyname, role, Location, date FROM callcenter where Location Like '''%''' + @city + '''%''' and DATEDIFF(Day, postdate, getdate( ) ) <= @datediff and Role in (' + @role + ')'
EXEC SP_EXECUTESQL @SQL
END

Yes ..variable moreabout holds comma seperated values

seeing your post I wrote stored procedure

CREATE PROCEDURE dbo.bporesults
    (

    @datediff int ,
    @city varchar(20),
    @role varchar(100)
    @SQL NVARCHAR(1000)
    )

AS

    BEGIN

    SET @SQL='SELECT Companyname, Role, Moreaboutjob, candidate,Experience,Location,salarymin,salarymax,Interviewdate,postdate FROM callcenter where Location Like '%' + @city + '%' and DATEDIFF(Day, postdate, getdate( ) ) <= @datediff and Role in (' + @role + ')'
       EXEC SP_EXECUTESQL @SQL

END

But I am not able to save it ( hence some syntax error is there)..

Re: problem in pattern matching..

Now @role has a value VP-Operations,Fresher. But we require it as 'VP-Operations','Fresher' to use it IN constraint. To achieve this we use the following statement. Here we added single quotes to the current value and replaced all the commas (,) with (single quote + comma + sigle quote) ','

SET @role = '''' + REPLACE(@role,',',''',''') + ''''

The SET @SQL statement would be

SET @SQL='SELECT Companyname, Role, Moreaboutjob, candidate,Experience,Location,salarymin,salarymax,Interviewdate,postdate FROM callcenter where Location Like ''%' + @city + '%'' and DATEDIFF(Day, postdate, getdate( ) ) <= @datediff and Role in (' + @role + ')'

Re: problem in pattern matching..

vijayakrishna

Now @role has a value VP-Operations,Fresher. But we require it as 'VP-Operations','Fresher' to use it IN constraint. To achieve this we use the following statement. Here we added single quotes to the current value and replaced all the commas (,) with (single quote + comma + sigle quote) ','
SET @role = '''' + REPLACE(@role,',',''',''') + ''''

The SET @SQL statement would be

SET @SQL='SELECT Companyname, Role, Moreaboutjob, candidate,Experience,Location,salarymin,salarymax,Interviewdate,postdate FROM callcenter where Location Like ''%' + @city + '%'' and DATEDIFF(Day, postdate, getdate( ) ) <= @datediff and Role in (' + @role + ')'

Hi,

Please let me know where I am going wrong .. I copied your code only but not able to save the stored procedure

GOT ERRORS

Incorrect syntax nr @SQL
Must declare scalar variable @role.

Here is my stored procedure :

ALTER PROCEDURE dbo.bporesults
    (

    @datediff int ,
    @city varchar(20),
    @role varchar(100)
    @SQL NVARCHAR(1000)
    )


AS
    SET @role = '''' + REPLACE(@role,',',''',''') + ''''
    SET @SQL='SELECT Companyname, Role, Moreaboutjob, candidate,Experience,Location,salarymin,salarymax,Interviewdate,postdate FROM callcenter where Location Like ''%' + @city + '%'' and DATEDIFF(Day, postdate, getdate( ) ) <= @datediff and Role in (' + @role + ')'

    RETURN

Also, where should I write

EXEC SP_EXECUTESQL @SQL ( as i told you I am binding my datalist dynamically using this query..please check my previous posts)

Re: problem in pattern matching..

Your SP would be

ALTER PROCEDURE dbo.bporesults
(
	@datediff int ,
    @city varchar(20),
    @role varchar(100)
)
AS
    DECLARE @SQL NVARCHAR(1000)
    SET @role = '''' + REPLACE(@role,',',''',''') + ''''
    SET @SQL='SELECT Companyname, Role, Moreaboutjob, candidate,Experience,Location,salarymin,salarymax,Interviewdate,postdate FROM callcenter where Location Like ''%' + @city + '%'' and DATEDIFF(Day, postdate, getdate( ) ) <= @datediff and Role in (' + @role + ')'
	EXEC sp_executesql @SQL

Re: problem in pattern matching..

--select * from dbo.FN_SPLIT('Vijaya,Krishna,Paruchuri')
CREATE FUNCTION FN_SPLIT (@Val VARCHAR(MAX))
RETURNS @Table1 TABLE (SplitItem VARCHAR(100))
AS
--This function expects a comma separated string
--and it splits the values on comma basis
--and returns a table having a column SplitItem
BEGIN
	DECLARE @ValTemp VARCHAR(100)
	DECLARE @ValT VARCHAR(100)
	DECLARE @Start int
	DECLARE @End int


	IF SUBSTRING(@VaL,LEN(@Val),1) <> ','
		SET @Val = @Val + ','
		

	WHILE CHARINDEX(',',@Val)>0
		BEGIN
			SET @Start = CHARINDEX(',',@Val)
			SET @End = LEN(@Val) - @Start
			SET @ValT = SUBSTRING(@Val,1,@start-1)
			SET @Val = SUBSTRING(@Val,@start+1,@End)
			INSERT INTO @Table1 (SplitItem) VALUES (@ValT)
		END
	RETURN
END

Re: problem in pattern matching..

vijayakrishna
ALTER PROCEDURE dbo.bporesults ( @datediff int , @city varchar(20), @role varchar(100) ) AS DECLARE @SQL NVARCHAR(1000) SET @role = '''' + REPLACE(@role,',',''',''') + '''' SET @SQL='SELECT Companyname, Role, Moreaboutjob, candidate,Experience,Location,salarymin,salarymax,Interviewdate,postdate FROM callcenter where Location Like ''%' + @city + '%'' and DATEDIFF(Day, postdate, getdate( ) ) <= @datediff and Role in (' + @role + ')' EXEC sp_executesql @SQL

Thanks..now I am able to save my procedure

But while execution I got error

Must declare the scalar variable "@datediff".

Re: problem in pattern matching..

vijayakrishna
If you want fn_split try this,

First , i think its better to complete your previous way...as Swithcing different ways would be time consuming

If that does not work I will try this..

Thanks for this post though..

also, please tell me why i am getting that error when i am mentioning @datediff int in stored procedure?

ALTER PROCEDURE dbo.bporesults
(
    @datediff int ,
    @city varchar(20),
    @role varchar(100)
)
AS
    DECLARE @SQL NVARCHAR(1000)
    SET @role = '''' + REPLACE(@role,',',''',''') + ''''
    SET @SQL='SELECT Companyname, Role, Moreaboutjob, candidate,Experience,Location,salarymin,salarymax,Interviewdate,postdate FROM callcenter where Location Like ''%' + @city + '%'' and DATEDIFF(Day, postdate, getdate( ) ) <= @datediff and Role in (' + @role + ')'
    EXEC sp_executesql @SQL

Re: problem in pattern matching..

vijayakrishna

SET @SQL='SELECT Companyname, Role, Moreaboutjob, candidate,Experience,Location,salarymin,salarymax,Interviewdate,postdate FROM callcenter where Location Like ''%' + @city + '%'' and DATEDIFF(Day, postdate, getdate( ) ) <=''' + @datediff + ''' and Role in (' + @role + ')'

Ok..as i told you i am using this query to bind my datalist dynamically, I am writing following code on search button

cmd.Parameters.AddWithValue("@datediff", beforedays)
        cmd.Parameters.AddWithValue("@city", city1)
        cmd.Parameters.AddWithValue("@role", moreabout)

        cmd.Connection = objConn

        Dim ds As New System.Data.DataSet
        Dim da As System.Data.SqlClient.SqlDataAdapter = New System.Data.SqlClient.SqlDataAdapter(cmd)

        da.Fill(ds, "callcenter")                       //GOT ERROR HERE
        DataList1.DataSource = ds
        DataList1.DataBind()

Now following error comes

Conversion failed when converting the varchar value 'SELECT Companyname, Role, Moreaboutjob, candidate,Experience,Location,salarymin,salarymax,Interviewdate,postdate FROM callcenter where Location Like '%mumbai%' and DATEDIFF(Day, postdate, getdate( ) ) <='' to data type int.