Getting Error While creating 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)

Getting Error While creating Stored Procedure

Author

Topic

satish.gorijala
Posting Yak Master

182 Posts

Posted - 2009-03-20 : 03:28:08

CREATE PROCEDURE [dbo].[SP_GetCountryData]
@Zipcode varchar(20),
@QueryStr varchar(100)
AS
BEGIN
SELECT TOP 1 WITH TIES theValue,theCol FROM
(SELECT * FROM Tbl_Country WHERE Zip_Final = @Zipcode) AS t1
UNPIVOT(theValue FOR theCol IN (' + @QueryStr + ')) AS u
ORDER BY theValue DESC
END

my requirement is i want to use the dynamic query string in @QueryStr.
The input for @QueryStr will be t1.INDI,t1.CHIN,t1.JPAN

G. Satish

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2009-03-20 : 05:20:36

try with this.

CREATE PROCEDURE [dbo].[SP_GetCountryData]
@Zipcode varchar(20),
@QueryStr varchar(100)
AS
BEGIN
exec('SELECT TOP 1 WITH TIES theValue,theCol FROM
(SELECT * FROM Tbl_Country WHERE Zip_Final ='+ @Zipcode +') AS t1
UNPIVOT(theValue FOR theCol IN (' + @QueryStr + ')) AS u
ORDER BY theValue DESC')
END

Karthik

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources