Solved

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

Posted on 2009-05-07
3
2,007 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

Business Impact of IT Communications

What are the business impacts of how well businesses communicate during an IT incident? Targeting, speed, and transparency all matter. Find out more in this infographic.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

751 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