Solved

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

Posted on 2008-06-09
11
240 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

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 …
      Install BugTracker on Windows 2008 server Step 1:  Install windows 2008 server 32 bit OS and configure IIS. Step 2:  Install SQL server ( SQL server 2005 or SQL server 2005 Express edition. The installer for 2008  version isn’t very f…
Use Wufoo, an online form creation tool, to make powerful forms. Learn how to choose which pages of your form are visible to your users based on their inputs. The page rules feature provides you with an opportunity to create if:then statements for y…
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.

743 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

12 Experts available now in Live!

Get 1:1 Help Now