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

SQL Server Development (2000)

Sproc problem

Author

Topic

Gsuttie
Starting Member

14 Posts

Posted - 2002-03-04 : 09:00:49

All my lookup tables have 3 columns
column 1 = int (identity)
column 2 = varchar(255)
column 3 = int

I need a dynamic sproc which will let me insert into these tables - so far ive got this but im stuck with how to update the second column.

CREATE PROCEDURE usp_update_lookup_table
( @lookup_tablename varchar(200),
@lookup_desc varchar(100),
@lookup_active int

)
AS

Declare @SQL VarChar(1000)

SELECT @SQL = 'UPDATE '
SELECT @SQL = @SQL + @lookup_tablename
SELECT @SQL = @SQL + ' SET '
SELECT @SQL = @SQL + @lookup_desc
SELECT @SQL = @SQL + '= @lookup_desc'

print (@SQL)
--Exec ( @SQL)

RETURN
@@ERROR
GO

Greg

davidpardoe
Constraint Violating Yak Guru

324 Posts

Posted - 2002-03-04 : 09:39:09

Are the name of columns 2 and 3 always the same? If they are and they are called lookup_desc and lookup_active then use the following in your sp.

SET @SQL = 'UPDATE ' + @lookup_tablename + ' SET '
SET @SQL = @SQL + 'lookup_desc='''+@lookup_desc+''','
SET @SQL = @SQL + 'lookup_active='+@lookup_active

Note the need to embed single quotes within the @SQL variable using two single quotes next to each other - for the 2nd column. This will put single quotes around the value in the @lookup_desc variable passed to the sp. Not necessary for the 3rd column as it is datatype int and hence does not need single quotes.

Post again if unclear.

============
The Dabbler!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources