Solved

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

Posted on 2009-05-07
3
1,950 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
ID: 24326334
try loosing the parenthesis
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 24326338
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
ID: 24326343

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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

867 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

19 Experts available now in Live!

Get 1:1 Help Now