CREATE PROCEDURE GetSaleTop
@TopCount INT = NULL
AS
DECLARE @SQLString NVARCHAR(1000)
DECLARE @SaleOrBuy NVARCHAR(4)
SET @SaleOrBuy = N'Sale'
SELECT * FROM PRT WHERE SaleOrBuy = @SaleOrBuy
@TopCount INT = NULL
AS
DECLARE @SQLString NVARCHAR(1000)
DECLARE @SaleOrBuy NVARCHAR(4)
SET @SaleOrBuy = N'Sale'
SELECT * FROM PRT WHERE SaleOrBuy = @SaleOrBuy
@TopCount INT = NULL
AS
DECLARE @SQLString VARCHAR(1000)
DECLARE @SaleOrBuy VARCHAR(4)
SET @SaleOrBuy = N'Sale'
SELECT * FROM PRT WHERE SaleOrBuy = @SaleOrBuy
应该是:
CREATE PROCEDURE GetSaleTop
@TopCount INT = NULL
AS
DECLARE @SQLString NVARCHAR(1000)
DECLARE @SaleOrBuy NVARCHAR(4)
SET @SaleOrBuy = N'Sale'
SET @SQLString ='SELECT '+ CAST(@TopCount, NVARCHAR(32) ) + ' FROM PRT WHERE SaleOrBuy = '+ @SaleOrBuy
exec (@SQLString)我想实现: 取出 PRT 表中 字段 SaleOrBuy = 'Sale' 的前面若干行(@TopCount) 数据结果包错: 运行dbo."GetSaleTop" ( @TopCount = 8 ).列名 'Sale' 无效。
@TopCount INT = 0 --默认值0显示全部记录
AS
DECLARE @SQLString NVARCHAR(1000)
DECLARE @SaleOrBuy NVARCHAR(4)
SET @SaleOrBuy = N'Sale'
set rowcount @TopCount
SET @SQLString ='SELECT * FROM PRT WHERE SaleOrBuy = '''+@SaleOrBuy +'''' exec (@SQLString)
set rowcount 0
begin
set @TopCount =10 --默认取10条或者其它,不能为NULL
endSET @SQLString ='SELECT TOP '+ CAST(@TopCount, NVARCHAR(32) ) + ' * FROM PRT WHERE SaleOrBuy = '+ @SaleOrBuy
@TopCount INT = 0 --默认值0显示全部记录
AS
DECLARE @SQLString NVARCHAR(1000)
DECLARE @SaleOrBuy NVARCHAR(4)
SET @SaleOrBuy = N'Sale'
set rowcount @TopCount
SET @SQLString ='SELECT * FROM PRT WHERE SaleOrBuy = '+char(39)+rtrim(@SaleOrBuy) +char(39) exec (@SQLString)
set rowcount 0
@TopCount INT = NULL
AS
DECLARE @SQLString NVARCHAR(1000)
DECLARE @SaleOrBuy NVARCHAR(4)
SET @SaleOrBuy = 'Sale'
SET @SQLString ='SELECT top '+ CAST(@TopCount as varchar(3) ) + ' * FROM PRT WHERE SaleOrBuy = '''+ @SaleOrBuy +''''
exec (@SQLString)
@TopCount INT = NULL
AS
DECLARE @SQLString NVARCHAR(1000)
DECLARE @SaleOrBuy NVARCHAR(4)
SET @SaleOrBuy = 'Sale'
if @TopCount is not null
SET @SQLString ='SELECT top '+ CAST(@TopCount as varchar(3) ) + ' * FROM PRT WHERE SaleOrBuy = '''+ @SaleOrBuy +''''
else
SET @SQLString ='SELECT * FROM PRT WHERE SaleOrBuy = '''+ @SaleOrBuy +''''
exec (@SQLString)
@TopCount INT = NULLAS
DECLARE @SQLString NVARCHAR(1000)
DECLARE @SaleOrBuy NVARCHAR(4)
SET @SaleOrBuy = N'Sale'
SET @SQLString ='SELECT top '+@TopCount +'* FROM PRT WHERE SaleOrBuy = '''+ @SaleOrBuy +''''exec (@SQLString)