Dynamic Sql query problem - 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 query problem

Author

Topic

ranjeetsingh_6
Posting Yak Master

125 Posts

Posted - 2007-03-21 : 05:29:27

Hi when i execute Below Query This give error mention by red color
plz help me.

declare @tablename varchar(100),@SQLQuery varchar(4000),@tempgrno varchar(100),@grnomber int,@grno varchar(100)
set @tablename='tbltgr'
set @grno='01797'
select @SQLQuery ='select @tempgrno=txtgr_no from'+' '+@tablename+' '+' where txtgr_no='''+@grno+''''
exec sp_executesql @SQLQuery, N'@c int output', @grnomber output
select @grnomber

Error

Server: Msg 214, Level 16, State 2, Procedure sp_executesql, Line 5
Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.

Ranjeet Kumar Singh

ranjeetsingh_6
Posting Yak Master

125 Posts

Posted - 2007-03-21 : 05:36:03

Hi
i have solve my problem.

Ranjeet Kumar Singh

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-21 : 05:57:48

May I guess?

declare @tablename varchar(100),@SQLQuery nvarchar(4000),@tempgrno varchar(100),@grnomber int,@grno varchar(100)
set @tablename='tbltgr'
set @grno='01797'
select @SQLQuery ='select @tempgrno=txtgr_no from'+' '+@tablename+' '+' where txtgr_no='''+@grno+''''
exec sp_executesql @SQLQuery, N'@c int output', @grnomber output
select @grnomber

For those of you having the same problem...

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-21 : 06:07:50

Also for easier reading, get rid of concatenated spaces and embed them directly into string

select @SQLQuery = 'select @tempgrno = txtgr_no from ' + @tablename + ' where txtgr_no = ''' + @grno + ''''

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources