Solved

Passing a null parameter value to a stored procedure

Posted on 2004-03-22
15
3,931 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Suggested Solutions

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
Creating an auto free TStringList The TStringList is a basic and frequently used object in Delphi. On many occasions, you may want to create a temporary list, process some items in the list and be done with the list. In such cases, you have to…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

821 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