[Webinar] Streamline your web hosting managementRegister Today

x
  • 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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

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