动态SQL赋值
CREATE proc sp_ksxs1
@JGBZ NVARCHAR(50),
@column1 nVARCHAR(50)
as
declare @num numeric(3,2),@sql nvarchar(500)
--SET @sql = N'select ' + @column1 + ' from ks where jgbz=@JGBZ'
--exec sp_executesql @sql, N'@JGBZ NVARCHAR(50)',@JGBZ
set @sql = 'select '+ @column1 +' from ks where jgbz='''+ @JGBZ +''''
Exec(@sql)
--select @num
--select XH,PS,(PS*@num)'得分 from xxjg where jggc is not null
--如何让select使用@num？@num为exec（@sql）执行结果
GO
--------------------------------------
执行：
sp_ksxs1'P+ET','srjy'
你用的可能是2000，表变量只有2005才支持。你换成临时表即可。
DROP TABLE dbo.tab
go
CREATE TABLE [dbo].[tab]
(
[id] [int] NOT NULL IDENTITY(1, 1),
[title] [nvarchar] (50),
[name] [nvarchar] (20),
[time] [datetime]
)
GO
INSERT tab(name) VALUES('test')
go
ALTER proc sp_ksxs1
@JGBZ NVARCHAR(50),
@column1 nVARCHAR(50)
as
declare @num numeric(3,2),@sql nvarchar(500)
IF object_id('tempdb..#temp', 'u') IS NOT NULL
DROP TABLE #temp
CREATE TABLE #temp(COLUMN1 VARCHAR(10))
set @sql = 'select '+ QUOTENAME(@column1) +' from tab where name='''+ @JGBZ +''''
INSERT #temp(COLUMN1)
Exec(@sql)
select TOP(1) @num = COLUMN1 FROM #temp
DROP TABLE #temp
SELECT @num
GO
--结果
EXEC sp_ksxs1 @JGBZ='test',@column1='id'
