1. @EncodeID= 'Select FID from UID where FName ='''+@TableName+''''
--这里怎么返回FID 的值到@EncodeID用于下一句的查询
Set @SQL = 'insert into UApplyShop(FApplyID) values('''+@EncodeID+''') '2.
SELECT
CASE
WHEN @Name='申购单' THEN set @TableName = 'UApplyShop' ,Set @Field ='FApplyID'
WHEN @Name='采购单' THEN set @TableName = 'UStockOrder' ,Set @Field ='FOrdID'
ELSE 'Expensive book!'
end
--set 那里怎么写
--这里怎么返回FID 的值到@EncodeID用于下一句的查询
Set @SQL = 'insert into UApplyShop(FApplyID) values('''+@EncodeID+''') '2.
SELECT
CASE
WHEN @Name='申购单' THEN set @TableName = 'UApplyShop' ,Set @Field ='FApplyID'
WHEN @Name='采购单' THEN set @TableName = 'UStockOrder' ,Set @Field ='FOrdID'
ELSE 'Expensive book!'
end
--set 那里怎么写
Set @SQL = 'insert into UApplyShop (FApplyID) values (@EncodeID)'
--1、直接这样
Set @SQL = 'insert into UApplyShop(FApplyID) Select FID from UID where FName ='''+@TableName+''''
--2、set @tablename=case @Name='申购单' then 'UApplyShop' when @Name='采购单' then 'UStockOrder' else 'Expensive book!' end
set @Field=case @Name='申购单' then 'FApplyID' when @Name='采购单' then 'FOrdID' else 'Expensive book!' end
if @Name='申购单'
begin
set @TableName = 'UApplyShop'
Set @Field ='FApplyID'
end
else
if @Name='采购单'
begin
set @TableName = 'UStockOrder'
Set @Field ='FOrdID'
end
ELSE
print 'Expensive book!'
--这里怎么返回FID 的值到@EncodeID用于下一句的查询
Set @SQL = 'insert into UApplyShop(FApplyID) +'Select FID from UID where FName ='''+@TableName+''''
--第二个写错了。不好意思。掉了个when.。改下:
set @tablename=case @Name when '申购单' then 'UApplyShop' when '采购单' then 'UStockOrder' else 'Expensive book!' end
set @Field=case @Name when '申购单' then 'FApplyID' when '采购单' then 'FOrdID' else 'Expensive book!' end
Set @SQL = 'insert into UApplyShop(FApplyID) 'Select FID from UID where FName ='''+@TableName+''''
2.
SELECT @TableName = CASE @Name
WHEN '申购单' THEN 'UApplyShop'
WHEN '采购单' THEN 'UStockOrder'
ELSE 'Expensive book!'
END,
@Field = CASE @Name
WHEN '申购单' THEN 'FApplyID'
WHEN '采购单' THEN 'FOrdID'
ELSE 'Expensive book!'
END
Set @SQL = 'insert into UApplyShop(FApplyID) Select FID from UID where FName ='''+@TableName+''''
2.
SELECT @TableName = CASE @Name
WHEN '申购单' THEN 'UApplyShop'
WHEN '采购单' THEN 'UStockOrder'
ELSE 'Expensive book!'
END,
@Field = CASE @Name
WHEN '申购单' THEN 'FApplyID'
WHEN '采购单' THEN 'FOrdID'
ELSE 'Expensive book!'
ENDmodify
@EncodeID= 'Select FID from UID where FName ='''+@TableName+''''比如这里返回 'yy'
我是想在下一句使用Set @SQL =@EncodeID+'0001'
以上第一句还要怎么执行返回
set @s= 'Select @EncodeID=FID from UID where FName ='''+@TableName+''''declare @re varchar(20)
exec sp_executesql @s,N'@EncodeID varchar(20) out',@re out
select @re
Select @EncodeID=FID from UID where FName = @TableName
into UApplyShop(FApplyID) values(@EncodeID)
2\SELECT
@TableName =CASE
WHEN @Name='申购单' THEN 'UApplyShop'
WHEN @Name='采购单' THEN 'UStockOrder'
ELSE 'Expensive book!' end,
@Field =
CASE
WHEN @Name='申购单' THEN 'FApplyID'
WHEN @Name='采购单' THEN 'FOrdID'
ELSE 'Expensive book!' end
CREATE Proc Pro_EncodeID
@Name varChar(20) --类型名称,比如 申购单
As
DECLARE @EncodeID varchar(20) --返回ID
DECLARE @SQL varchar(250) --执行的SQL
DECLARE @TableName varChar(20) --要查询的表名
DECLARE @Field varChar(20) --单号字段
BEGIN SELECT @TableName = CASE @Name
WHEN '申购单' THEN 'UApplyShop'
WHEN '采购单' THEN 'UStockOrder'
ELSE 'Expensive book!'
END,
@Field = CASE @Name
WHEN '申购单' THEN 'FApplyID'
WHEN '采购单' THEN 'FOrdID'
ELSE 'Expensive book!'
END
--获取单头
Set @SQL= 'Select FID from UID where FName ='''+@TableName+''''
--获取最大流水号
Set @SQL ='Select RIGHT(''00000'' + CAST(Max(SUBSTRING(@Field,len(@Field)-4,5))+1 AS varchar(20)),5) from @TableName'
--@EncodeID=单头+日期(yymmdd)+流水号
Set @EncodeID= 单头+CONVERT(varchar(100), GETDATE(), 12)+流水号
--返回@EncodeID
end全部代码这里了,有几个地方不知道怎么改,大家帮看看
DECLARE @SQL varchar(250) --执行的SQL
DECLARE @TableName varChar(20) --要查询的表名
你这几个的值从哪来????//
Set @SQL= 'Select TOP 1 FID from UID where FName ='''+@TableName+''''
--获取最大流水号
Set @SQL ='Select RIGHT(''00000'' + CAST(Max(SUBSTRING('''+@Field+''',len('''+@Field+''')-4,5))+1 AS varchar(20)),5) from '+@TableName
--@EncodeID=单头+日期(yymmdd)+流水号
Set @EncodeID= 单头+CONVERT(varchar(100), GETDATE(), 12)+流水号这里要使用输出参数形式执行获取值再相加
@Name VARCHAR(20), --类型名称,比如 申购单
@EncodeID VARCHAR(20) OUT ----返回ID
ASDECLARE @SQL NVARCHAR(250) --执行的SQL
DECLARE @TableName VARCHAR(20) --要查询的表名
DECLARE @Field VARCHAR(20) --单号字段
BEGIN
SELECT @TableName = CASE @Name
WHEN '申购单' THEN 'UApplyShop'
WHEN '采购单' THEN 'UStockOrder'
ELSE 'Expensive book!'
END,
@Field = CASE @Name
WHEN '申购单' THEN 'FApplyID'
WHEN '采购单' THEN 'FOrdID'
ELSE 'Expensive book!'
END
--获取单头
SET @SQL = 'SELECT @EncodeID=(Select LTRIM(FID) from UID where FName ='''+@TableName+''')+CONVERT(VARCHAR(100), GETDATE(), 12)+
--获取最大流水号
(Select RIGHT(100000 + Max(SUBSTRING('+@Field+',len('+@Field+')-4,5))+1,5) from '+@TableName+')'
--@EncodeID=单头+日期(yymmdd)+流水号
EXEC sp_executesql @SQL,N'@EncodeID VARCHAR(20) OUT',@EncodeID OUT
--返回@EncodeID
END
GO--调用:
DECLARE @EncodeID VARCHAR(20)
EXEC Pro_EncodeID '申购单',@EncodeID OUT
SELECT @EncodeID