help with dynamic SQL - 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 2000 Forums

Transact-SQL (2000)

help with dynamic SQL

Author

Topic

Vignesh
Starting Member

20 Posts

Posted - 2006-04-26 : 08:06:47

hello all

let me explain my problem.

i have a table where i have list of all countries and their associated rates for different customers. so the table format is something like this

country code rates1 rates2 rates3...

where rates1,rates2 .. are the ratingid
now i have a stored procedure where i pass the ratingid as input parameter and i want to select the rates for Australia, US and UK for that particular rating id say rates1.

i tried something like this but its not working
declare @sql varchar(500)
declare @ratingid varchar(20)
declare @aus varchar(100)
set @ratingid='cc'
set @aus='Australia'
set @sql='select top 1 country, ' + @ratingid + ' as rates from ratelist where country=' + @aus
exec(@sql)

Thanks in advance
Vignesh

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-04-26 : 08:10:47

If u can, better to normalize the DB.

Srinika

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-26 : 08:15:21

use a PRINT statment to print out the sql string and verify.
You need to use 2 single quote to enclose your string. You would have notice it if you have the PRINT statement


set @sql='select top 1 country, ''' + @ratingid + ''' as rates from ratelist where country=''' + @aus +''''
print @sql

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-04-26 : 08:23:56

somthing like this

]
Create Table  #RateList 
(
Country varchar(10),
Rate1 Money,
Rate2 Money,
Rate3 Money
)

Insert #RateList 
Select 'Ind',1000.0,1200.0,1250.0 Union All 
Select 'UK',1200.0,1500.0,1750.0 Union All 
Select 'AUS',1500.0,1800.0,2050.0 Union All 
Select 'UAE',1800.0,2100.0,2250.0  

Select * from #RateList

Declare @CountryCOde Varchar(10)
Set @CountryCode = 'UAE'

Declare @RatingID Varchar(10)
Set @RatingID = 'Rate1'

Declare @QryString Varchar(800)
Set @QryString = 'Select Top 1 Country, ' + @RatingID + ' From #RateList Where Country = ''' + @CountryCode  + ''''
Exec (@QryString)

Drop Table #RateList

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

Vignesh
Starting Member

20 Posts

Posted - 2006-04-28 : 08:23:08

Thanks All. I tried It and its workin. U guys Rock

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources