Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3525
  • Last Modified:

"Could not find stored procedure" When Running Stored Procedure

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?
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

Open in new window

0
advlgx
Asked:
advlgx
1 Solution
 
knightEknightCommented:
try using parens:

exec(@sql)
0
 
chapmandewCommented:
use sp_executesql instead...in case you ever need to pass variables to it....also, it does a better job of caching the execution plan.

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'
execute sp_executesql @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
0
 
advlgxAuthor Commented:
Thanks
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now