Solved

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

Posted on 2009-05-07
3
2,027 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 35

Expert Comment

by:YZlat
ID: 24326334
try loosing the parenthesis
0
 
LVL 143

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

Create CentOS 7 Newton Packstack Running Keystone

A bug was filed against RDO for the installation of Keystone v3. This guide is designed to walk you through the configuration for using Keystone v3 with Packstack. You will accomplish this using various repos and the Answers file.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

630 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