Solved

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

Posted on 2008-06-09
11
250 Views
Last Modified: 2008-06-18
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
Comment
Question by:ipjyo
  • 5
  • 4
  • 2
11 Comments
 
LVL 22

Expert Comment

by:neeraj523
ID: 21747999
Hello

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

neeraj523
0
 

Author Comment

by:ipjyo
ID: 21748436
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
 
LVL 22

Expert Comment

by:neeraj523
ID: 21748537
Try this

dim xmlText = vbNULL
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 15

Expert Comment

by:dosth
ID: 21748933
dim xmlText
xmlText = NULL
0
 

Author Comment

by:ipjyo
ID: 21751082
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
 
LVL 15

Expert Comment

by:dosth
ID: 21751195
.Parameters.Append(cmdref.CreateParameter("@XMLText",adLongVarChar,adParamInput,255,xmlText2))
0
 

Author Comment

by:ipjyo
ID: 21751848
It is not throwing any error.
But it is not doing the action that is in the stored procedure.

thanks
0
 
LVL 15

Accepted Solution

by:
dosth earned 500 total points
ID: 21751928
post the full sp

if @XMLText is null return what that means
0
 

Author Comment

by:ipjyo
ID: 21752015
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
 

Author Comment

by:ipjyo
ID: 21752077
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
 
LVL 15

Expert Comment

by:dosth
ID: 21819534
thanks
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

New Relic: Our company recently started researching several products to figure out what were the best ways for us to increase our web page speed and to quickly identify performance problems that we may be having. One of the products we evaluated wa…
It’s a strangely common occurrence that when you send someone their login details for a system, they can’t get in. This article will help you understand why it happens, and what you can do about it.
Use Wufoo, an online form creation tool, to make powerful forms. Learn how to selectively show certain fields based on user input using rules to gather relevant information and data from your forms. The rules feature provides you with an opportunity…
Learn how to set-up custom confirmation messages to users who complete your Wufoo form. Include inputs from fields in your form, webpage redirects, and more with Wufoo’s confirmation options.

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