Error in Stored Procedure.
-
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
G. Satish
-
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
G. Satish
Good day Satish Becacause of time i have, i will not write you a solution , but i will point to a problem First you use one variable to do all things. Why do you create one variable that will hold the SQl statement and one to store the results and evauate the result. i think you know what you are doing but you get confused by your code. declare another variable to hold the results after that you must test for only nulls and remove the quotes and try it like this
DECLARE @Result VARCHAR(MAX)
DECLARE @Final_Res 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
Set @Result = 'Select Top 1 theCol from Tbl_Origin'
else
select @Result
Hope this Helps
Vuyiswa Maseko, Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers." C#/VB.NET/ASP.NET/SQL7/2000/2005/2008 http://www.vuyiswamaseko.somee.com vuyiswa@its.co.za http://www.itsabacus.co.za/itsabacus/