Solved

Stored Procedure - Error: 102, Severity: 15, State: 1

Posted on 2009-05-07
3
1,936 Views
Last Modified: 2012-05-06
I have the below stored procedure. When I invoke it in "New Query" from within SQL 2005,  supplying it with the correct parameter values, it runs fine  . Unfortunately when I call the same stored procedure from an .aspx I get the following error : "Incorrect syntax near 'sprocSearchPart'. Confused... I turned on SQL Profiler and found out that it goes to execute the proc and then throws this error ..Error: 102, Severity: 15, State: 1.
I did some research and it appears there are several reasons that this could happen. (One solution is to apply a Microsoft hotfix). I have checked my SP over thouroughly and can't figure out where the issue maybe from syntax standpoint. Any suggestions would be greatly appreciated.
USE [QuoteDB]

GO

/****** Object:  StoredProcedure [dbo].[sprocSearchPart]    Script Date: 05/07/2009 10:16:09 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO
 

ALTER PROCEDURE [dbo].[sprocSearchPart] 

(

	@pnpartnumber as varchar(100),

	@pntitle as varchar(100),

	@pndetail as varchar(100)

)
 
 

AS 

if (@pnpartnumber is null)

	BEGIN

	  set @pnpartnumber = ''

    END

if (@pntitle is null)

   BEGIN

    set @pntitle=''

    END

if (@pndetail is null)

   BEGIN

     set @pndetail =''

   END

declare @pnpartnumber_flg int

declare @pntitle_flg int

declare @pndetail_flg int
 

SET @pnpartnumber_flg = 1

SET @pntitle_flg = 1

SET @pndetail_flg = 1
 

if (@pnpartnumber ='')

  BEGIN

     SET @pnpartnumber_flg = 0

  END

if (@pntitle ='')

  BEGIN

     SET @pntitle_flg = 0

  END
 

if (@pndetail ='')

   BEGIN

     SET @pndetail_flg = 0

   END
 

SELECT     pnpartnumber, pntitle, pndetail, pnrevision

FROM         partnoTbl
 

Where ((@pnpartnumber_flg=1 AND pnpartnumber like '%' + @pnpartnumber+ '%') OR (@pnpartnumber_flg=0))AND

      ((@pntitle_flg=1 AND pntitle like '%' +@pntitle+ '%')OR (@pntitle_flg=0)) AND

      ((@pndetail_flg=1 AND pndetail like '%' +@pndetail+ '%')OR (@pndetail_flg=0))

Open in new window

0
Comment
Question by:jazzcatone
  • 2
3 Comments
 
LVL 35

Expert Comment

by:YZlat
Comment Utility
try loosing the parenthesis
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
Comment Utility
2 things:
please add this line as first line in your stored proc (after the AS) :

SET NOCOUNT ON

for the error, the problem in on the asp code.
you probably forgot to specify the sqlcommand.CommandType = CommandType.StoredProc  ...
0
 
LVL 35

Expert Comment

by:YZlat
Comment Utility

USE [QuoteDB]

GO

/****** Object:  StoredProcedure [dbo].[sprocSearchPart]    Script Date: 05/07/2009 10:16:09 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

 

ALTER PROCEDURE [dbo].[sprocSearchPart] 

	@pnpartnumber as varchar(100),

	@pntitle as varchar(100),

	@pndetail as varchar(100)

 

AS 

if (@pnpartnumber is null)

	BEGIN

	  set @pnpartnumber = ''

    END

if (@pntitle is null)

   BEGIN

    set @pntitle=''

    END

if (@pndetail is null)

   BEGIN

     set @pndetail =''

   END

declare @pnpartnumber_flg int

declare @pntitle_flg int

declare @pndetail_flg int

 

SET @pnpartnumber_flg = 1

SET @pntitle_flg = 1

SET @pndetail_flg = 1

 

if (@pnpartnumber ='')

  BEGIN

     SET @pnpartnumber_flg = 0

  END

if (@pntitle ='')

  BEGIN

     SET @pntitle_flg = 0

  END

 

if (@pndetail ='')

   BEGIN

     SET @pndetail_flg = 0

   END

 

SELECT     pnpartnumber, pntitle, pndetail, pnrevision

FROM         partnoTbl

 

Where ((@pnpartnumber_flg=1 AND pnpartnumber like '%' + @pnpartnumber+ '%') OR (@pnpartnumber_flg=0))AND

      ((@pntitle_flg=1 AND pntitle like '%' +@pntitle+ '%')OR (@pntitle_flg=0)) AND

      ((@pndetail_flg=1 AND pndetail like '%' +@pndetail+ '%')OR (@pndetail_flg=0))

Open in new window

0

Featured Post

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

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how the fundamental information of how to create a table.

771 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