USE [AWS]
GO
/****** Object: UserDefinedFunction [dbo].[genCCGC] Script Date: 10/22/2012 09:14:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GOALTER function [dbo].[genCCGC](@X varchar(50))
returns nvarchar(30)
as
begin
--DECLARE @X NVARCHAR(10)
--SET @X = 'A0000009'
DECLARE @Y NVARCHAR(19)
declare @id char(19)
exec makecardid @id output
set @y=@id
return REVERSE(@y)
end
---------------------------------------------------------------------------------------------
if exists(select * from sysobjects where name='makecardid')
drop proc makecardid
go
create proc makecardid
@cardid char(19) output,
@before char(10)='5200 1314'
as
declare @randnum numeric(15,8)
declare @end char(10)
select @randnum= rand(( datepart(mm,getdate())*100000)+( datepart(ss,getdate())*1000)+( datepart(ms,getdate())))set @end=cast(@randnum as char(10))set @cardid=@before+substring(@end,3,4)+' '+substring(@end,7,4)
print '恭喜您,你的卡号是:'+@cardid
go
这个是过程
GO
/****** Object: UserDefinedFunction [dbo].[genCCGC] Script Date: 10/22/2012 09:14:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GOALTER function [dbo].[genCCGC](@X varchar(50))
returns nvarchar(30)
as
begin
--DECLARE @X NVARCHAR(10)
--SET @X = 'A0000009'
DECLARE @Y NVARCHAR(19)
declare @id char(19)
exec makecardid @id output
set @y=@id
return REVERSE(@y)
end
---------------------------------------------------------------------------------------------
if exists(select * from sysobjects where name='makecardid')
drop proc makecardid
go
create proc makecardid
@cardid char(19) output,
@before char(10)='5200 1314'
as
declare @randnum numeric(15,8)
declare @end char(10)
select @randnum= rand(( datepart(mm,getdate())*100000)+( datepart(ss,getdate())*1000)+( datepart(ms,getdate())))set @end=cast(@randnum as char(10))set @cardid=@before+substring(@end,3,4)+' '+substring(@end,7,4)
print '恭喜您,你的卡号是:'+@cardid
go
这个是过程
自定义sp 是不能被调用的。创建成功是sql 的错误延时性提示导致的
select dbo.genCCGC('TEST10010') 是这么执行的 ,
执行后错误 :Only functions and some extended stored procedures can be executed from within a function.
String sql="declare @id char(19)exec makecardid @id output select * from tast"
这样的话..这个sql是否可以执行?
不好意思我钻进死胡同了,,,存储过程本身就是可以返回结果集的.我又何必非要用select 去查询呢.直接用存储过程里面写,返回就可以了,但是返回的结果需要用个值来接受啊...我不想声明一个变量来接收这个值.而是直接得到
drop proc makecardid
go
create proc makecardid
@cardid char(19)='', --output,
@before char(10)='5200 1314'
as
declare @randnum numeric(15,8)
declare @end char(10)
select @randnum= rand(( datepart(mm,getdate())*100000)+( datepart(ss,getdate())*1000)+( datepart(ms,getdate())))set @end=cast(@randnum as char(10))set @cardid=@before+substring(@end,3,4)+' '+substring(@end,7,4)
print '恭喜您,你的卡号是:'+@cardid
--insert into tast(id) values(@cardid)
go
exec makecardid