Solved

Passing a null parameter value to a stored procedure

Posted on 2004-03-22
15
3,919 Views
Last Modified: 2009-01-21
Hi,

I'm using a TSQLStoredProc to execute a procedure (that I didn't write, and can't modify). I need to be able to pass the first parameter as a null value, because I need the procedure to use the default value for that parameter.

If I just create the parameter, without assigning it a value I get:

No value for parameter "param_name"

when I execute the procedure.

My question is, how do I execute the procedure without getting this error?

Regards,

Bill
0
Comment
Question by:wipnav
  • 8
  • 5
  • 2
15 Comments
 
LVL 27

Accepted Solution

by:
kretzschmar earned 250 total points
ID: 10650846
try this at runtime

  ProcName.Params[0].Clear; //for ado

  ProcName.Parambyname('NameHere').Clear; //for bde for ex.

what do u use ado/bde/other?

meikl ;-)

0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 10650877
o, dbexpress i guess (TSQLStoredProc)
-> use the second one
0
 
LVL 1

Author Comment

by:wipnav
ID: 10651477
I had already tried that. It doesn't help.
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 10651651
maybe the parameter in stored procedure is defined as not null?
(place the declaration here)

do you have adjusted the param- and datatype?

meikl ;-)
0
 
LVL 1

Author Comment

by:wipnav
ID: 10652006
The stored procedure parameter is defined as:

@param_name param_type = NULL

Which means that a parameter doesn't have to be provided.
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 10652162
yep, the param can be null
usual it should work with the clear-method

you could also try

ParamByName('PData').Value := Null;

will do a test tomorrow

meikl ;-)
0
 
LVL 1

Author Comment

by:wipnav
ID: 10652379
"Null" isn't a declaired identifier.
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 5

Expert Comment

by:delphized
ID: 10652457
err

read this sample taken from the mssql book online:
-------------------------------------------------------
Examples
This example shows the get_sales_for_title procedure with special handling for cases when the stored procedure is executed without a value for the @title parameter:

CREATE PROCEDURE get_sales_for_title
@title varchar(80) = NULL,  -- NULL default value
@ytd_sales int OUTPUT
AS  

-- Validate the @title parameter.
IF @title IS NULL
BEGIN
   PRINT 'ERROR: You must specify a title value.'
   RETURN
END

-- Get the sales for the specified title and
-- assign it to the output parameter.
SELECT @ytd_sales = ytd_sales
FROM titles
WHERE title = @title

RETURN
GO

-----------------------------------------

also in your case the parameter has a default value of null, but it has to be valorized.
0
 
LVL 5

Expert Comment

by:delphized
ID: 10652469
you can find the full text in "stored procedures, parameters"
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 10652480
null is a function defined in variants.pas
(unit should be included in the uses-clause)

btw. what delphi version do u use?
(the variants-unit was introduced with d6, i guess)

from helpfile:

function Null: Variant;

Description

Use Null to obtain a Null variant that can indicate unknown or missing data. Null Variants can be assigned to variant variables in an application that must contain a null value. Assigning Null to a variant variable does not cause an error, and Null can be returned from any function with a variant return value.

Assigning Null to a variable of any type other than Variant causes either a compile-time error or raises an EVariantTypeMismatch exception. For example, in the following code the assignment of v, the Null variant, to variant q is successful; whereas the conversion of variant v, which is now Null, to the integer return type of the Test function, raises an exception.

meikl ;-)
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 10652549
yep, delphized (just not seen your posts until refreshing),
it could be also raised within this stored procedure

meikl ;-)
0
 
LVL 1

Author Comment

by:wipnav
ID: 10653268
FYI I am using Delphi 7.

The procedure specifies nine parameters, all with defaults. If I don't create any parameters before I call the procedure it works fine. I want to specify some of the parameters, and leave the rest to the default values. The problem is that I'm using CreateParam, and it seems to only create the parameters in the order that they are in the procedure. That causes my problem, because I can't provide a valid value for the first parameter in the list. What I really need is a way to create parameters for only the parameters that I want to override the default value.
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 10655963
well,

did a test, and have no problem to provide NULL-Values
in the parameters using the clear-method.

>The problem is that I'm using CreateParam, and it seems
>to only create the parameters in the order that they are in
>the procedure

you have to create all possible parameters,
because the parameters are forwarded
in the order as they are declared in the storedproc

meikl ;-)
0
 
LVL 1

Author Comment

by:wipnav
ID: 10657091
Calling clear did work. The reason it didn't work for me yesterday is that I had a parameter type of ftUnknown. That's what it didn't like. I changed it to ftString (even though it isn't a string) and called Clear, and it worked.

Thank you,

Bill
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 10657328
glad you got it sorted :-))

happy coding

meikl ;-)
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now