Sending NULL value to parameter of Stored Procedure

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Siva - 06 Nov 2006 14:51 GMT

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.

Reply to this Message

Hilary Cotter - 06 Nov 2006 17:37 GMT

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

Signature

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

>I am getting the following error, when i am trying to pass the NULL value
>to
[quoted text clipped - 12 lines]
> you
> please help me.

Reply to this Message

Siva - 07 Nov 2006 09:31 GMT

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

> You can do an existence check in your header, ie
>
[quoted text clipped - 29 lines]
> > you
> > please help me.

Reply to this Message

Hilary Cotter - 07 Nov 2006 12:25 GMT

I would avoid dynamic queries as much as possible. Can you post your proc
here, or send it to me offline?

Signature

> Hi Hilary,
>
[quoted text clipped - 50 lines]
>> > you
>> > please help me.

Reply to this Message

Simon Sabin - 08 Nov 2006 00:39 GMT

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?
[quoted text clipped - 74 lines]
>>>> you
>>>> please help me

Reply to this Message

Hilary Cotter - 08 Nov 2006 00:58 GMT

Hi Simon, thanks for the links. Erland's arguments are precisely why I am
trying to encourage the OP NOT to use dynamic sql.

Signature

> Hello Hilary,
>
[quoted text clipped - 88 lines]
>>>>> you
>>>>> please help me.

Reply to this Message

Hilary Cotter - 08 Nov 2006 00:59 GMT

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.

Signature

> Hello Hilary,
>
[quoted text clipped - 88 lines]
>>>>> you
>>>>> please help me.

Reply to this Message

Siva - 08 Nov 2006 11:54 GMT

Hi Hilary,
I have sent the sample SP to your mail id.
Please let me know your comments.
Thanks,
Siva

> 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
[quoted text clipped - 92 lines]
> >>>>> you
> >>>>> please help me.

Reply to this Message

Hilary Cotter - 08 Nov 2006 15:30 GMT

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.

Signature

> Hi Hilary,
> I have sent the sample SP to your mail id.
[quoted text clipped - 101 lines]
>> >>>>> you
>> >>>>> please help me.

Reply to this Message

Simon Sabin - 14 Nov 2006 02:32 GMT

Hello Siva,

Can you post the SP

Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons

> Hi Hilary,
> I have sent the sample SP to your mail id.
[quoted text clipped - 111 lines]
>>>>>>> you
>>>>>>> please help me.

Reply to this Message

Siva - 08 Nov 2006 11:55 GMT

Hi Simon,
Thanks for your response. I have gone through the links. It's very
usefull.
Thanks,
Siva

> Hello Hilary,
>
[quoted text clipped - 86 lines]
> >>>> you
> >>>> please help me.

Reply to this Message