quick help on stored proc - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

quick help on stored proc

Author

Topic

vasu4us
Posting Yak Master

102 Posts

Posted - 2009-10-01 : 17:13:55

I have this below script thats running fine when i run it manually.
when i try to create a stored proc of the same query its erroring out.
The script is to change all the spaces in any of the columns of the 'TABLENAME' parameter should change to NULL.

any help appriciated - thanks

--TABLE
CREATE TABLE [dbo].[proc_test](
[a] [nchar](10) NULL,
[b] [nchar](10) NULL
)

--DATA
INSERT INTO [AdventureWorks].[dbo].[proc_test]
([a]
,[b])
VALUES
(1,'a')
GO
INSERT INTO [AdventureWorks].[dbo].[proc_test]
([a]
,[b])
VALUES
(2,' ')

--WORKING SCRIPT
DECLARE @COLNAME VARCHAR(8000)

DECLARE DB_CURSOR CURSOR FOR
SELECT NAME FROM sys.all_columns WHERE [OBJECT_ID] = object_id('TABLENAME') and is_nullable = 1

OPEN DB_CURSOR
FETCH NEXT FROM DB_CURSOR INTO @COLNAME

WHILE @@FETCH_STATUS = 0
BEGIN

EXEC('UPDATE TABLENAME SET ' + @COLNAME + ' = NULL WHERE LTRIM(RTRIM(' + @COLNAME + ')) = ''''')

FETCH NEXT FROM DB_CURSOR into @COLNAME
END

CLOSE DB_CURSOR
DEALLOCATE DB_CURSOR

--STORED PROC
CREATE PROCEDURE [dbo].[TEST_ABC]
@TAB VARCHAR(100) AS
BEGIN
SET NOCOUNT ON
DECLARE @COLNAME VARCHAR(8000)

DECLARE DB_CURSOR CURSOR FOR
SELECT NAME FROM sys.all_columns WHERE [OBJECT_ID] = object_id(@TAB) and is_nullable = 1

OPEN DB_CURSOR
FETCH NEXT FROM DB_CURSOR INTO @COLNAME

WHILE @@FETCH_STATUS = 0
BEGIN

EXEC('UPDATE' +@TAB+ 'SET ' + @COLNAME + ' = NULL WHERE LTRIM(RTRIM(' + @COLNAME + ')) = ''''')

FETCH NEXT FROM DB_CURSOR into @COLNAME
END

CLOSE DB_CURSOR
DEALLOCATE DB_CURSOR

END

--ERROR
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '='.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '='.

vasu4us
Posting Yak Master

102 Posts

Posted - 2009-10-01 : 17:55:35

GOT IT THANKS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources