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

Mng
Yak Posting Veteran

59 Posts

Posted - 2009-08-03 : 09:36:36

Hi, i have writeen below stored procedure. I will give input as one zipcode and querystr. If there is no record with given zipcode, it has to go for another query. But it is not going in second if condition. When i try to print the query always showing the first one.

--Exec Test '48019','t1.engl,t1.germ,t1.iris,t1.jewi,t1.scot,t1.wels'

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

DECLARE @Result VARCHAR(MAX)

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
Set @Result = 'Select Top 1 theCol from Tbl_Origin'
End

--Exec(@Result)
Print @Result
End

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-08-03 : 10:33:29

Are you coming from MySQL?
You are setting @Result to 'SELECT TOP 1...'
Now the value of @Result is 'SELECT TOP 1 ...'
But there is no execution!
So @Result is not null and it is not = '' because it is 'SELECT TOP 1 ...'

You may overthink what you are doing there :)

Webfred

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

Mng
Yak Posting Veteran

59 Posts

Posted - 2009-08-04 : 01:25:35

hmm. then how to check that one. need to put exec statement before and then if condition.? Can any one modify the above stored procedure to correct one. It may helps me to come out this problem.

saran_d28
Starting Member

36 Posts

Posted - 2009-08-04 : 02:52:06

Hi Try this,

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

DECLARE @Result VARCHAR(MAX)
if exists (select 1 from Tbl_Origin WHERE Zip_Final = @Zipcode)
begin
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'
end
else
Begin
Set @Result = 'Select Top 1 theCol from Tbl_Origin'
end
Exec(@Result)

End

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-08-04 : 02:55:53

Something like this:

IF EXISTS (SELECT * FROM Tbl_Origin WHERE Zip_Final = @Zipcode)
 Begin
  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'
 End
Else
 Begin
  Set @Result = 'Select Top 1 theCol from Tbl_Origin'
 End

--Exec(@Result)
Print @Result

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

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-08-04 : 02:56:37

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources