Solved

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

Posted on 2008-06-09
11
243 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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
ASP Classic - Load test 2 45
format nvarchar field as mm/dd/yyyy 4 61
Error handling in PHP 5 58
wordpress display sub menu only when click 12 23
Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this.Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it is …
These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
This video teaches viewers how to create their own website using cPanel and Wordpress. Tutorial walks users through how to set up their own domain name from tools like Domain Registrar, Hosting Account, and Wordpress. More specifically, the order in…
Wufoo.com provides powerful tools for surveying targeted groups, and utilizing data from completed surveys to find trends, discover areas of demand or customer expectation, and make business decisions on products or services.

920 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now