This proc is not working ..urgent

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

This proc is not working ..urgent

Author

Topic

aravindt77
Posting Yak Master

120 Posts

Posted - 2007-07-05 : 15:27:23

Hi ALL,

Plz see thru this proc

This is not functioning... what to do Plz do help me

ALTER PROC DELPROC
AS
BEGIN

DECLARE @PROCLIST TABLE (
SPCOUNT INT IDENTITY(1,1) NOT NULL ,
SPNAME VARCHAR(255) NOT NULL DEFAULT(''),
STATUS VARCHAR(50) NOT NULL DEFAULT(''))
DECLARE @COUNT INT
DECLARE @PROCNAME VARCHAR(255)
DECLARE @SQL VARCHAR(1000)

SET @COUNT = 0

INSERT INTO @PROCLIST (SPNAME)
SELECT [NAME] FROM DBO.SYSOBJECTS WHERE [NAME] LIKE 'ER_%'

SELECT @COUNT = MAX(SPCOUNT) FROM @PROCLIST

WHILE @COUNT > 0
BEGIN
SELECT @PROCNAME = SPNAME FROM @PROCLIST WHERE SPCOUNT = @COUNT
SET @SQL = 'DROP PROCEDURE ' + @PROCNAME
EXEC @SQL
SET @SQL = 'GO'
EXEC @SQL
SET @COUNT = @COUNT - 1
END
RETURN 0
END

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-05 : 15:35:11

What error do you get?

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-05 : 15:37:04

Replace the "EXEC @SQL" with "PRINT @SQL" and you will notice what is wrong.
Maybe you have spaces in the SPROC name?

Peter Larsson
Helsingborg, Sweden

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2007-07-05 : 15:38:53

With no information on the actual problem, I'd say it's not working because the OP is only selecting SPNAME into PROCLIST, but is trying to get MAX(SPCOUNT), none of which have any value other than NULL.

Haywood
Posting Yak Master

221 Posts

Posted - 2007-07-05 : 15:40:07

FWIW, EXEC also needs to be called as such: EXEC (@SQL) and not like EXEC @SQL....

To see the error would defenitly be helpful though.

aravindt77
Posting Yak Master

120 Posts

Posted - 2007-07-05 : 22:56:23

Hi,

I m getting an error like this

"Could not find stored procedure 'DROP PROCEDURE ER_SP_Get_JobsonWhatCountsLists'."
.....
.....
so on up to no of PROCs listed

aravindt77
Posting Yak Master

120 Posts

Posted - 2007-07-05 : 22:59:00

Hi,

Thanks Haywood , you have made it.. it works when the statement given like this

EXEC (@SQL)

Thankyou so much for all my friends to make an effort in resolving my problem..
Once again I Thank all of you

Regards

Aravind

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources