Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2009-05-07
3
Medium Priority
?
2,077 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 2000 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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

927 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