Getting Error in stored procedure

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 2005 Forums

Transact-SQL (2005)

Getting Error in stored procedure

Author

Topic

satish.gorijala
Posting Yak Master

182 Posts

Posted - 2009-06-15 : 07:30:14

Hi, i have writeen below stored procedure. The input of stored procedure should be zipcode. The zipcode should be minimum 3 to 8 characters. if the zipcode is 94122345, it checks with DB, if result si null and length is greater than 5, it has to take first five digits of the zipcode as input.(so it tooks 94122). But getting error when i used left(zipcode,5). If the left function is not correct what is the other function to take only first 5 digists of the integer.

When i try to compile this, it giving error as follows:

"Msg 102, Level 15, State 1, Procedure Sample, Line 32
Incorrect syntax near '@Zcode'."

Create Procedure Sample
@Zipcode INT
AS
Begin

Declare @Result varchar(20), @Zcode INT ;

Select @Result = 'Select Atm from Tbl_ZipData Where Zipcode = ' + @Zipcode

if(@Result is null or @Result = '')
Begin

if( Len(@Zipcode) <= 5)
Begin
Select @Result = 'Select Atm from Tbl_ZipData Where Zipcode = ' + @Zipcode + 1;

if(@Result is null or @Result = '')
Select @Result = 'Select Atm from Tbl_ZipData Where Zipcode = ' + @Zipcode - 1;

if(@Result is null or @Result = '')
Select @Result = 'Select Atm from Tbl_ZipData Where Zipcode = ' + @Zipcode + 2;

if(@Result is null or @Result = '')
Select @Result = 'Select Atm from Tbl_ZipData Where Zipcode = ' + @Zipcode - 2;
End
-------------------

if( Len(@Zipcode) > 5)
Begin
@Zcode = LEFT(@Zipcode,5);

Select @Result = 'Select Atm from Tbl_ZipData Where Zipcode = ' + @Zcode + 1;

if(@Result is null or @Result = '')
Select @Result = 'Select Atm from Tbl_ZipData Where Zipcode = ' + @Zcode - 1;

if(@Result is null or @Result = '')
Select @Result = 'Select Atm from Tbl_ZipData Where Zipcode = ' + @Zcode + 2;

if(@Result is null or @Result = '')
Select @Result = 'Select Atm from Tbl_ZipData Where Zipcode = ' + @Zcode - 2;
End
--------------
End

G. Satish

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-06-15 : 07:38:07

Syntax error here


if( Len(@Zipcode) > 5)
Begin	
@Zcode = LEFT(@Zipcode,5);

Should be


if( Len(@Zipcode) > 5)
Begin	
SET @Zcode = LEFT(@Zipcode,5);

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-06-15 : 07:39:04

Or use


SELECT @Zipcode = xxxx

if you prefer to use that notation.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

satish.gorijala
Posting Yak Master

182 Posts

Posted - 2009-06-15 : 07:43:40

Thank you.

quote:
Originally posted by Transact Charlie

Syntax error here

if( Len(@Zipcode) > 5)
Begin
@Zcode = LEFT(@Zipcode,5);

Should be

if( Len(@Zipcode) > 5)
Begin
SET @Zcode = LEFT(@Zipcode,5);

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

G. Satish

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources