Advertisement

06.10.2008 at 11:29AM PDT, ID: 23473387
[x]
Attachment Details

"Could not find stored procedure" When Running Stored Procedure

Asked by advlgx in MS SQL Server, SQL Query Syntax, SQL Server 2005

Tags: SQL

When I create the stored procedure below, then run it by supplying the  2 parameters, I get the following Errors:
Msg 2812, Level 16, State 62, Procedure CEM_BulkOpsInsert, Line 15
Could not find stored procedure 'Delete From Case_IUD WHERE LEN(ID) > 4'.
Msg 2812, Level 16, State 62, Procedure CEM_BulkOpsInsert, Line 17
Could not find stored procedure ''.

If I hard code the second parameter @table and take out the dynamic SQL it works fine. What is wrong?Start Free Trial
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
USE [xxx]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[CEM_BulkOpsInsert] 
@tablename varchar(50),
@table varchar(100)
AS
BEGIN
 
	SET NOCOUNT ON;
 
Declare @error nvarchar(255)
Declare @subject1 varchar(200)
Declare @sql varchar(255)
 
exec SF_BulkOps 'Insert',@tablename,@table
Set @sql = 'Delete From ' + @table + ' WHERE LEN(ID) > 4'
exec @sql
Set @sql = 'select ' + @error + ' = COUNT(ID) From ' + @table + ' WHERE LEN(Error) > 4'
exec @sql
IF @error > 0
BEGIN
Set @subject1 = 'Errors Exist In ' + @table + ' For ' + @tablename
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Host',
    @recipients = 'host@xxx.com',
    @body = '',
    @subject = @subject1;
END
 
END
[+][-]06.10.2008 at 11:37AM PDT, ID: 21753771

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]06.10.2008 at 11:42AM PDT, ID: 21753810

View this solution now by starting your 7-day free trial. Setting up your free trial is quick, easy, and secure. We will return you to this solution, unlocked, when you're done.

 

About this solution

Zones: MS SQL Server, SQL Query Syntax, SQL Server 2005
Tags: SQL
Sign Up Now!
Solution Provided By: chapmandew
Participating Experts: 2
Solution Grade: A
 
 
 
Loading Advertisement...
20080716-EE-VQP-32 / EE_QW_2_20070628