34,590
社区成员
发帖
与我相关
我的任务
分享
CREATE PROCEDURE [dbo].[sp_test] @i INT ,@o INT OUTPUT
AS
BEGIN
SET @o=@i*100
END
DECLARE @o1 INT,@o2 INT
EXEC dbo.sp_test @i = 10,@o=@o1 OUTPUT
EXEC dbo.sp_test @i = 20,@o=@o2 OUTPUT
SELECT @o1,@o2
-------
1000 2000
USE tempdb
GO
IF EXISTS( SELECT * FROM sys.procedures AS p WHERE p.[object_id]=OBJECT_ID('ins') )
DROP PROC ins
GO
CREATE PROC [ins]
@p1 NVARCHAR(20),
@p2 NVARCHAR(20),
@p3 NVARCHAR(20)
AS
BEGIN
SET NOCOUNT ON
SELECT @p1+'_'+@p2+'_'+@p3
END
GO
--下面是正式调用
declare @a table(a nvarchar(64),b nvarchar(64))
DECLARE @r TABLE (rowNum INT IDENTITY(1,1), r NVARCHAR(MAX))
INSERT INTO @r(r)
exec ins 'g','水果','nm'
INSERT INTO @r(r)
exec ins 'person','苹果','nm'
insert @a(a,b)
SELECT (SELECT r FROM @r WHERE rowNum=1),(SELECT r FROM @r WHERE rowNum=2)
select * from @a
/*
a b
g_水果_nm person_苹果_nm
*/
ALTER PROCEDURE ins
(
@t NVARCHAR(32) ,--表名
@v NVARCHAR(222) ,--值,多个时以逗号(',')分隔,不用加引号
@n NVARCHAR(222) = ''--字段名,多个时以逗号(',')分隔,当插入全部字段时可缺省
)
AS
DECLARE @s NVARCHAR(444);--执行的sql
SET @s = N'insert ' + @t + ' ';
IF ( @n != '' )
SET @s = @s + '(' + @n + ')';
BEGIN
SET @v = '''' + REPLACE(@v, ',', ''',''') + ''''--2个单引号会替换成3个
SET @v = REPLACE(@v, '''''''', '''''')--3个单引号换回2个
END
SET @s = @s + 'values(' + @v + ')' + ';select scope_identity()';
EXEC(@s)
GO