Using DML with variables - 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)

Using DML with variables

Author

Topic

ryanlcs
Yak Posting Veteran

62 Posts

Posted - 2009-10-28 : 21:27:31

Hi

I have some DML of Insert, Delete. With then statement, I want to execute a with tables that are assigned within a variables.

Declare @table_Name_Status varchar(500)
Set @table_Name_Status = 'Table_Name_Status'
Delete from @table_Name_Status

But it still prompt me error:
Must declare the table variable "@table_Name_Status".

How to fix this?
Thanks.

rocknpop
Posting Yak Master

201 Posts

Posted - 2009-10-28 : 21:51:55

use dynamic query:

Declare @table_Name_Status varchar(500), @Sql varchar(1000)
Set @table_Name_Status = 'Table_Name_Status'
Set @Sql='Delete from ' + @table_Name_Status
Exec(@sql)

--------------------
Rock n Roll with SQL

ryanlcs
Yak Posting Veteran

62 Posts

Posted - 2009-10-28 : 21:59:46

I got another query with a return variables, and I did the following, and hit this error:
Must declare the scalar variable "@process".

Declare @table_Name_Status varchar(500),
@process varchar(100),
@strSQL varchar(500)
Set @table_Name_Status = 'Table_Name_Status'

set @strSQL = 'Select @process = process from ' + @table_Name_Status
print @process
exec(@strSQL)

How to make it correct?

ryanlcs
Yak Posting Veteran

62 Posts

Posted - 2009-10-28 : 22:37:06

I got this, but hit with this error:
Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.

Declare @table_Name_Status varchar(500),
@process varchar(100),
@strSQL varchar(500)
Set @table_Name_Status = 'Table_Name_Status'

set @strSQL = 'Select @process = process from ' + @table_Name_Status
Exec sp_executesql @strSQL,N'@process varchar Output',@process Output
print @process

Please help.

rocknpop
Posting Yak Master

201 Posts

Posted - 2009-10-29 : 00:21:47

All parameters for sp_executesql must be nvarchar. Make @process nvarchar and then test.

--------------------
Rock n Roll with SQL

ryanlcs
Yak Posting Veteran

62 Posts

Posted - 2009-11-05 : 22:36:20

Is there another by not using the dynamic query as this:

Declare @table_Name_Status varchar(500), @Sql varchar(1000)
Set @table_Name_Status = 'Table_Name_Status'
Set @Sql='Delete from ' + @table_Name_Status
Exec(@sql)

Because the output of the query will go into a cursor, and with dynamic query, I cant "save" the output into a cursor.

Please advice.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources