Set local variables when EXEC-ing a sql String

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

General SQL Server Forums

New to SQL Server Programming

Set local variables when EXEC-ing a sql String

Author

Topic

firefly
Starting Member

5 Posts

Posted - 2006-11-23 : 11:14:51

Hey guys, needless to say I'm new at this.

What I'm trying to accomplish is to execute a SQL string via exec and inside it set the value of a local variable. I understand that I cannot do this the way I'm currently doing it because an Executed string runs in a scope of its own so local variables are invisible. And sure enough this is the error I get. So how do I make this work?

Code snip:


declare @ErrMessage as varchar(1000)
set @Sql = 'if exists ( select * from ' + @TargetTable + ' where (' + @ValueField + '=' + '''' + @NewValue + '''' 
				set @Sql = @Sql + ' and ' + @TagField + '= ' + '''' + @Tag + '''' + '))' + @CRLF
				set @Sql = @Sql + 'set @ErrMessage = ''Insertion could not be performed. ' + @NewValue + ' is already an entry in the table. '''

So what I want is check if a certain table has entries...what table? I don't know, there are tens that this check will apply to. And if that tavle has an entry that satisfies the where clause then assign the appropriate error message to @ErrMessage.

I understand sp_executesql might do the trick because it allows passing local params back and forth.

Any ideas on how to make this work? I appreciate the effort.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-11-23 : 11:31:31

[code]
declare @sql nvarchar(4000)
declare @ErrMessage varchar(1000)
set @Sql = 'if exists ( select * from ' + @TargetTable + ' where (' + @ValueField + '=' + '''' + @NewValue + ''''
set @Sql = @Sql + ' and ' + @TagField + '= ' + '''' + @Tag + '''' + '))' + @CRLF
set @Sql = @Sql + 'set @ErrMessage = ''Insertion could not be performed. ' + @NewValue + ' is already an entry in the table. '''

exec sp_executesql @sql, N'@ErrMessage varchar(1000) OUTPUT', @ErrMessage OUTPUT
[/code]

firefly
Starting Member

5 Posts

Posted - 2006-11-23 : 13:36:27

I applied this method to my code and it worked. Thanks alot.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-11-25 : 01:38:04

For more informations on dynamic sql, read this
www.Sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources