statement with parameter - formatting issue

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)

statement with parameter - formatting issue

Author

Topic

havelock
Starting Member

2 Posts

Posted - 2008-06-06 : 14:01:03

Hi i can not seem to get the correct formatting/syntax for this statement, its the @tp var (at is throwing it off) it has to be a string in the select statement, any suggestions? I've tried things like:
Clients.ID="'@tp'"
Clients.ID="'+@tp+'"
Clients.ID="')+@tp+('"
Clients.ID="')+(select @tp)+('"

here is the code (thanks):

declare @tp int

set @tp = 3012

select 'BMI', Result, ExamDate from openquery ([HEALTH REPORTER],'SELECT BMI AS Result, AppraisalDate AS ExamDate FROM Appraisals, Clients WHERE Clients.ID="'@tp'" AND Appraisals.ClientId = Clients.ClientID AND not Appraisals.BMI is null')

havelock
Starting Member

2 Posts

Posted - 2008-06-06 : 15:50:52

solution:

DECLARE @OPENQUERY nvarchar(70), @TSQL nvarchar(180), @LinkedServer nvarchar(20), @VAR nvarchar(7)

SET @VAR = '3012'

SET @LinkedServer = '[HEALTH REPORTER]'

SET @OPENQUERY = 'SELECT ''BMI'', Result, ExamDate FROM OPENQUERY('+ @LinkedServer + ','''

SET @TSQL = 'SELECT BMI AS Result, AppraisalDate AS ExamDate FROM Appraisals, Clients WHERE Appraisals.ClientId = Clients.ClientID AND not Appraisals.BMI is null AND Clients.ID= ''''' + @VAR + ''''''')'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources