Correct Syntax - 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

General SQL Server Forums

New to SQL Server Programming

Correct Syntax

Author

Topic

BendJoe
Posting Yak Master

128 Posts

Posted - 2008-02-25 : 17:05:51

Hi.

This is a Stored proc I am working on
I dont know much about stored procs.
This is what I am trying to achieve
The proc takes two input parameter and returns one outparameter.

This is what I have

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[GetNextAction]

(
@Code char(10),
@Track varchar(30),
@NextAction ntext output
)
AS
BEGIN

SET NOCOUNT ON;
Declare @ID int;
@ID = Select Sequence from [dbo].[Track] where Code=@Code;
Declare @nextCode varchar;
@nextCode= Select Code from [dbo].[Track] where sequence =(@ID+1);
@NextAction= Select nextAction from [dbo].[CaseStage] where Code=@nextCode;
return @NextAction;

END
GO

I guess you can understand what I am trying in the proc but the rules and syntax are wrong. Please help me with this.
Thanks

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-02-25 : 17:11:09

try this

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[GetNextAction]
(
@Code char(10),
@Track varchar(30),
@NextAction ntext output
)
AS
BEGIN

SET NOCOUNT ON;
Declare @ID int;
Select @ID=Sequence from [dbo].[Track] where Code=@Code;
Declare @nextCode varchar;
Select @nextCode=Code from [dbo].[Track] where sequence =(@ID+1);
Select @NextAction=nextAction from [dbo].[CaseStage] where Code=@nextCode;
return @NextAction;

END
GO

BendJoe
Posting Yak Master

128 Posts

Posted - 2008-02-25 : 17:28:44

Got that one right now one more question Can I transfer table name as a parameter,
So my
Select will be like,
Select * from [db0].[@TableName];

Will this work.
Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-02-25 : 17:57:45

Yes but it is a very bad idea due to performance and security reasons. Why do you want to do this? Lazy programming?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-26 : 01:28:06

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=97902

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources