| Groups | Blog | Home |
sql server full text search : Sending NULL value to parameter of Stored Procedure
I am getting the following error, when i am trying to pass the NULL value to
the parameter of Stored procedure. That parameter has been used in Contains clause in T-SQL. Server: Msg 7603, Level 15, State 1, Procedure spCMXLSearchReports, Line 256 Syntax error in search condition, or empty or null search condition ''. This one is working fine, when i am sending any value to the parameter, which is mentioned Contains caluse. It's not accepting any null values. Should i use Dynamic query? Can you please help me.
You can do an existence check in your header, ie
Create searchproc (@search varchar(200)) as .... having no value here will have the proc complain when nothing is entered. If you do some triming of the value of @search in your proc, you could do an existence check before firing it; Create searchproc (@search varchar(200)) as select @search=Replace(@search, char(34)+char(34), char(34)) if len(replace(@search,char(34),'')) > 0 select * from mytable where contains(*, @search) else return -1 -- Hilary Cotter Director of Text Mining and Database Strategy RelevantNOISE.Com - Dedicated to mining blogs for business intelligence. This posting is my own and doesn't necessarily represent RelevantNoise's positions, strategies or opinions. Looking for a SQL Server replication book? http://www.nwsu.com/0974973602.html Looking for a FAQ on Indexing Services/SQL FTS http://www.indexserverfaq.com [quoted text, click to view] "Siva" <Siva@discussions.microsoft.com> wrote in message news:A987EBE5-F578-420E-9A75-220B280B01E1@microsoft.com... >I am getting the following error, when i am trying to pass the NULL value >to > the parameter of Stored procedure. That parameter has been used in > Contains > clause in T-SQL. > > Server: Msg 7603, Level 15, State 1, Procedure spCMXLSearchReports, Line > 256 > Syntax error in search condition, or empty or null search condition ''. > > This one is working fine, when i am sending any value to the parameter, > which is mentioned Contains caluse. > > It's not accepting any null values. Should i use Dynamic query? Can > you > please help me. > > >
I would avoid dynamic queries as much as possible. Can you post your proc
here, or send it to me offline? -- Hilary Cotter Director of Text Mining and Database Strategy RelevantNOISE.Com - Dedicated to mining blogs for business intelligence. This posting is my own and doesn't necessarily represent RelevantNoise's positions, strategies or opinions. Looking for a SQL Server replication book? http://www.nwsu.com/0974973602.html Looking for a FAQ on Indexing Services/SQL FTS http://www.indexserverfaq.com [quoted text, click to view] "Siva" <Siva@discussions.microsoft.com> wrote in message news:2C1EF77D-1AD6-4A70-A053-9456C08CD2AC@microsoft.com... > Hi Hilary, > > Thanks for your response. I would like to confirm with you one more > thing. > > My SP is having around 25 parameters apart from Full Text Search > parameter, I would like to execute the SP for remaining parameters and > gets > the result. > > I would like to exclude only Contains Clause, If the parameter value is > NULL / Empty. > > Should i use Dynamic query to achieve the above points? > > Thanks, > Siva > > > "Hilary Cotter" wrote: > >> You can do an existence check in your header, ie >> >> Create searchproc (@search varchar(200)) >> as >> .... >> having no value here will have the proc complain when nothing is entered. >> If >> you do some triming of the value of @search in your proc, you could do an >> existence check before firing it; >> >> Create searchproc (@search varchar(200)) >> as >> select @search=Replace(@search, char(34)+char(34), char(34)) >> if len(replace(@search,char(34),'')) > 0 >> select * from mytable where contains(*, @search) >> else >> return -1 >> >> -- >> Hilary Cotter >> Director of Text Mining and Database Strategy >> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence. >> >> This posting is my own and doesn't necessarily represent RelevantNoise's >> positions, strategies or opinions. >> >> Looking for a SQL Server replication book? >> http://www.nwsu.com/0974973602.html >> >> Looking for a FAQ on Indexing Services/SQL FTS >> http://www.indexserverfaq.com >> >> >> >> "Siva" <Siva@discussions.microsoft.com> wrote in message >> news:A987EBE5-F578-420E-9A75-220B280B01E1@microsoft.com... >> >I am getting the following error, when i am trying to pass the NULL >> >value >> >to >> > the parameter of Stored procedure. That parameter has been used in >> > Contains >> > clause in T-SQL. >> > >> > Server: Msg 7603, Level 15, State 1, Procedure spCMXLSearchReports, >> > Line >> > 256 >> > Syntax error in search condition, or empty or null search condition ''. >> > >> > This one is working fine, when i am sending any value to the parameter, >> > which is mentioned Contains caluse. >> > >> > It's not accepting any null values. Should i use Dynamic query? Can >> > you >> > please help me. >> > >> > >> > >> >> >>
Hi Hilary,
Thanks for your response. I would like to confirm with you one more thing. My SP is having around 25 parameters apart from Full Text Search parameter, I would like to execute the SP for remaining parameters and gets the result. I would like to exclude only Contains Clause, If the parameter value is NULL / Empty. Should i use Dynamic query to achieve the above points? Thanks, Siva [quoted text, click to view] "Hilary Cotter" wrote:
> You can do an existence check in your header, ie > > Create searchproc (@search varchar(200)) > as > .... > having no value here will have the proc complain when nothing is entered. If > you do some triming of the value of @search in your proc, you could do an > existence check before firing it; > > Create searchproc (@search varchar(200)) > as > select @search=Replace(@search, char(34)+char(34), char(34)) > if len(replace(@search,char(34),'')) > 0 > select * from mytable where contains(*, @search) > else > return -1 > > -- > Hilary Cotter > Director of Text Mining and Database Strategy > RelevantNOISE.Com - Dedicated to mining blogs for business intelligence. > > This posting is my own and doesn't necessarily represent RelevantNoise's > positions, strategies or opinions. > > Looking for a SQL Server replication book? > http://www.nwsu.com/0974973602.html > > Looking for a FAQ on Indexing Services/SQL FTS > http://www.indexserverfaq.com > > > > "Siva" <Siva@discussions.microsoft.com> wrote in message > news:A987EBE5-F578-420E-9A75-220B280B01E1@microsoft.com... > >I am getting the following error, when i am trying to pass the NULL value > >to > > the parameter of Stored procedure. That parameter has been used in > > Contains > > clause in T-SQL. > > > > Server: Msg 7603, Level 15, State 1, Procedure spCMXLSearchReports, Line > > 256 > > Syntax error in search condition, or empty or null search condition ''. > > > > This one is working fine, when i am sending any value to the parameter, > > which is mentioned Contains caluse. > > > > It's not accepting any null values. Should i use Dynamic query? Can > > you > > please help me. > > > > > > > >
Hi Simon, thanks for the links. Erland's arguments are precisely why I am
trying to encourage the OP NOT to use dynamic sql. -- Hilary Cotter Director of Text Mining and Database Strategy RelevantNOISE.Com - Dedicated to mining blogs for business intelligence. This posting is my own and doesn't necessarily represent RelevantNoise's positions, strategies or opinions. Looking for a SQL Server replication book? http://www.nwsu.com/0974973602.html Looking for a FAQ on Indexing Services/SQL FTS http://www.indexserverfaq.com [quoted text, click to view] "Simon Sabin" <SimonSabin@noemail.noemail> wrote in message news:62959f1a2c6ba8c8d0e58ad6371c@msnews.microsoft.com... > Hello Hilary, > > With that number of options dynamic SQL is the best option, you need to > understand the implications, SQL injection, multiple plans. Be careful > about plans based on one set of parameters that may not perform well with > another set. > > Have a read of Erlands Dynamic sql article > http://www.sommarskog.se/dynamic_sql.html > > > > Simon Sabin > SQL Server MVP > http://sqlblogcasts.com/blogs/simons > > >> I would avoid dynamic queries as much as possible. Can you post your >> proc here, or send it to me offline? >> >> This posting is my own and doesn't necessarily represent >> RelevantNoise's positions, strategies or opinions. >> >> Looking for a SQL Server replication book? >> http://www.nwsu.com/0974973602.html >> Looking for a FAQ on Indexing Services/SQL FTS >> http://www.indexserverfaq.com >> "Siva" <Siva@discussions.microsoft.com> wrote in message >> news:2C1EF77D-1AD6-4A70-A053-9456C08CD2AC@microsoft.com... >> >>> Hi Hilary, >>> >>> Thanks for your response. I would like to confirm with you one more >>> thing. >>> >>> My SP is having around 25 parameters apart from Full Text Search >>> parameter, I would like to execute the SP for remaining parameters >>> and >>> gets >>> the result. >>> I would like to exclude only Contains Clause, If the parameter value >>> is NULL / Empty. >>> >>> Should i use Dynamic query to achieve the above points? >>> >>> Thanks, >>> Siva >>> "Hilary Cotter" wrote: >>> >>>> You can do an existence check in your header, ie >>>> >>>> Create searchproc (@search varchar(200)) >>>> as >>>> .... >>>> having no value here will have the proc complain when nothing is >>>> entered. >>>> If >>>> you do some triming of the value of @search in your proc, you could >>>> do an >>>> existence check before firing it; >>>> Create searchproc (@search varchar(200)) >>>> as >>>> select @search=Replace(@search, char(34)+char(34), char(34)) >>>> if len(replace(@search,char(34),'')) > 0 >>>> select * from mytable where contains(*, @search) >>>> else >>>> return -1 >>>> -- >>>> Hilary Cotter >>>> Director of Text Mining and Database Strategy >>>> RelevantNOISE.Com - Dedicated to mining blogs for business >>>> intelligence. >>>> This posting is my own and doesn't necessarily represent >>>> RelevantNoise's positions, strategies or opinions. >>>> >>>> Looking for a SQL Server replication book? >>>> http://www.nwsu.com/0974973602.html >>>> Looking for a FAQ on Indexing Services/SQL FTS >>>> http://www.indexserverfaq.com >>>> "Siva" <Siva@discussions.microsoft.com> wrote in message >>>> news:A987EBE5-F578-420E-9A75-220B280B01E1@microsoft.com... >>>> >>>>> I am getting the following error, when i am trying to pass the NULL >>>>> value >>>>> to >>>>> the parameter of Stored procedure. That parameter has been used in >>>>> Contains >>>>> clause in T-SQL. >>>>> Server: Msg 7603, Level 15, State 1, Procedure spCMXLSearchReports, >>>>> Line >>>>> 256 >>>>> Syntax error in search condition, or empty or null search condition >>>>> ''. >>>>> This one is working fine, when i am sending any value to the >>>>> parameter, which is mentioned Contains caluse. >>>>> >>>>> It's not accepting any null values. Should i use Dynamic query? >>>>> Can >>>>> you >>>>> please help me. > >
Oops sorry Simon, I think I missed your point. However until I look at what
he is trying to do, I can't tell if it can't be done without using dynamic sql. -- Hilary Cotter Director of Text Mining and Database Strategy RelevantNOISE.Com - Dedicated to mining blogs for business intelligence. This posting is my own and doesn't necessarily represent RelevantNoise's positions, strategies or opinions. Looking for a SQL Server replication book? http://www.nwsu.com/0974973602.html Looking for a FAQ on Indexing Services/SQL FTS http://www.indexserverfaq.com [quoted text, click to view] "Simon Sabin" <SimonSabin@noemail.noemail> wrote in message news:62959f1a2c6ba8c8d0e58ad6371c@msnews.microsoft.com... > Hello Hilary, > > With that number of options dynamic SQL is the best option, you need to > understand the implications, SQL injection, multiple plans. Be careful > about plans based on one set of parameters that may not perform well with > another set. > > Have a read of Erlands Dynamic sql article > http://www.sommarskog.se/dynamic_sql.html > > > > Simon Sabin > SQL Server MVP > http://sqlblogcasts.com/blogs/simons > > >> I would avoid dynamic queries as much as possible. Can you post your >> proc here, or send it to me offline? >> >> This posting is my own and doesn't necessarily represent >> RelevantNoise's positions, strategies or opinions. >> >> Looking for a SQL Server replication book? >> http://www.nwsu.com/0974973602.html >> Looking for a FAQ on Indexing Services/SQL FTS >> http://www.indexserverfaq.com >> "Siva" <Siva@discussions.microsoft.com> wrote in message >> news:2C1EF77D-1AD6-4A70-A053-9456C08CD2AC@microsoft.com... >> >>> Hi Hilary, >>> >>> Thanks for your response. I would like to confirm with you one more >>> thing. >>> >>> My SP is having around 25 parameters apart from Full Text Search >>> parameter, I would like to execute the SP for remaining parameters >>> and >>> gets >>> the result. >>> I would like to exclude only Contains Clause, If the parameter value >>> is NULL / Empty. >>> >>> Should i use Dynamic query to achieve the above points? >>> >>> Thanks, >>> Siva >>> "Hilary Cotter" wrote: >>> >>>> You can do an existence check in your header, ie >>>> >>>> Create searchproc (@search varchar(200)) >>>> as >>>> .... >>>> having no value here will have the proc complain when nothing is >>>> entered. >>>> If >>>> you do some triming of the value of @search in your proc, you could >>>> do an >>>> existence check before firing it; >>>> Create searchproc (@search varchar(200)) >>>> as >>>> select @search=Replace(@search, char(34)+char(34), char(34)) >>>> if len(replace(@search,char(34),'')) > 0 >>>> select * from mytable where contains(*, @search) >>>> else >>>> return -1 >>>> -- >>>> Hilary Cotter >>>> Director of Text Mining and Database Strategy >>>> RelevantNOISE.Com - Dedicated to mining blogs for business >>>> intelligence. >>>> This posting is my own and doesn't necessarily represent >>>> RelevantNoise's positions, strategies or opinions. >>>> >>>> Looking for a SQL Server replication book? >>>> http://www.nwsu.com/0974973602.html >>>> Looking for a FAQ on Indexing Services/SQL FTS >>>> http://www.indexserverfaq.com >>>> "Siva" <Siva@discussions.microsoft.com> wrote in message >>>> news:A987EBE5-F578-420E-9A75-220B280B01E1@microsoft.com... >>>> >>>>> I am getting the following error, when i am trying to pass the NULL >>>>> value >>>>> to >>>>> the parameter of Stored procedure. That parameter has been used in >>>>> Contains >>>>> clause in T-SQL. >>>>> Server: Msg 7603, Level 15, State 1, Procedure spCMXLSearchReports, >>>>> Line >>>>> 256 >>>>> Syntax error in search condition, or empty or null search condition >>>>> ''. >>>>> This one is working fine, when i am sending any value to the >>>>> parameter, which is mentioned Contains caluse. >>>>> >>>>> It's not accepting any null values. Should i use Dynamic query? >>>>> Can >>>>> you >>>>> please help me. > >
Hello Hilary,
With that number of options dynamic SQL is the best option, you need to understand the implications, SQL injection, multiple plans. Be careful about plans based on one set of parameters that may not perform well with another set. Have a read of Erlands Dynamic sql article http://www.sommarskog.se/dynamic_sql.html Simon Sabin SQL Server MVP http://sqlblogcasts.com/blogs/simons [quoted text, click to view] > I would avoid dynamic queries as much as possible. Can you post your > proc here, or send it to me offline? > > This posting is my own and doesn't necessarily represent > RelevantNoise's positions, strategies or opinions. > > Looking for a SQL Server replication book? > http://www.nwsu.com/0974973602.html > Looking for a FAQ on Indexing Services/SQL FTS > http://www.indexserverfaq.com > "Siva" <Siva@discussions.microsoft.com> wrote in message > news:2C1EF77D-1AD6-4A70-A053-9456C08CD2AC@microsoft.com... > >> Hi Hilary, >> >> Thanks for your response. I would like to confirm with you one more >> thing. >> >> My SP is having around 25 parameters apart from Full Text Search >> parameter, I would like to execute the SP for remaining parameters >> and >> gets >> the result. >> I would like to exclude only Contains Clause, If the parameter value >> is NULL / Empty. >> >> Should i use Dynamic query to achieve the above points? >> >> Thanks, >> Siva >> "Hilary Cotter" wrote: >> >>> You can do an existence check in your header, ie >>> >>> Create searchproc (@search varchar(200)) >>> as >>> .... >>> having no value here will have the proc complain when nothing is >>> entered. >>> If >>> you do some triming of the value of @search in your proc, you could >>> do an >>> existence check before firing it; >>> Create searchproc (@search varchar(200)) >>> as >>> select @search=Replace(@search, char(34)+char(34), char(34)) >>> if len(replace(@search,char(34),'')) > 0 >>> select * from mytable where contains(*, @search) >>> else >>> return -1 >>> -- >>> Hilary Cotter >>> Director of Text Mining and Database Strategy >>> RelevantNOISE.Com - Dedicated to mining blogs for business >>> intelligence. >>> This posting is my own and doesn't necessarily represent >>> RelevantNoise's positions, strategies or opinions. >>> >>> Looking for a SQL Server replication book? >>> http://www.nwsu.com/0974973602.html >>> Looking for a FAQ on Indexing Services/SQL FTS >>> http://www.indexserverfaq.com >>> "Siva" <Siva@discussions.microsoft.com> wrote in message >>> news:A987EBE5-F578-420E-9A75-220B280B01E1@microsoft.com... >>> >>>> I am getting the following error, when i am trying to pass the NULL >>>> value >>>> to >>>> the parameter of Stored procedure. That parameter has been used in >>>> Contains >>>> clause in T-SQL. >>>> Server: Msg 7603, Level 15, State 1, Procedure spCMXLSearchReports, >>>> Line >>>> 256 >>>> Syntax error in search condition, or empty or null search condition >>>> ''. >>>> This one is working fine, when i am sending any value to the >>>> parameter, which is mentioned Contains caluse. >>>> >>>> It's not accepting any null values. Should i use Dynamic query? >>>> Can >>>> you >>>> please help me
Hi Hilary,
I have sent the sample SP to your mail id. Please let me know your comments. Thanks, Siva [quoted text, click to view] "Hilary Cotter" wrote:
> Oops sorry Simon, I think I missed your point. However until I look at what > he is trying to do, I can't tell if it can't be done without using dynamic > sql. > > -- > Hilary Cotter > Director of Text Mining and Database Strategy > RelevantNOISE.Com - Dedicated to mining blogs for business intelligence. > > This posting is my own and doesn't necessarily represent RelevantNoise's > positions, strategies or opinions. > > Looking for a SQL Server replication book? > http://www.nwsu.com/0974973602.html > > Looking for a FAQ on Indexing Services/SQL FTS > http://www.indexserverfaq.com > > > > "Simon Sabin" <SimonSabin@noemail.noemail> wrote in message > news:62959f1a2c6ba8c8d0e58ad6371c@msnews.microsoft.com... > > Hello Hilary, > > > > With that number of options dynamic SQL is the best option, you need to > > understand the implications, SQL injection, multiple plans. Be careful > > about plans based on one set of parameters that may not perform well with > > another set. > > > > Have a read of Erlands Dynamic sql article > > http://www.sommarskog.se/dynamic_sql.html > > > > > > > > Simon Sabin > > SQL Server MVP > > http://sqlblogcasts.com/blogs/simons > > > > > >> I would avoid dynamic queries as much as possible. Can you post your > >> proc here, or send it to me offline? > >> > >> This posting is my own and doesn't necessarily represent > >> RelevantNoise's positions, strategies or opinions. > >> > >> Looking for a SQL Server replication book? > >> http://www.nwsu.com/0974973602.html > >> Looking for a FAQ on Indexing Services/SQL FTS > >> http://www.indexserverfaq.com > >> "Siva" <Siva@discussions.microsoft.com> wrote in message > >> news:2C1EF77D-1AD6-4A70-A053-9456C08CD2AC@microsoft.com... > >> > >>> Hi Hilary, > >>> > >>> Thanks for your response. I would like to confirm with you one more > >>> thing. > >>> > >>> My SP is having around 25 parameters apart from Full Text Search > >>> parameter, I would like to execute the SP for remaining parameters > >>> and > >>> gets > >>> the result. > >>> I would like to exclude only Contains Clause, If the parameter value > >>> is NULL / Empty. > >>> > >>> Should i use Dynamic query to achieve the above points? > >>> > >>> Thanks, > >>> Siva > >>> "Hilary Cotter" wrote: > >>> > >>>> You can do an existence check in your header, ie > >>>> > >>>> Create searchproc (@search varchar(200)) > >>>> as > >>>> .... > >>>> having no value here will have the proc complain when nothing is > >>>> entered. > >>>> If > >>>> you do some triming of the value of @search in your proc, you could > >>>> do an > >>>> existence check before firing it; > >>>> Create searchproc (@search varchar(200)) > >>>> as > >>>> select @search=Replace(@search, char(34)+char(34), char(34)) > >>>> if len(replace(@search,char(34),'')) > 0 > >>>> select * from mytable where contains(*, @search) > >>>> else > >>>> return -1 > >>>> -- > >>>> Hilary Cotter > >>>> Director of Text Mining and Database Strategy > >>>> RelevantNOISE.Com - Dedicated to mining blogs for business > >>>> intelligence. > >>>> This posting is my own and doesn't necessarily represent > >>>> RelevantNoise's positions, strategies or opinions. > >>>> > >>>> Looking for a SQL Server replication book? > >>>> http://www.nwsu.com/0974973602.html > >>>> Looking for a FAQ on Indexing Services/SQL FTS > >>>> http://www.indexserverfaq.com > >>>> "Siva" <Siva@discussions.microsoft.com> wrote in message > >>>> news:A987EBE5-F578-420E-9A75-220B280B01E1@microsoft.com... > >>>> > >>>>> I am getting the following error, when i am trying to pass the NULL > >>>>> value > >>>>> to > >>>>> the parameter of Stored procedure. That parameter has been used in > >>>>> Contains > >>>>> clause in T-SQL. > >>>>> Server: Msg 7603, Level 15, State 1, Procedure spCMXLSearchReports, > >>>>> Line > >>>>> 256 > >>>>> Syntax error in search condition, or empty or null search condition > >>>>> ''. > >>>>> This one is working fine, when i am sending any value to the > >>>>> parameter, which is mentioned Contains caluse. > >>>>> > >>>>> It's not accepting any null values. Should i use Dynamic query? > >>>>> Can > >>>>> you > >>>>> please help me. > > > > > >
Hi Simon,
Thanks for your response. I have gone through the links. It's very usefull. Thanks, Siva [quoted text, click to view] "Simon Sabin" wrote:
> Hello Hilary, > > With that number of options dynamic SQL is the best option, you need to understand > the implications, SQL injection, multiple plans. Be careful about plans based > on one set of parameters that may not perform well with another set. > > Have a read of Erlands Dynamic sql article http://www.sommarskog.se/dynamic_sql.html > > > > Simon Sabin > SQL Server MVP > http://sqlblogcasts.com/blogs/simons > > > > I would avoid dynamic queries as much as possible. Can you post your > > proc here, or send it to me offline? > > > > This posting is my own and doesn't necessarily represent > > RelevantNoise's positions, strategies or opinions. > > > > Looking for a SQL Server replication book? > > http://www.nwsu.com/0974973602.html > > Looking for a FAQ on Indexing Services/SQL FTS > > http://www.indexserverfaq.com > > "Siva" <Siva@discussions.microsoft.com> wrote in message > > news:2C1EF77D-1AD6-4A70-A053-9456C08CD2AC@microsoft.com... > > > >> Hi Hilary, > >> > >> Thanks for your response. I would like to confirm with you one more > >> thing. > >> > >> My SP is having around 25 parameters apart from Full Text Search > >> parameter, I would like to execute the SP for remaining parameters > >> and > >> gets > >> the result. > >> I would like to exclude only Contains Clause, If the parameter value > >> is NULL / Empty. > >> > >> Should i use Dynamic query to achieve the above points? > >> > >> Thanks, > >> Siva > >> "Hilary Cotter" wrote: > >> > >>> You can do an existence check in your header, ie > >>> > >>> Create searchproc (@search varchar(200)) > >>> as > >>> .... > >>> having no value here will have the proc complain when nothing is > >>> entered. > >>> If > >>> you do some triming of the value of @search in your proc, you could > >>> do an > >>> existence check before firing it; > >>> Create searchproc (@search varchar(200)) > >>> as > >>> select @search=Replace(@search, char(34)+char(34), char(34)) > >>> if len(replace(@search,char(34),'')) > 0 > >>> select * from mytable where contains(*, @search) > >>> else > >>> return -1 > >>> -- > >>> Hilary Cotter > >>> Director of Text Mining and Database Strategy > >>> RelevantNOISE.Com - Dedicated to mining blogs for business > >>> intelligence. > >>> This posting is my own and doesn't necessarily represent > >>> RelevantNoise's positions, strategies or opinions. > >>> > >>> Looking for a SQL Server replication book? > >>> http://www.nwsu.com/0974973602.html > >>> Looking for a FAQ on Indexing Services/SQL FTS > >>> http://www.indexserverfaq.com > >>> "Siva" <Siva@discussions.microsoft.com> wrote in message > >>> news:A987EBE5-F578-420E-9A75-220B280B01E1@microsoft.com... > >>> > >>>> I am getting the following error, when i am trying to pass the NULL > >>>> value > >>>> to > >>>> the parameter of Stored procedure. That parameter has been used in > >>>> Contains > >>>> clause in T-SQL. > >>>> Server: Msg 7603, Level 15, State 1, Procedure spCMXLSearchReports, > >>>> Line > >>>> 256 > >>>> Syntax error in search condition, or empty or null search condition > >>>> ''. > >>>> This one is working fine, when i am sending any value to the > >>>> parameter, which is mentioned Contains caluse. > >>>> > >>>> It's not accepting any null values. Should i use Dynamic query? > >>>> Can > >>>> you > >>>> please help me. > >
Hi Siva
Your proc consists of creation of a temp table, checking and setting various parameters and then a multi-table join. This will not benefit from dynamic sql. -- Hilary Cotter Director of Text Mining and Database Strategy RelevantNOISE.Com - Dedicated to mining blogs for business intelligence. This posting is my own and doesn't necessarily represent RelevantNoise's positions, strategies or opinions. Looking for a SQL Server replication book? http://www.nwsu.com/0974973602.html Looking for a FAQ on Indexing Services/SQL FTS http://www.indexserverfaq.com [quoted text, click to view] "Siva" <Siva@discussions.microsoft.com> wrote in message news:EEFA62C3-F758-4F76-9235-93BE71736FEF@microsoft.com... > Hi Hilary, > I have sent the sample SP to your mail id. > Please let me know your comments. > Thanks, > Siva > > "Hilary Cotter" wrote: > >> Oops sorry Simon, I think I missed your point. However until I look at >> what >> he is trying to do, I can't tell if it can't be done without using >> dynamic >> sql. >> >> -- >> Hilary Cotter >> Director of Text Mining and Database Strategy >> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence. >> >> This posting is my own and doesn't necessarily represent RelevantNoise's >> positions, strategies or opinions. >> >> Looking for a SQL Server replication book? >> http://www.nwsu.com/0974973602.html >> >> Looking for a FAQ on Indexing Services/SQL FTS >> http://www.indexserverfaq.com >> >> >> >> "Simon Sabin" <SimonSabin@noemail.noemail> wrote in message >> news:62959f1a2c6ba8c8d0e58ad6371c@msnews.microsoft.com... >> > Hello Hilary, >> > >> > With that number of options dynamic SQL is the best option, you need to >> > understand the implications, SQL injection, multiple plans. Be careful >> > about plans based on one set of parameters that may not perform well >> > with >> > another set. >> > >> > Have a read of Erlands Dynamic sql article >> > http://www.sommarskog.se/dynamic_sql.html >> > >> > >> > >> > Simon Sabin >> > SQL Server MVP >> > http://sqlblogcasts.com/blogs/simons >> > >> > >> >> I would avoid dynamic queries as much as possible. Can you post your >> >> proc here, or send it to me offline? >> >> >> >> This posting is my own and doesn't necessarily represent >> >> RelevantNoise's positions, strategies or opinions. >> >> >> >> Looking for a SQL Server replication book? >> >> http://www.nwsu.com/0974973602.html >> >> Looking for a FAQ on Indexing Services/SQL FTS >> >> http://www.indexserverfaq.com >> >> "Siva" <Siva@discussions.microsoft.com> wrote in message >> >> news:2C1EF77D-1AD6-4A70-A053-9456C08CD2AC@microsoft.com... >> >> >> >>> Hi Hilary, >> >>> >> >>> Thanks for your response. I would like to confirm with you one more >> >>> thing. >> >>> >> >>> My SP is having around 25 parameters apart from Full Text Search >> >>> parameter, I would like to execute the SP for remaining parameters >> >>> and >> >>> gets >> >>> the result. >> >>> I would like to exclude only Contains Clause, If the parameter value >> >>> is NULL / Empty. >> >>> >> >>> Should i use Dynamic query to achieve the above points? >> >>> >> >>> Thanks, >> >>> Siva >> >>> "Hilary Cotter" wrote: >> >>> >> >>>> You can do an existence check in your header, ie >> >>>> >> >>>> Create searchproc (@search varchar(200)) >> >>>> as >> >>>> .... >> >>>> having no value here will have the proc complain when nothing is >> >>>> entered. >> >>>> If >> >>>> you do some triming of the value of @search in your proc, you could >> >>>> do an >> >>>> existence check before firing it; >> >>>> Create searchproc (@search varchar(200)) >> >>>> as >> >>>> select @search=Replace(@search, char(34)+char(34), char(34)) >> >>>> if len(replace(@search,char(34),'')) > 0 >> >>>> select * from mytable where contains(*, @search) >> >>>> else >> >>>> return -1 >> >>>> -- >> >>>> Hilary Cotter >> >>>> Director of Text Mining and Database Strategy >> >>>> RelevantNOISE.Com - Dedicated to mining blogs for business >> >>>> intelligence. >> >>>> This posting is my own and doesn't necessarily represent >> >>>> RelevantNoise's positions, strategies or opinions. >> >>>> >> >>>> Looking for a SQL Server replication book? >> >>>> http://www.nwsu.com/0974973602.html >> >>>> Looking for a FAQ on Indexing Services/SQL FTS >> >>>> http://www.indexserverfaq.com >> >>>> "Siva" <Siva@discussions.microsoft.com> wrote in message >> >>>> news:A987EBE5-F578-420E-9A75-220B280B01E1@microsoft.com... >> >>>> >> >>>>> I am getting the following error, when i am trying to pass the NULL >> >>>>> value >> >>>>> to >> >>>>> the parameter of Stored procedure. That parameter has been used in >> >>>>> Contains >> >>>>> clause in T-SQL. >> >>>>> Server: Msg 7603, Level 15, State 1, Procedure spCMXLSearchReports, >> >>>>> Line >> >>>>> 256 >> >>>>> Syntax error in search condition, or empty or null search condition >> >>>>> ''. >> >>>>> This one is working fine, when i am sending any value to the >> >>>>> parameter, which is mentioned Contains caluse. >> >>>>> >> >>>>> It's not accepting any null values. Should i use Dynamic query? >> >>>>> Can >> >>>>> you >> >>>>> please help me. >> > >> > >> >> >>
Hello Siva,
Can you post the SP Simon Sabin SQL Server MVP http://sqlblogcasts.com/blogs/simons [quoted text, click to view] > Hi Hilary, > I have sent the sample SP to your mail id. > Please let me know your comments. > Thanks, > Siva > > "Hilary Cotter" wrote: > >> Oops sorry Simon, I think I missed your point. However until I look >> at what he is trying to do, I can't tell if it can't be done without >> using dynamic sql. >> >> -- >> Hilary Cotter >> Director of Text Mining and Database Strategy >> RelevantNOISE.Com - Dedicated to mining blogs for business >> intelligence. >> This posting is my own and doesn't necessarily represent >> RelevantNoise's positions, strategies or opinions. >> >> Looking for a SQL Server replication book? >> http://www.nwsu.com/0974973602.html >> Looking for a FAQ on Indexing Services/SQL FTS >> http://www.indexserverfaq.com >> "Simon Sabin" <SimonSabin@noemail.noemail> wrote in message >> news:62959f1a2c6ba8c8d0e58ad6371c@msnews.microsoft.com... >> >>> Hello Hilary, >>> >>> With that number of options dynamic SQL is the best option, you need >>> to understand the implications, SQL injection, multiple plans. Be >>> careful about plans based on one set of parameters that may not >>> perform well with another set. >>> >>> Have a read of Erlands Dynamic sql article >>> http://www.sommarskog.se/dynamic_sql.html >>> >>> Simon Sabin >>> SQL Server MVP >>> http://sqlblogcasts.com/blogs/simons >>>> I would avoid dynamic queries as much as possible. Can you post >>>> your proc here, or send it to me offline? >>>> >>>> This posting is my own and doesn't necessarily represent >>>> RelevantNoise's positions, strategies or opinions. >>>> >>>> Looking for a SQL Server replication book? >>>> http://www.nwsu.com/0974973602.html >>>> Looking for a FAQ on Indexing Services/SQL FTS >>>> http://www.indexserverfaq.com >>>> "Siva" <Siva@discussions.microsoft.com> wrote in message >>>> news:2C1EF77D-1AD6-4A70-A053-9456C08CD2AC@microsoft.com... >>>>> Hi Hilary, >>>>> >>>>> Thanks for your response. I would like to confirm with you one >>>>> more thing. >>>>> >>>>> My SP is having around 25 parameters apart from Full Text Search >>>>> parameter, I would like to execute the SP for remaining parameters >>>>> and >>>>> gets >>>>> the result. >>>>> I would like to exclude only Contains Clause, If the parameter >>>>> value >>>>> is NULL / Empty. >>>>> Should i use Dynamic query to achieve the above points? >>>>> >>>>> Thanks, >>>>> Siva >>>>> "Hilary Cotter" wrote: >>>>>> You can do an existence check in your header, ie >>>>>> >>>>>> Create searchproc (@search varchar(200)) >>>>>> as >>>>>> .... >>>>>> having no value here will have the proc complain when nothing is >>>>>> entered. >>>>>> If >>>>>> you do some triming of the value of @search in your proc, you >>>>>> could >>>>>> do an >>>>>> existence check before firing it; >>>>>> Create searchproc (@search varchar(200)) >>>>>> as >>>>>> select @search=Replace(@search, char(34)+char(34), char(34)) >>>>>> if len(replace(@search,char(34),'')) > 0 >>>>>> select * from mytable where contains(*, @search) >>>>>> else >>>>>> return -1 >>>>>> -- >>>>>> Hilary Cotter >>>>>> Director of Text Mining and Database Strategy >>>>>> RelevantNOISE.Com - Dedicated to mining blogs for business >>>>>> intelligence. >>>>>> This posting is my own and doesn't necessarily represent >>>>>> RelevantNoise's positions, strategies or opinions. >>>>>> Looking for a SQL Server replication book? >>>>>> http://www.nwsu.com/0974973602.html >>>>>> Looking for a FAQ on Indexing Services/SQL FTS >>>>>> http://www.indexserverfaq.com >>>>>> "Siva" <Siva@discussions.microsoft.com> wrote in message >>>>>> news:A987EBE5-F578-420E-9A75-220B280B01E1@microsoft.com... >>>>>>> I am getting the following error, when i am trying to pass the >>>>>>> NULL >>>>>>> value >>>>>>> to >>>>>>> the parameter of Stored procedure. That parameter has been used >>>>>>> in >>>>>>> Contains >>>>>>> clause in T-SQL. >>>>>>> Server: Msg 7603, Level 15, State 1, Procedure >>>>>>> spCMXLSearchReports, >>>>>>> Line >>>>>>> 256 >>>>>>> Syntax error in search condition, or empty or null search >>>>>>> condition >>>>>>> ''. >>>>>>> This one is working fine, when i am sending any value to the >>>>>>> parameter, which is mentioned Contains caluse. >>>>>>> It's not accepting any null values. Should i use Dynamic >>>>>>> query? >>>>>>> Can >>>>>>> you >>>>>>> please help me.
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |