exec a local variable in sp ? - 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)

exec a local variable in sp ?

Author

Topic

yipchunyu
Yak Posting Veteran

80 Posts

Posted - 2003-03-21 : 06:12:06

hi guys,
I am writing a sp that needs to stored the sql first and exec it later. so, i try to stored the sql into a local varialbe. however, i don't know how to exec it as the exec command won't work.
the code are as followings and hope your guys can give me some tips on that. i tried a lot but still can't make it works.

CREATE PROCEDURE Agent_GetCompareProductSet
@PlanSet varchar(300),
@Option varchar(1),
@IssAge int,
@SexInd varchar(1),
@SmkInd varchar(1),
@CashValue varchar(1) = '%',
@GteCashValue varchar(1) = '%',
@PrdNature varchar(1) = '%'

AS

DECLARE @SQL as varchar(5000)
DECLARE @strIssAge as varchar(3)
set @strIssAge = Cast(@IssAge as varchar(3))

set @SQL = ('SELECT ProductInfo.*, ProductFeatures.*
FROM ProductInfo INNER JOIN ProductFeatures ON ProductInfo.link_ind = ProductFeatures.link_ind
WHERE pln_cd in ('+@PlanSet+') and iss_age_min <= ' +@strIssAge +
' and iss_age_max >= ' + @strIssAge +
' and (sex_ind = ''B'' OR sex_ind = ''' +@SexInd+ ''') ' +
' and (smk_ind = ''0'' OR smk_ind = '''+@SmkInd+''')' +
' ORDER BY ProductInfo.pln_cd, ProductFeatures.type DESC, ProductFeatures.line_num')

if (@Option = '1')

print @SQL

-- exec ???

else if (@Option = '2')

SELECT ProductInfo.*, ProductFeatures.*
FROM ProductInfo INNER JOIN ProductFeatures ON ProductInfo.link_ind = ProductFeatures.link_ind

WHERE ((iss_age_min <= @IssAge) and (iss_age_max >= @IssAge))
and (sex_ind = 'B' OR
sex_ind = @SexInd) AND (smk_ind = '0' OR
smk_ind = @SmkInd)
and (prd_nature like @PrdNature) and (cash_value like @CashValue) and (gte_value like @GteCashValue)

ORDER BY ProductInfo.pln_cd, ProductFeatures.type DESC, ProductFeatures.line_num
GO

nr
SQLTeam MVY

12543 Posts

Posted - 2003-03-21 : 07:08:11

exec (@sql)

also see sp_executesql

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

yipchunyu
Yak Posting Veteran

80 Posts

Posted - 2003-03-21 : 23:47:25

thx a lot

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources