Passing a null parameter value to a stored procedure

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
LVL 1
wipnavAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
kretzschmarConnect With a Mentor Commented:
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
 
kretzschmarCommented:
o, dbexpress i guess (TSQLStoredProc)
-> use the second one
0
 
wipnavAuthor Commented:
I had already tried that. It doesn't help.
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
kretzschmarCommented:
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
 
wipnavAuthor Commented:
The stored procedure parameter is defined as:

@param_name param_type = NULL

Which means that a parameter doesn't have to be provided.
0
 
kretzschmarCommented:
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
 
wipnavAuthor Commented:
"Null" isn't a declaired identifier.
0
 
delphizedCommented:
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
 
delphizedCommented:
you can find the full text in "stored procedures, parameters"
0
 
kretzschmarCommented:
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
 
kretzschmarCommented:
yep, delphized (just not seen your posts until refreshing),
it could be also raised within this stored procedure

meikl ;-)
0
 
wipnavAuthor Commented:
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
 
kretzschmarCommented:
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
 
wipnavAuthor Commented:
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
 
kretzschmarCommented:
glad you got it sorted :-))

happy coding

meikl ;-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.