Solved

Passing a null parameter value to a stored procedure

Posted on 2004-03-22
15
3,938 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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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
 
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The uses clause is one of those things that just tends to grow and grow. Most of the time this is in the main form, as it's from this form that all others are called. If you have a big application (including many forms), the uses clause in the in…
Objective: - This article will help user in how to convert their numeric value become words. How to use 1. You can copy this code in your Unit as function 2. than you can perform your function by type this code The Code   (CODE) The Im…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

830 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