Re: Sending NULL value to parameter of Stored Procedure

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@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:2C1EF77D-1AD6-4A70-A053-9456C08CD2AC@xxxxxxxxxxxxxxxx

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@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:A987EBE5-F578-420E-9A75-220B280B01E1@xxxxxxxxxxxxxxxx

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.


.

Relevant Pages

  • Re: DENY table SELECT, GRANT stored proc EXEC, but EXECUTE a string
    ... To be able to use dynamic SQL, the user must have direct SELECT permissions ... Vyas, MVP (SQL Server) ... > were using static statements in our stored procs instead of EXECUTE, ...
    (microsoft.public.sqlserver.security)
  • Re: Confused about proc vs. dynamic SQL vs LINQ
    ... In the case of SQL Server, you might get just as fast ... performance from dynamic sql (sql that you generate on the client and issue ... as opposed to executing the stored procedure with parameters) ... As of Sql Server 7, ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Flattening Parent Child, an issue, please help
    ... Execute the SQL string. ... It's a plain applicaiton of dynamic SQL, and the newsgroups for SQL Server ...
    (comp.databases.ms-sqlserver)
  • Re: Help me convince the dev manager. Please.
    ... SQL - not just in the fact that it's not precompiled, ... > dev managers insists that there must be another method out there to handle ... firmly believe that dynamic SQL is the best ... > to handle optional parameters. ...
    (microsoft.public.sqlserver.programming)
  • Re: Sending NULL value to parameter of Stored Procedure
    ... Looking for a SQL Server replication book? ... With that number of options dynamic SQL is the best option, ... "Hilary Cotter" wrote: ...
    (microsoft.public.sqlserver.fulltext)