We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Passing a null parameter value to a stored procedure

wipnav
wipnav asked
on
Medium Priority
4,296 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
Comment
Watch Question

Top Expert 2004
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
Top Expert 2004

Commented:
o, dbexpress i guess (TSQLStoredProc)
-> use the second one

Author

Commented:
I had already tried that. It doesn't help.
Top Expert 2004

Commented:
maybe the parameter in stored procedure is defined as not null?
(place the declaration here)

do you have adjusted the param- and datatype?

meikl ;-)

Author

Commented:
The stored procedure parameter is defined as:

@param_name param_type = NULL

Which means that a parameter doesn't have to be provided.
Top Expert 2004

Commented:
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 ;-)

Author

Commented:
"Null" isn't a declaired identifier.
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.
you can find the full text in "stored procedures, parameters"
Top Expert 2004

Commented:
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 ;-)
Top Expert 2004

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

meikl ;-)

Author

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.
Top Expert 2004

Commented:
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 ;-)

Author

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
Top Expert 2004

Commented:
glad you got it sorted :-))

happy coding

meikl ;-)
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.