Need Urgent 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

SQL Server Development (2000)

Need Urgent Help

Author

Topic

awbc
Starting Member

2 Posts

Posted - 2005-04-29 : 01:43:39

Hi i have a SP containing a dynamic T-SQL. When i execute the stored procedure with it's value i receive this error msg.

----
Server: Msg 178, Level 15, State 1, Line 1
A RETURN statement with a return value cannot be used in this context.
----

My values, Stored procedure and table statement is being create as below. So could anyone let me know what mistake did i do in this SP.

TBLNAME = "AA"
FieldDesc = "BOX"

alter PROCEDURE UDP_InsTable
@TBLNAME VARCHAR(100),
@FieldDesc VARCHAR(100)
AS
DECLARE @STR01 VARCHAR(2000)
BEGIN
SET @STR01 = 'IF NOT EXISTS (SELECT * FROM [' + @TBLNAME + '] WHERE FIELDDESC = ''' + @FieldDesc + ''')'
SET @STR01 = @STR01 + 'BEGIN '
SET @STR01 = @STR01 + 'INSERT INTO [' + @TBLNAME + '] ([FIELDDESC]) VALUES(''' + @FieldDesc + ''') RETURN 0 '
SET @STR01 = @STR01 + 'END '
SET @STR01 = @STR01 + 'ELSE '
SET @STR01 = @STR01 + 'BEGIN '
SET @STR01 = @STR01 + 'RETURN 1 '
SET @STR01 = @STR01 + 'END '
--select @STR01
EXEC(@STR01)
END

CREATE TABLE [dbo].[AAA] (
[ID] [int] IDENTITY (100, 1) NOT NULL ,
[FieldDesc] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-04-29 : 02:55:57

Instead of RETURN use PRINT

Madhivanan

Failing to plan is Planning to fail

awbc
Starting Member

2 Posts

Posted - 2005-04-29 : 03:49:48

quote:
Originally posted by madhivanan

Instead of RETURN use PRINT

Madhivanan

Failing to plan is Planning to fail

Okay Madhivanan. Actually i'm using VB6 to call this SP. So if i use print i can't get the value. I know there is another way to do it but i would like to know why what cause return error when i do this kind of SP's.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-04-29 : 04:57:36

your dynamic sql is in another batch than your sproc.
so you can't use return in this way.
because you basicaly do
insert into ....
values(...)
return 0

and the parser isn't treating it like a part of the sproc.

you'll have to do some magic with sp_executesql. read about it in BOL = Books Online = Sql server help
understand what it does, play with it and then ask questions if you don't get something.
it's the only way you'll learn.

Go with the flow & have fun! Else fight the flow

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-04-29 : 05:01:23

You need to use output parameter. Refer this

http://sqlteam.com/forums/topic.asp?TOPIC_ID=37826&SearchTerms=return,parameter

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources