showing 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)

showing error in stored procedure

Author

Topic

satish.gorijala
Posting Yak Master

182 Posts

Posted - 2009-06-10 : 06:16:34

hi, can u help me what's the error in below procedure. I am getting error as Msg 102, Level 15, State 1, Procedure Sample, Line 14
Incorrect syntax near '@Scode'.

Create Procedure Sample
@SIcode INT
AS
Begin
Declare @Result varchar(20);
Declare @Scode INT;

Select @Result = 'Select Assimilation from Tbl_Data Where sicode = ' + @SIcode

if(@Result is null or @Result = '')
Begin
if( Len(@SIcode) > 5)
Begin
@Scode = LEFT(@SIcode,5);
Select @Result = 'Select Assimilation from Tbl_Data Where sicode = ' + @Scode + 1;
End
End

G. Satish

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-06-10 : 06:30:14

quote:
Originally posted by satish.gorijala

hi, can u help me what's the error in below procedure. I am getting error as Msg 102, Level 15, State 1, Procedure Sample, Line 14
Incorrect syntax near '@Scode'.

Create Procedure Sample
@SIcode INT
AS
Begin
Declare @Result varchar(20);
Declare @Scode INT;

Select @Result = 'Select Assimilation from Tbl_Data Where sicode = ' + @SIcode

if(@Result is null or @Result = '')
Begin
if( Len(@SIcode) > 5)
Begin
@Scode = LEFT(@SIcode,5);
Select @Result = 'Select Assimilation from Tbl_Data Where sicode = ' + @Scode + 1;
End
End
END
G. Satish

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-10 : 06:45:22

You can't concatenate INT with VARCHAR. Convert INT to VARCHAR, and then concatenate.
Your next problem is that @Result variable is only declared a 20 characters, but the string you are building is longer than that.
Your third problem is the assignment of @scode variable, which is missing a SELECT or SET.

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-10 : 06:48:14

[code]CREATE PROCEDURE dbo.uspSample
(
@SIcode INT
)
AS

SET NOCOUNT ON

DECLARE @Result VARCHAR(200),
@Scode INT

SET @Result = 'SELECT Assimilation FROM Tbl_Data WHERE SIcode = ' + STR(@SIcode)

IF @Result IS NULL OR @Result = ''
IF LEN(@SIcode) > 5
BEGIN
SET @Scode = LEFT(@SIcode, 5)
SET @Result = 'SELECT Assimilation FROM Tbl_Data WHERE SIcode = ' + STR(@Scode + 1);
END
END[/code]

E 12°55'05.63"
N 56°04'39.26"

satish.gorijala
Posting Yak Master

182 Posts

Posted - 2009-06-10 : 06:49:22

deleted

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-10 : 06:50:27

See FULL suggestion posted 06/10/2009 : 06:48:14

E 12°55'05.63"
N 56°04'39.26"

satish.gorijala
Posting Yak Master

182 Posts

Posted - 2009-06-10 : 07:11:10

Hi Peso, I am not able to understand your procedure. How it will execute. I will explain my requirement clearly. I will give input "SICode" as input to stored procedure. It checks in database with this code. if record not exist with those code, in same procedure, i am adding value "1" to SICode and againg checking in database. If not found again i have to check by subtracting "1" to SICode. Finally in any of the above case, if record exist it has to retrive assimilation column data. when i run your stored procedure it showing "Command(s) completed successfully.". When i use "Print @Result" in stored procedure i am getting result as "SELECT Assimilation FROM Tbl_Data WHERE SIcode = 94188". Whether stored procedure checking all the above conditons are not..dont no? i am new to stored procedures...can you give me the exact procedure(code)..how my requirement works.

ALTER PROCEDURE dbo.uspSample
(
@SIcode INT
)
AS
Begin
SET NOCOUNT ON

DECLARE @Result VARCHAR(200),
@Scode INT

SET @Result = 'SELECT Assimilation FROM Tbl_Data WHERE SIcode = ' + STR(@SIcode)

IF @Result IS NULL OR @Result = ''
IF LEN(@SIcode) > 5
BEGIN
SET @Scode = LEFT(@SIcode, 5)
SET @Result = 'SELECT Assimilation FROM Tbl_Data WHERE SIcode = ' + STR(@Scode + 1);
END

Print @Result

End

Exec uspSample 94188897

G. Satish

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-10 : 07:14:26

I can't tell! I have NO idea what the LEFT(5) part is doing in all this.

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-10 : 07:16:03

Unless you are NOT adding a 1 to the number. If you are padding or concatenating a 1 at the end, try this

CREATE PROCEDURE dbo.uspSample
(
	@SIcode INT
)
AS

SET NOCOUNT ON

DECLARE @Result VARCHAR(200),
	@Scode INT

SET	@Result = 'SELECT Assimilation FROM Tbl_Data WHERE SIcode = ' + STR(@SIcode)

IF @Result IS NULL OR @Result = ''
	BEGIN
		IF LEN(@SIcode) > 5
			SET	@Result = 'SELECT Assimilation FROM Tbl_Data WHERE SIcode = ' + LEFT(@SIcode, 5) + '1'
	END

E 12°55'05.63"
N 56°04'39.26"

satish.gorijala
Posting Yak Master

182 Posts

Posted - 2009-06-10 : 07:19:48

If the result not found by SICode, what i am doing is if the length of SICODE is greater than 5 i want to take the first five digits of that SICode. How can i do this? i tried by using left(sicode,5). Dont know whether it works in stored procedure or not? but when i use in query like "Select Left(12345,3)" it gives output as 123

quote:
Originally posted by Peso

I can't tell! I have NO idea what the LEFT(5) part is doing in all this.

E 12°55'05.63"
N 56°04'39.26"

G. Satish

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources