Dynamic SQL question - 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)

Dynamic SQL question

Author

Topic

lawyer
Starting Member

5 Posts

Posted - 2003-08-04 : 11:59:10

I'm experimenting with Dynamic SQL a bit. I've learned the basics from a couple postings on this site, but am having a problem.

I have several tables that I want to be able to search using the same stored procedure. I plan to pass the table name as a parameter and I want to filter on the event date. The non-dynamic version of the SQL statement is this:

SELECT * FROM CALENDAR WHERE @XDate > EventDate

Running that query returns 67 records. Running the one below against the same table returns none. BTW, it seems I have to pass the column name (EventDate) as a parameter, because when I attempt to append that name to the string, Query Analyzer tells me that EventDate is not a valid column name (I have no clue why). Any and all help is appreciated.

Here is my procedure:

DECLARE @XDate smalldatetime
DECLARE @SQL varchar(100)
DECLARE @ColName varchar(50)
DECLARE @TName varchar(50)

SET @XDate = '6/1/2001'
SET @ColName = 'EventDate'
SET @TName = 'Calendar'

SET @SQL = ''
SET @SQL = @SQL + 'SELECT * FROM '
SET @SQL = @SQL + @TName
SET @SQL = @SQL + ' WHERE ' + convert(varchar,@XDate,101) + ' > ' + convert(varchar, @ColName, 101)

Exec(@SQL)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-08-04 : 12:30:40

You are converting EventDate inside the string and not after execution. Shouldn't you be running this instead:

SET @SQL = ''
SET @SQL = @SQL + 'SELECT * FROM '
SET @SQL = @SQL + @TName
SET @SQL = @SQL + ' WHERE ' + 'CONVERT(VARCHAR(50), ' + @ColName + ', 101) + ' > ' + 'CONVERT(VARCHAR(50), ' + @XDate + ', 101)'

Tara

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-08-04 : 12:32:39

Assuming that your column name is fixed (i.e. you will be checking all your tables against a column with the same name, EventDate) :


SET @SQL = ''
SET @SQL = @SQL + 'SELECT * FROM ' +@TName + '  WHERE  convert(datetime, ''' + convert(varchar(10), + @XDate, 101) + ''', 101) > convert(datetime, ''EventDate'', 101)'
print @sql

alternatively you could replace the hard coded column name with a variable that you could pass to your SP.

----------------
Shadow to Light

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-08-04 : 12:34:24

I did not copy your solution honest!