passing Table and Column name to stored procedure

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)

passing Table and Column name to stored procedure

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-01-30 : 08:19:56

vince writes "Hi,

As you can see I have taken the parsing algorithm from one of your other posts and use it for my stored proc. The only thing is though I need to know how I can pass in the table and column name I want to update for the stored proc to truly be multi functional.

Create procedure UpdateFacilityAssgmts
( @Array varchar(1000),
@Array2 varchar(1000),
@TableName varchar(100),
@ColumnName varchar(100),
@separator char(1) )
AS
-- Created by graz@sqlteam.com
set nocount on
-- @Array is the array we wish to parse
-- @Separator is the separator charactor such as a comma
declare @separator_position int -- This is used to locate each separator character
declare @separator_position2 int -- This is used to locate each separator character
declare @array_value varchar(1000) -- this holds each array value as it is returned
declare @array_value2 varchar(1000) -- this holds each array value as it is returned

-- For my loop to work I need an extra separator at the end. I always look to the
-- left of the separator character for each array value
set @array = @array + @separator
set @array2 = @array2 + @separator
-- Loop through the string searching for separtor characters
while patindex('%' + @separator + '%' , @array) <> 0
begin

-- patindex matches the a pattern against a string
select @separator_position = patindex('%' + @separator + '%' , @array)
select @array_value = left(@array, @separator_position - 1)

select @separator_position2 = patindex('%' + @separator + '%' , @array2)
select @array_value2 = left(@array2, @separator_position2 - 1)

-- This is where you process the values passed.
-- Replace this select statement with your processing
-- @array_value holds the value of this element of the array

Update @TableName
SET EnterprsFacilitySeqNum = @array_value2
WHERE @ColumnName = @array_value

select Array_Value = @array_value
select Array_Value2 = @array_value2

-- This replaces what we just processed with and empty string
select @array = stuff(@array, 1, @separator_position, '')
select @array2 = stuff(@array2, 1, @separator_position2, '')
end

set nocount off
go"

X002548
Not Just a Number

15586 Posts

Posted - 2003-01-30 : 10:03:02

This is akin to dynamic sql....what will your mother (optimizer) think?

Anyway, I think you have to prepare a Command String for the SQL Statement and Execute it.

Declare strCommand varchar(4000)

Select @strCommand = 'Update ' + @Table...ect

Execute(@strCommand)

Happy Hunting

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources