stored procedure help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

stored procedure help

Author

Topic

phaze
Starting Member

42 Posts

Posted - 2005-04-05 : 00:06:50

I run the following stored procedure and I get an error message. Can anyone explain to me why the error occured.

DECLARE @RC int
DECLARE @client_database varchar(32)
DECLARE @create_table int
DECLARE @create_indices int
DECLARE @update_accounts_id_column int
EXEC @RC = [jnguyen].[dbo].[proc_createtrans] DEFAULT, DEFAULT, DEFAULT, DEFAULT
DECLARE @PrnLine nvarchar(4000)
PRINT 'Stored Procedure: jnguyen.dbo.proc_createtrans'
SELECT @PrnLine = ' Return Code = ' + CONVERT(nvarchar, @RC)
PRINT @PrnLine

the error is follows

Server: Msg 201, Level 16, State 3, Procedure proc_createtrans, Line 0
Procedure 'proc_createtrans' expects parameter '@client_database', which was not supplied.
Stored Procedure: jnguyen.dbo.proc_createtrans

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-04-05 : 00:10:14

Post the code for proc_createtrans. Or, just post the DECLARE part of it. That should be enough.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

phaze
Starting Member

42 Posts

Posted - 2005-04-05 : 00:20:58

I believe this is what you are asking for?

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

/*
Name:
dbo.proc_createtrans

Description:
Creates a new (or overwrites) trans table
in the specified database. dbo.tran_gifts
depends on dbo.trans and will be dropped
if dbo.trans is dropped.

Parameters:
@client_database - The client database
to affect.
@create_table - Default is 1. If zero,
the ALTER table operation is skipped
(optional).
@create_indices - Default is 1. If zero,
the create indices operation is skipped
(optional).
@update_account_id_column - Default is NULL.

*/

create procedure dbo.proc_createtrans
@client_database varchar(32),
@create_table int = 1,
@create_indices int = 1,
@update_accounts_id_column int = NULL
as
set nocount on

declare @query nvarchar(4000)

if ( @create_table = 1 )
begin

set @query = 'use [' + @client_database + ']'

set @query = @query + '

if exists (
select *
from dbo.sysobjects
where id = object_id(''trans_gifts'')
) drop table trans_gifts

print ''Table '' + db_name() + ''..trans_gifts dropped ...''

if exists (
select *
from dbo.sysobjects
where id = object_id(''trans'')
) drop table trans

print ''Table '' + db_name() + ''..trans dropped ...''

create table dbo.trans (
[id] int primary key IDENTITY(1, 1) not NULL,
accounts_id int NULL,
accounts_code varchar(32) NULL,
[date] datetime NULL,
[amount] money NULL,
type varchar(2) NULL,
motive varchar(32) NULL,
fund varchar(32) NULL,
payment_type varchar(2) NULL,
pledge_flag char(1) NULL,
receipt_flag char(1) NULL,
letter_flag char (1) NULL,
receipt varchar (32) NULL,
note varchar (128) NULL,
xref_account varchar (32) NULL
)

print ''Table '' + db_name() + ''.dbo.trans created ...'' '

exec sp_executesql @query

end

if ( @create_indices = 1 )
begin

set @query = 'use [' + @client_database + ']'

set @query = @query + '

create nonclustered index ix_trans
on dbo.trans ( accounts_id, accounts_code, [date], amount, motive )

create nonclustered index ix_flags
on dbo.trans ( type, payment_type, pledge_flag, receipt_flag, letter_flag )

create nonclustered index ix_codes
on dbo.trans ( accounts_code, motive, fund, receipt, xref_account )

print ''Indices for '' + db_name() + ''.dbo.trans created ...'' '

exec sp_executesql @query

end

if ( @update_accounts_id_column = 1 )
begin

set @query = 'use [' + @client_database + ']'

set @query = @query + '

if exists (
select *
from dbo.sysobjects
where id = object_id(''accounts'')
)
begin
update dbo.trans
set accounts_id = ( select top 1 accounts.id from accounts where accounts.code = accounts_code )

print ''Columun accounts_id in '' + db_name() + ''.dbo.accounts has been updated.''
end
else
begin
print ''Columun accounts_id will be NULL because '' + db_name() + ''.dbo.accounts doesn''''t exist''
end '

exec sp_executesql @query

end

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-04-05 : 00:54:07

Well, that was an easy one. Read Books Online for DEFAULT:

quote:

DEFAULT

Supplies the default value of the parameter as defined in the procedure. When the procedure expects a value for a parameter that does not have a defined default and either a parameter is missing or the DEFAULT keyword is specified, an error occurs.

You don't have a default defined for @client_database, so you're getting an error trying to use that keyword. Either define a default, or pass in a real value.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources