Question

SQL Server2008 存储过程传入表名称和列名称，如何在语句中使用表明引用列名?

Answer 1

CREATE PROCEDURE [dbo].[prc_loc_pl]
@today Datetime AS
BEGIN

DECLARE @strategyid INT,@starttime VARCHAR(20),@endtime VARCHAR(20),@intervaltime INT,@locperiod INT,@users VARCHAR(Max)
DECLARE @weekday INT,@times INT

if @today is NULL Set @today=getdate()

Set @weekday = (select datepart(weekday,getdate()))--取得时间对应的当前星期几

DECLARE tablename CURSOR FOR SELECT id,starttime,endtime,intervaltime,locperiod,users FROM loc_st WHERE isopen=1 --读取已启用的策略
OPEN tablename
FETCH NEXT FROM tablename INTO @strategyid,@starttime,@endtime,@intervaltime,@locperiod,@users WHILE @@FETCH_STATUS != -1 --循环读取策略表
BEGIN
if @locperiod=5 AND (@weekday=7 OR @weekday=1)--五工作日
begin
SET @times=0
end
else if @locperiod=6 AND (@weekday=1) --六工作日
begin
SET @times=0
end
else
begin
SET @times=(SELECT DATEDIFF(n,@starttime,@endtime)/@intervaltime)
end

INSERT INTO [loc_pl]([loginname],[tday],[times])
SELECT LOGINNAME,@today,@times FROM loc_userst WHERE strategy=@strategyid
--Print @today
FETCH NEXT FROM tablename INTO @strategyid,@starttime,@endtime,@intervaltime,@locperiod,@users
END
CLOSE tablename
DEALLOCATE tablename
END

GO

追问

我主要是用在多表操作的where子句中，需要用表名.列名，这两个有都是作为输入参数传入的，该怎么写？

追答

。。

Answer 2

EXEC 'select ' + @列名 + 'From ' + @表名称

Answer 3

这个要用 动态 SQL 来处理了。