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)  Passing column name to dynamic SQL

Author  Topic  Billc Starting Member 5 Posts Posted - 2001-04-10 : 15:55:59 I'm having a problem creating a dynamic sql version of robvolk's script for parsing a comma-delimited string into multiple rows. I encapsulated the script into a stored procedure as follows: CREATE PROCEDURE sp_UnpackList @NextName varchar(25) AS DECLARE @SQLStr varchar(400), SET NOCOUNT ON TRUNCATE TABLE SingleLetter SELECT @SQLStr = 'INSERT INTO SingleLetter SELECT MLSNumber,' SELECT @SQLStr = @SQLStr + 'NullIf(SubString('','' + ' + @NextName + ' + '','' , ID , CharIndex('','' , '','' + ' + @NextName + ' + '','', ID) - ID) , '''') AS Code ' SELECT @SQLStr = @SQLStr + 'FROM Tally, GAMLS_Property ' SELECT @SQLStr = @SQLStr + 'WHERE ID <= Len('','' + ' + @NextName + ' + '','') AND SubString('','' + ' + @NextName + ' + '','' , ID - 1, 1) = '',''' SELECT @SQLStr = @SQLStr + 'AND CHARINDEX('','' , '','' + ' + @NextName + ' + '','' , ID) - ID > 0' EXEC(@SQLStr) so that I can pass a column name in as @NextName. But when I call the SP with a valid column name from table Property like this, exec sp_UnpackList 'column3' I get the following error: "Insert Error: Column name or number of supplied values does not match table definition." I'm pretty sure that my syntax of escaping single quotes in the dynamic SQL is wrong, but don't see where. Am using SQL 7.0. Any help appreciated.