Solved

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

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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
I have a large data set and a SSIS package. How can I load this file in multi threading?
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
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

821 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