• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 259
  • Last Modified:

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

0
ipjyo
Asked:
ipjyo
  • 5
  • 4
  • 2
1 Solution
 
neeraj523Commented:
Hello

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

neeraj523
0
 
ipjyoAuthor Commented:
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

0
 
neeraj523Commented:
Try this

dim xmlText = vbNULL
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
dosthCommented:
dim xmlText
xmlText = NULL
0
 
ipjyoAuthor Commented:
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
0
 
dosthCommented:
.Parameters.Append(cmdref.CreateParameter("@XMLText",adLongVarChar,adParamInput,255,xmlText2))
0
 
ipjyoAuthor Commented:
It is not throwing any error.
But it is not doing the action that is in the stored procedure.

thanks
0
 
dosthCommented:
post the full sp

if @XMLText is null return what that means
0
 
ipjyoAuthor Commented:
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

0
 
ipjyoAuthor Commented:
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

0
 
dosthCommented:
thanks
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.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

  • 5
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now