need help Dynamic SQL - 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 2005 Forums

Transact-SQL (2005)

need help Dynamic SQL

Author

Topic

fh200300us
Starting Member

28 Posts

Posted - 2008-02-08 : 11:17:02

I have the D-SQL like thisALTER PROCEDURE [dbo].[sp_getColumnNameList]
-- Add the parameters for the stored procedure here
@tablename nvarchar(50)='Fix'

-- RETURNS nvarChar(500)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @StrSQL nvarchar(500)
declare @params nvarchar(500)
declare @StrcolumnList nvarchar(500)

Set @StrSQL = '

SELECT @strColumnName = @strColumnName + COLUMN_NAME +' + ''' , '' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME ='''+ @tablename +'''' +

' SELECT @columnList= @strColumnName SELECT @columnList '
select @params = N'@columnList nvarchar(500) OUTPUT, ' +
N'@strColumnName nvarchar(500)'
print @StrSQL

EXEC sp_executesql @StrSQL,@params, @columnList=@StrcolumnList OUTPUT

the error like this Msg 8178, Level 16, State 1, Line 0
Parameterized Query '(@columnList nvarchar(500) OUTPUT, @strColumnName nvarchar(500' expects parameter @strColumnName, which was not supplied.

What is the problem?

Thanks,

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-02-08 : 11:35:09

There are several issues with what you posted. Here is a simpler solution:


ALTER PROCEDURE [dbo].[sp_getColumnNameList] 

@tablename nvarchar(50)='Fix'

AS

declare @strColumnList nvarchar(500)
select @strColumnList = coalesce(@strColumnList + ', ' + column_name, column_name)
from	information_schema.columns
where	table_name = @tableName
order by ordinal_position

select @strColumnList

go

Be One with the Optimizer
TG

fh200300us
Starting Member

28 Posts

Posted - 2008-02-08 : 11:43:25

Thanks you very much.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources