Solved

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

Posted on 2009-05-07
3
1,958 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
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 extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

810 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