Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 257
  • 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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