Dynamic SQL help - 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

SQL Server Development (2000)

Dynamic SQL help

Author

Topic

LaurieCox

158 Posts

Posted - 2005-10-17 : 16:47:11

Hi,
I think I need to sp_executesql but I am finding the examples in BOL confusing.

Anyway:

I would like the table name (Budget) and field name (FiscalYear) to be dynamic in the following code:

declare @delimiter char
SET @delimiter = ';'
declare @YearList varchar(50)

SELECT @YearList = COALESCE(@YearList + @delimiter, '') + x.YearString FROM (SELECT distinct convert(char(4),FiscalYear) as YearString from Budget)x

select @YearList

if FiscalYear (or whatever the column is) has the following distinct values in the table Budget (or whatever the table is): 1999,2001,2003 then @YearList would have the following value:
'2001;1999;2003;'

Thanks for any help.

Laurie

karuna
Aged Yak Warrior

582 Posts

Posted - 2005-10-18 : 01:41:18

sp_executesql will expect the entire query to be passed as a single string. so your query should be single string something like this. The variable should be of nvarchar/ntext/text I think.

DECLARE @SQL nvarchar(100)

SET @SQL = 'Select * from TBLA'

Execute sp_executesql @SQL

Karunakaran

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources