asked on

How do I pass a null value to a stored procedure?

I have a stored proc as attached.
How do I pass a parameter for XMLText?
I want to validate it to null. Basically I want that stored proc will read XMLText as null.
what should I do in ASP to pass that parameter?

Please help me.

Thanks a lot

ALTER PROC [dbo].[spCSW_UpdateFocusISINList]
	@FocusISINGroupCd	varchar(7)	
,	@ActionFlag		char(1)		
,	@XMLText		text	
,	@LoginDMSysId		varchar(40)	= null
,	@RefreshFlag		char(1)		= 'N'
as
set nocount on
 
declare @SMDBFlagIn	char(1)
,	@S3dFlagIn	char(1)
 
if @LoginDMSysId is null select @LoginDMSysId = current_user
 
if @RefreshFlag = 'Y' and @ActionFlag = 'A'
	delete tb_FOcusISINList where FocusISINGroupCd = @FocusISINGroupCd
 
if @XMLText is null return

Open in new window

neeraj523

Hello

You can pass it as VBNULL or NULL itself if you executeing stored procedure using con.execute comman..

neeraj523

ipjyo

ASKER

Hi Neeraj

when I am trying the following

dim xmlText = null
.Parameters.Append(cmd.CreateParameter("@XMLText",adLongVarChar,adParamInput,len(xmlText),xmlText))

it is giving an error as
"parameter is improperly defined or insufficient information."
actually I think I am going wrong with
dim xmlText=null and len(xmlText)
Could you please correct the above syntax? It is very urgent for me.
Thanks a lot for your help

neeraj523

Try this

dim xmlText = vbNULL

dosth

dim xmlText
xmlText = NULL

ipjyo

ASKER

can you please tell me what should i specify for length of xmlText?
If I try the following
dim xmlText2= null
.Parameters.Append(cmdref.CreateParameter("@XMLText",adLongVarChar,adParamInput,len(xmlText2),xmlText2))

it is giving type mismatch error.

please help me
thanks

dosth

.Parameters.Append(cmdref.CreateParameter("@XMLText",adLongVarChar,adParamInput,255,xmlText2))

ipjyo

ASKER

It is not throwing any error.
But it is not doing the action that is in the stored procedure.

thanks

ASKER CERTIFIED SOLUTION

dosth

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

ipjyo

ASKER

I attached the full SP.

Thanks for your response.

USE [PostTrade]
GO
/****** Object:  StoredProcedure [dbo].[spCSW_UpdateFocusISINList]    Script Date: 05/23/2008 15:34:46 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROC [dbo].[spCSW_UpdateFocusISINList]
	@FocusISINGroupCd	varchar(7)	
,	@ActionFlag		char(1)		
,	@XMLText		text	
,	@LoginDMSysId		varchar(40)	= null
,	@RefreshFlag		char(1)		= 'N'
as
set nocount on
 
declare @SMDBFlagIn	char(1)
,	@S3dFlagIn	char(1)
 
if @LoginDMSysId is null select @LoginDMSysId = current_user
 
if @RefreshFlag = 'Y' and @ActionFlag = 'A'
	delete tb_FOcusISINList where FocusISINGroupCd = @FocusISINGroupCd
 
if @XMLText is null return
 
select	@SMDBFlagIn	= substring(ValidateMappingCd,1,1)
,	@S3dFlagIn	= substring(ValidateMappingCd,2,1)
from	tb_FocusISINGroup (nolock)
where	FocusISINGroupCd = @FocusISINGroupCd
 
 
declare @ISINXMLdoc int
declare @ISINList table (SecISINNo varchar(30))
 
EXEC sp_xml_preparedocument @ISINXMLdoc OUTPUT, @XMLText
 
	insert	@ISINList 
	select	SecISINNo
	from	openxml (@ISINXMLdoc, '/ISINLIST/ISIN',2)
	with (	SecISINNo	varchar(30))
 
EXEC sp_xml_removedocument @ISINXMLdoc OUTPUT
 
--**********************************
if @ActionFlag = 'A'
begin
	insert tb_FocusISINList (FocusISINGroupcd, SecISINNo, RowInsrtdTS, RowInsrtdbyUserId)
	select	@FocusISINGroupCd, SecISINNo, getdate(), @LoginDMSysId from @ISINList i
	where	SecISINNo not in 
		(select f.SecISINNo from tb_FocusISINList f (nolock) where FocusISINGroupCd = @FocusISINGroupCd and f.SecISINNo = i.SecISINNo)
end
--**********************************
if @ActionFlag = 'D'
begin
	delete	tb_FocusISINList 
	from	tb_FocusISINList  f
	join	@ISINList i
	on	f.SecISINNo = i.SecISINNo
	where	f.FocusISINGroupCd = @FocusISINGroupCd
end

Open in new window

ipjyo

ASKER

I have the following in my ASP page

please help me

call DeleteISINRefresh("A","Y")
 
sub DeleteISINRefresh(action,refr)
 
   dim xmlText2  
   dim cmdref
         
   set conn = Server.CreateObject("ADODB.Connection")
   set cmdref = server.CreateObject("ADODB.Command")
         
      xmlText2 = Null
             
      conn.Open(ConnString)
     with cmdref
      .ActiveConnection = conn
      .CommandType  = 4
      .CommandText = "spCSW_UpdateFocusISINList"
      .Parameters.Append(cmdref.CreateParameter("@FocusISINGroupCd", adVarChar, adParamInput, 7, isinfilter))
      .Parameters.Append(cmdref.CreateParameter("@ActionFlag",adChar,adParamInput, 1,action))
      .Parameters.Append(cmdref.CreateParameter("@XMLText",adLongVarChar,adParamInput,255,xmlText2))
      .Parameters.Append(cmdref.CreateParameter("@RefreshFlag",adChar,adParamInput, 1, refr))
     end with 
     
     on error resume next
     cmdref.Execute()
     If cmdref.ActiveConnection.Errors.Count > 0 Then
        Response.Write("An error occured")
     End If
                                                            
     set cmdref = nothing 
     conn.Close
     set conn = nothing
    
End sub

Open in new window

dosth

thanks