Error in Stored Procedure: name is not valid ident

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)

Error in Stored Procedure: name is not valid ident

Author

Topic

satish.gorijala
Posting Yak Master

182 Posts

Posted - 2009-08-03 : 04:51:53

I am getting below error by executing my stored procedure. Please can you find the error in my sp.

My Observation: If i remove the statement "Exec @Result" from my storedprocdure, then its not giving any error and it showing result as "Command(s) completed successfully.". When i put statement "PRINT @Result". it printing the query. When i execute it, it giving result.

Msg 203, Level 16, State 2, Procedure MYPROC_GetCountryData, Line 75
The name 'SELECT TOP 1 WITH TIES theCol FROM (SELECT * FROM SGX_Tbl_CountryOrigin WHERE Zip_Final ='68025-3562') AS t1 UNPIVOT (theValue FOR theCol IN (t1.engl,t1.germ,t1.iris,t1.jewi,t1.scot,t1.wels)) AS u1 ORDER BY theValue DESC' is not a valid identifier.

--Exec MYPROC_GetCountryData '20906-3630','t1.blac,t1.germ'

Note: Zip_Final is the varchar column in the table.

ALTER PROCEDURE [dbo].[MYPROC_GetCountryData]
(
@Zipcode Varchar(50),
@Querystr Varchar(200)
)
AS
Begin
SET NOCOUNT ON

DECLARE @Result VARCHAR(MAX),
@ZcodeHigh INT,
@ZcodeLow INT,
@ZcodeToUse varchar(500),
@ZcodeBefore varchar(500),
@ZcodeAfter varchar(500)

Set @Result = 'SELECT TOP 1 WITH TIES theCol FROM (SELECT * FROM Tbl_origin WHERE Zip_Final =''' + @Zipcode + ''') AS t1 UNPIVOT (theValue FOR theCol IN (' + @Querystr + ')) AS u1 ORDER BY theValue DESC'

IF @Result IS NULL OR @Result = ''

Begin

--Splitting Zipcode at hypen. And check with zip(hypen before) to DB. If not found check again with +1 and -1

IF(CHARINDEX('-', @Zipcode)> 0)
BEGIN
SET @ZcodeBefore = 'SElect SUBSTRING(@Zipcode,1, (CHARINDEX(''-'',@Zipcode))-1)'
SET @ZcodeAfter = 'SElect SUBSTRING(@Zipcode,CHARINDEX(''-'',@Zipcode)+ 1,DATALENGTH(@Zipcode))'

IF LEN(@ZcodeBefore) <= 5
BEGIN
SET @Result = 'SELECT TOP 1 WITH TIES theCol FROM (SELECT * FROM Tbl_origin WHERE Zip_Final =''' + @ZcodeBefore + ''') AS t1 UNPIVOT (theValue FOR theCol IN (' + @Querystr + ')) AS u2 ORDER BY theValue DESC'
IF @Result IS NULL OR @Result = ''
Begin
SET @ZcodeHigh = 'Select Convert(INT,@ZcodeBefore) + 1'
SET @Result = 'SELECT TOP 1 WITH TIES theCol FROM (SELECT * FROM Tbl_origin WHERE Zip_Final =''' + @ZcodeHigh + ''') AS t1 UNPIVOT (theValue FOR theCol IN (' + @Querystr + ')) AS u2 ORDER BY theValue DESC'
IF @Result IS NULL OR @Result = ''
BEGIN
SET @ZcodeLow = 'Select Convert(INT,@ZcodeBefore)-1'
SET @Result = 'SELECT TOP 1 WITH TIES theCol FROM (SELECT * FROM Tbl_origin WHERE Zip_Final =''' + @ZcodeLow + ''') AS t1 UNPIVOT (theValue FOR theCol IN (' + @Querystr + ')) AS u3 ORDER BY theValue DESC'
END
END
END
END
-- If there is no hypen in zipcode. Check by adding +1 and -1
Else IF(CHARINDEX('-', @Zipcode) = 0)
BEGIN
SET @ZcodeHigh = 'Select Convert(INT,@Zipcode) + 1'
SET @Result = 'SELECT TOP 1 WITH TIES theCol FROM (SELECT * FROM Tbl_origin WHERE Zip_Final =''' + @ZcodeHigh + ''') AS t1 UNPIVOT (theValue FOR theCol IN (' + @Querystr + ')) AS u4 ORDER BY theValue DESC'

IF @Result IS NULL OR @Result = ''
BEGIN
SET @ZcodeLow = 'Select Convert(INT,@Zipcode) - 1'
SET @Result = 'SELECT TOP 1 WITH TIES theCol FROM (SELECT * FROM Tbl_origin WHERE Zip_Final =''' + @ZcodeLow + ''') AS t1 UNPIVOT (theValue FOR theCol IN (' + @Querystr + ')) AS u5 ORDER BY theValue DESC'
END
END
End

Exec @Result
End

developer :)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-03 : 05:13:03

the size of @Result is too small. Your SQL Query will be truncated


DECLARE @Result VARCHAR(200 MAX),

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-03 : 06:11:35

use


exec (@result)

also take a look at The Curse and Blessings of Dynamic SQL

KH
[spoiler]Time is always against us[/spoiler]

satish.gorijala
Posting Yak Master

182 Posts

Posted - 2009-08-03 : 07:18:08

I am getting Error at this line. Its not a compiler error but runtime one. See the highlighted one. When i try to execute the same line separatley it giving the error as "Operand data type varchar is invalid for subtract operator."

SET @ZcodeBefore = 'SElect SUBSTRING(@Zipcode,1, (CHARINDEX(''-'',@Zipcode))-1)'

developer :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources