Creating Stored Procedure With Output Parameter

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

General SQL Server Forums

New to SQL Server Programming

Creating Stored Procedure With Output Parameter

Author

Topic

Plotin
Starting Member

21 Posts

Posted - 2005-12-05 : 19:18:44

Hi

Being a rookie to stored procedures the following cry for help will be posted.

The stored procedure below is supposed to create a dynamic string as an output parameter

create procedure spGetFieldNameList
@TableName varchar(256),@String varchar(8000) output
As
select @String = @String + sc.name + ','
from syscolumns as sc(nolock) join sysobjects as so(nolock)
on sc.id = so.id
and so.name = @TableName
order by colid asc

Creating the stored procedure does not trigger an error.

Calling the stored procedure as: spGetFieldNameList 'OfferCondition'.
Causes the following message:
Server: Msg 201, Level 16, State 4, Procedure spGetFieldNameList, Line 0
Procedure 'spGetFieldNameList' expects parameter '@String', which was not supplied.

Calling it this way:
declare @String as varchar(8000)
set @String = ''
set @String = spGetFieldNameList 'OfferCondition'.

Causes:
Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near 'OfferCondition'.

Any help is highly appreciated.

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-12-05 : 20:28:11

call your store pro this way
declare @String as varchar(8000)
exec spGetFieldNameList 'OfferCondition', @String OUTPUT

-----------------
[KH]

activecrypt
Posting Yak Master

165 Posts

Posted - 2005-12-05 : 23:51:38

Hi,
or you may refer * CREATE PROCEDURE * from BOL , BOL is a good frend.

Andy Davis
Activecrypt Team
--------------------------------------------
SQL Server Encryption Software
http://www.activecrypt.com

Kristen
Test

22859 Posts

Posted - 2005-12-06 : 04:21:36

If you NEED the parameter preinitialized OUTSIDE the Sproc then you will need to call it like this:


declare @MyString as varchar(8000),
        @intErrorNo int
set @MyString = ''
EXEC @intErrorNo = dbo.spGetFieldNameList 'OfferCondition', @String=@MyString OUTPUT
IF @intErrorNo <> 0 ... error handling logic ...

I changed your external parameter to @MyString so that you could see which was what, but there is nothing to stop you actually have to parameter and the local variable use the same name. I also added in some stuff in blue that you might want to consider adding.

Kristen

Plotin
Starting Member

21 Posts

Posted - 2005-12-06 : 08:15:14

Hi khatan

Following your advice, the return value of @String is NULL.

Plotin

Plotin
Starting Member

21 Posts

Posted - 2005-12-06 : 08:26:12

Hi Kristen

Thanks for the solution.

All I had to add is the declaration of @String.

declare @MyString as varchar(8000),
@String as varchar(8000),
@intErrorNo int
set @MyString = ''

EXEC @intErrorNo = dbo.spGetFieldNameList 'OfferCondition',@String = @MyString OUTPUT
IF @intErrorNo <> 0
Print @intErrorNo
ELSE
Print @MyString

Plotin

Kristen
Test

22859 Posts

Posted - 2005-12-06 : 15:58:11

"All I had to add is the declaration of @String"

Not sure why, as it isn't used in the code snippet you've posted, is it?

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources