Quote:
-- =============================================
-- Create procedure with OUTPUT Parameters
-- =============================================
-- creating the store procedure
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'<procedure_name, sysname, proc_test>'
AND type = 'P')
DROP PROCEDURE <procedure_name, sysname, proc_test>
GO
CREATE PROCEDURE <procedure_name, sysname, proc_test>
<@param1, sysname, @p1> <datatype_for_param1, , int> = <default_value_for_param1, , 0>,
<@param2, sysname, @p2> <datatype_for_param2, , int> OUTPUT
AS
SELECT @p2 = @p2 + @p1
GO
-- =============================================
-- example to execute the store procedure
-- =============================================
DECLARE <@variable_for_output_parameter, sysname, @p2_output> <datatype_for_output_parameter, , int>
EXECUTE <procedure_name, sysname, proc_test> <value_for_param1, , 1>, <@variable_for_output_parameter, sysname, @p2_output> OUTPUT
SELECT <@variable_for_output_parameter, sysname, @p2_output>
GO
Quote:
create procedure sp_addlinkedsrvlogin
@rmtsrvname sysname,
@useself varchar(8) = 'true',
@locallogin sysname = NULL,
@rmtuser sysname = NULL,
@rmtpassword sysname = NULL
as
declare @srvid smallint,
@status smallint,
@localsid varbinary(85),
@ret int
-- DISALLOW USER TRANSACTION
set implicit_transactions off
if @@trancount > 0
begin
raiserror(15002,-1,-1,'sp_addlinkedsrvlogin')
return (1)
end
-- CHECK PERMISSIONS
if not (is_srvrolemember('securityadmin') = 1)
begin
raiserror(15247,-1,-1)
return (1)
end
-- VALIDATE @useself PARAMETER --
select @useself = lower(@useself)
if @useself is null or @useself not in ('true','false')
begin
raiserror(15600,-1,-1,'sp_addlinkedsrvlogin')
return 1
end
-- CHECK REMOTE SERVER NAME.
select @srvid = srvid from master.dbo.sysservers where srvname = @rmtsrvname
if @srvid is null
begin
raiserror(15015,-1,-1,@rmtsrvname)
return (1)
end
-- IF SPECIFIED CHECK LOCAL USER NAME
if (@locallogin IS NOT NULL)
begin
select @localsid = sid from master.dbo.syslogins where loginname = @locallogin
if @localsid IS NULL
begin
-- ADD ROW FOR NT LOGIN IF NEEDED --
execute @ret = sp_MSaddlogin_implicit_ntlogin @locallogin
if (@ret = 0)
select @localsid = sid from master.dbo.syslogins where loginname = @locallogin
if (@localsid IS NULL)
begin
raiserror(15067,-1,-1,@locallogin)
return (1)
end
end
end
-- 64 IMPLIES sysxlogins::ishqoutmap is TRUE
select @status = 64
-- IF @useself IS TRUE IT OVERRIDES PARAMETERS @rmtuser, and @rmtpassword
if @useself = 'true'
begin
select @rmtuser = NULL
select @rmtpassword = NULL
select @status = @status | 128
end
BEGIN TRAN
-- DELETE EXISTING MAPPING(s) FOR THIS @sid
update master.dbo.sysxlogins set xstatus = xstatus & ~192
where srvid = @srvid AND ishqoutmap = 1 AND isrpcinmap = 1
AND ((sid IS NULL AND @localsid IS NULL) OR sid = @localsid)
if @@rowcount = 0
delete master.dbo.sysxlogins where srvid = @srvid AND ishqoutmap = 1
AND ((sid IS NULL AND @localsid IS NULL) OR sid = @localsid)
-- ATTEMPT TO TAG THIS ONTO EXISTING ROW --
update master.dbo.sysxlogins
set xstatus = (xstatus & ~192) | @status,
xdate2 = getdate(),
password = convert(varbinary(256), encrypt(@rmtpassword))
where srvid = @srvid AND isrpcinmap = 1
AND ((sid IS NULL AND @localsid IS NULL) OR sid = @localsid)
AND ((name IS NULL AND @rmtuser IS NULL) OR name = @rmtuser)
-- IF NO ROW UPDATED, INSERT NEW ROW --
if (@@rowcount = 0)
insert master.dbo.sysxlogins values
(@srvid, @localsid, @status, getdate(), getdate(), @rmtuser,
convert(varbinary(256), encrypt(@rmtpassword)), 0, NULL)
COMMIT TRAN
-- RETURN SUCCESS
return(0) -- sp_addlinkedsrvlogin
GO
Quote:
CREATE PROC [ EDURE ] procedure_name [ ; number ]
[ { @parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ]
] [ ,...n ]
[ WITH
{ RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]
[ FOR REPLICATION ]
AS sql_statement [ ...n ]