Generic Update NOT WORKING PLEASE HELP...

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

Transact-SQL (2000)

Generic Update NOT WORKING PLEASE HELP...

Author

Topic

erikkl2000

15 Posts

Posted - 2006-05-12 : 14:29:00

For some reason i can not get this update to work... can someone please rearrange my code so that it works correctly?

Thanks for the help
Erik

=====================================================================
Running [dbo].[Update_Generic] ( @UserName = ERIKKL2444, @GenericTable = [Users], @GenericColumn = Note, @GenericValue = My Note ).

Incorrect syntax near 'Note'.
No rows affected.
(0 row(s) returned)
@RETURN_VALUE = 0
Finished running [dbo].[Update_Generic].
The thread 'local\1d15fe7c-ca6e-47 [52]' (0x304) has exited with code 0 (0x0).
The program '[2968] [SQL] local: local\1d15fe7c-ca6e-47' has exited with code 0 (0x0).

================================================================

ALTER Procedure [dbo].[Update_Generic]

@UserName nVARCHAR (256)
,@GenericTable VARCHAR (256)
,@GenericColumn VARCHAR (200)
,@GenericValue VARCHAR (200)

AS
DECLARE @SQL NVARCHAR(4000)
DECLARE @UserID uniqueidentifier
SELECT @UserID = UserId FROM dbo.aspnet_Users WHERE LOWER(@UserName)= LoweredUserName

IF (@UserID IS NOT NULL AND @GenericColumn IS NOT NULL)
BEGIN
SET @SQL = 'Update ' + @GenericTable + '
SET ' + @GenericColumn + ' = ' + @GenericValue + '
WHERE ' + @GenericTable + '.[UserID] = ''' + LOWER(CAST(@UserID AS VARCHAR(50))) + ''''
EXEC (@SQL)
END

Lopaka
Starting Member

48 Posts

Posted - 2006-05-12 : 14:41:29

Try this:
SET @SQL = 'Update ' + @GenericTable + '
SET ' + @GenericColumn + ' = ''' + @GenericValue + '''
WHERE ' + @GenericTable + '.[UserID] = ''' + LOWER(CAST(@UserID AS VARCHAR(50))) + ''''

Robert R. Barnes

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-05-12 : 15:15:07

@GenericValue = My Note == > @GenericValue = [My Note]

Srinika

Lopaka
Starting Member

48 Posts

Posted - 2006-05-12 : 16:08:09

TRY THIS:

IF OBJECT_ID('tempdb.dbo.#TEMP') IS NOT NULL DROP TABLE #TEMP

CREATE TABLE #TEMP(USERID VARCHAR(15), COL1 VARCHAR(15))

INSERT INTO #TEMP(USERID, COL1) VALUES('AAA', 'AAA')
INSERT INTO #TEMP(USERID, COL1) VALUES('BBB', 'BBB')
INSERT INTO #TEMP(USERID, COL1) VALUES('CCC', 'CCC')
INSERT INTO #TEMP(USERID, COL1) VALUES('DDD', 'DDD')

DECLARE @SQL VARCHAR(500)
DECLARE @GenericTable VARCHAR(15)
DECLARE @GenericColumn VARCHAR(15)
DECLARE @GenericValue VARCHAR(15)
DECLARE @UserID VARCHAR(15)

SET @GenericTable = '#temp'
SET @GenericColumn = 'COL1'
SET @GenericValue = 'CCC DD'
SET @UserID = 'AAA'

SET @SQL = 'Update ' + @GenericTable + '
SET ' + @GenericColumn + ' = ''' + @GenericValue + '''
WHERE ' + @GenericTable + '.[UserID] = ''' + LOWER(CAST(@UserID AS VARCHAR(50))) + ''''

PRINT @SQL

EXECUTE(@SQL)

SELECT * FROM #TEMP

Robert R. Barnes

cmdr_skywalker
Posting Yak Master

159 Posts

Posted - 2006-05-12 : 21:53:33

append the square brackets between [My Note] if [My Note] is a column else a single quote if it is a string 'My Note'.

May the Almighty God bless us all!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources