Error in Stored Procedure. - 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 2005 Forums

Transact-SQL (2005)

Error in Stored Procedure.

Author

Topic

satish.gorijala
Posting Yak Master

182 Posts

Posted - 2009-07-24 : 05:04:51

Hi, i wrote one stored procedure. The aim of it is. It has take input as zipcode. It checks directly in table with query. If there is no record with this zipcode then check the lenth of its zipcode. If it is <= 5 then add "1" to zipcode and again check in Db. If there is no result then subtract "1" from zipcode and check with DB.

When i try to execute this sp, i am getting this error
"Msg 245, Level 16, State 1, Procedure Testproc, Line 13
Conversion failed when converting the varchar value 'SELECT TOP 1 WITH TIES theCol
FROM (
SELECT * FROM SGX_TBL_ZipcodeBreakdown WHERE Zipcode_final = ' to data type int."

can any one correct my sp..

Note: Zipcode_final column is of type varchar in table.

ALTER PROCEDURE [dbo].[Testproc]
(
@Zipcode INT
)
AS
Begin
SET NOCOUNT ON

DECLARE @Result VARCHAR(200),
@ZcodeHigh INT,
@ZcodeLow INT

SET @Result = 'SELECT TOP 1 WITH TIES theCol
FROM (
SELECT * FROM TBL_ZipcodeBreakdown WHERE Zipcode_final = ' + @Zipcode + '
) AS t1
UNPIVOT (
theValue
FOR theCol IN (t1.Blac, t1.Native, t1.Asian, t1.Hawaii, t1.Twoplus, t1.Hispanic, t1.White)
) AS u
ORDER BY theValue DESC'

IF @Result IS NULL OR @Result = ''
IF LEN(@Zipcode) <= 5
BEGIN
SET @ZcodeHigh = @Zipcode + 1
SET @Result = 'SELECT TOP 1 WITH TIES theCol
FROM (
SELECT * FROM TBL_ZipcodeBreakdown WHERE Zipcode_final = ' + STR(@ZcodeHigh) + '
) AS t1
UNPIVOT (
theValue
FOR theCol IN (t1.Blac, t1.Native, t1.Asian, t1.Hawaii, t1.Twoplus, t1.Hispanic, t1.White)
) AS u
ORDER BY theValue DESC'

IF @Result IS NULL OR @Result = ''
BEGIN
SET @ZcodeLow = @Zipcode - 1
SET @Result = 'SELECT TOP 1 WITH TIES theCol
FROM (
SELECT * FROM TBL_ZipcodeBreakdown WHERE Zipcode_final = ' + @ZcodeLow + '
) AS t1
UNPIVOT (
theValue
FOR theCol IN (t1.Blac, t1.Native, t1.Asian, t1.Hawaii, t1.Twoplus, t1.Hispanic, t1.White)
) AS u
ORDER BY theValue DESC'
END
END

--Exec @Result

End

G. Satish

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-24 : 05:22:30

To concatenate @Zipcode (type is INT) you have to convert the @Zipcode to varchar.

No, you're never too old to Yak'n'Roll if you're too young to die.

asgast
Posting Yak Master

149 Posts

Posted - 2009-07-24 : 06:57:34

'SELECT TOP 1 WITH TIES theCol
FROM (
SELECT * FROM TBL_ZipcodeBreakdown WHERE Zipcode_final = ' + CAST(@Zipcode As varchar(10)) + '
) AS t1
UNPIVOT (
theValue
FOR theCol IN (t1.Blac, t1.Native, t1.Asian, t1.Hawaii, t1.Twoplus, t1.Hispanic, t1.White)
) AS u
ORDER BY theValue DESC'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources