Dynamic Select
-
I have the following code i call with Execute GetFacilities UserJoe, English:
CREATE PROCEDURE GetFacilities
@userID NVARCHAR(100),
@language NVARCHAR(100)
AS
SELECT Name,
(SELECT @language FROM lkpfacilitytype WHERE ID = Type) AS Type,
Address1,
Address2,
City,
State,
ZIP,
Country,
Phone,
Fax,
WebSite
FROM tblFacilities
WHERE userID = @userid
GO
The table lkpfacilitytype has the format ID (int 4), English (nvarchar 100) and Spanish nvarchar(100), e.g:
ID English Spanish
1 House Casa
2 Hospital Hopital
3 Emergency Emergencia
When i run the command Execute GetFacilities UserJoe, English I get all results as expected, but instead of getting House, Hospital or Emergency for (SELECT @language FROM lkpfacilitytype WHERE ID = Type) AS Type I get what I passed in, which is English.
Any ideas how I can make (SELECT @language FROM lkpfacilitytype WHERE ID = Type) AS Type pull the value from column English or Spanish from lkpfacilitytype , and not return the word 'English' or 'Spanish' instead?
I tried [@language] but that doesn't work. Can anyone help please?
-
I hope this helps you
CREATE PROCEDURE GetFacilities
@userID NVARCHAR(100),
@language NVARCHAR(100)
AS
DECLARE @sql_str VARCHAR(1000)
SET @sql_str = 'SELECT Name,
(SELECT ' + @language + ' FROM lkpfacilitytype WHERE ID = Type) AS Type,
Address1,
Address2,
City,
State,
ZIP,
Country,
Phone,
Fax,
WebSite
FROM tblFacilities
WHERE userID = ''' + @userid + ''''
EXEC (@sql_str)
GO
Prasad Bhogadi
www.inforaise.com -
Worked like a charm!! Thank you so much.
-
Not to be picky, but if you are not already aware of the downside of dynamic sql you might want to read this.
http://www.sommarskog.se/dynamic_sql.html
On Erland's site there is also another interesting article
http://www.sommarskog.se/dyn-search.html
HTH
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Yeah seems to be a great article.
Prasad Bhogadi
www.inforaise.com -
This would do away with dynamic sql.
SELECT f.Name,
(CASE WHEN @language = 'English'
THEN t.English ELSE t.Spanish END) as 'Type',
f.Address1,
f.Address2,
f.City,
f.State,
f.ZIP,
f.Country,
f.Phone,
f.Fax,
f.WebSite
FROM tblFacilities f
INNER JOIN lkpfacilitytype t
ON t.ID = f.Type
WHERE f.userID = @userID
Or, if there are only two languages then
IF @language = 'English'
BEGIN
SELECT f.Name,
t.English as 'Type',
f.Address1,
f.Address2,
f.City,
f.State,
f.ZIP,
f.Country,
f.Phone,
f.Fax,
f.WebSite
FROM tblFacilities f
INNER JOIN lkpfacilitytype t
ON t.ID = f.Type
WHERE f.userID = @userID
END
ELSE
BEGIN
SELECT f.Name,
t.Spanish as 'Type',
f.Address1,
f.Address2,
f.City,
f.State,
f.ZIP,
f.Country,
f.Phone,
f.Fax,
f.WebSite
FROM tblFacilities f
INNER JOIN lkpfacilitytype t
ON t.ID = f.Type
WHERE f.userID = @userID
END
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply