Comparison with variables in Stored Procs

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)

Comparison with variables in Stored Procs

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-12-06 : 10:04:15

Alessandra writes "CREATE PROCEDURE [DBO].[FOO_2]
@foo varchar(2)
AS
set @foo = 1
EXEC
(....
'if '+@foo+ ' = 2
begin
update #temp2 set DiaFev = (Select x from #temp where code = @code)
update #temp2 set ToFev = (Select x from #temp where code = @code)
end ')
go
There are any restriction with an update command or with a cursor in comparisons?
I'm trying execute the statement above and the SQL isn't respecting the condition.
It executes both lines."

shankarc
Starting Member

37 Posts

Posted - 2001-12-06 : 12:58:21

When i read your question, several questions arise in my mind!
1. Why you declare @foo as varchar if it's going to hold a number
2. Why you set @foo to 1 and check "if @foo = 2"? (this always evaluates to false)
3. why you are using dynamic SQL?
you can directly execute the statement.
4. Where you are create #temp & #temp2 tables?

Is that you have written part of the code or the whole procedure? If it's only a part of the code, can you get the whole script of the procedure?

nr
SQLTeam MVY

12543 Posts

Posted - 2001-12-06 : 13:05:06

'if '+@foo+ ' = 2
begin
update #temp2 set DiaFev = (Select x from #temp where code = @code)
end
else
begin
update #temp2 set ToFev = (Select x from #temp where code = @code)
end '

would agree with shankarc about not needing dynamic sql but I guess there is more to this.

with dsql it is usually better to create a variable to hold the command - then you can print it out before executing it for debugging.
Even if you want to use dsql here you could test the variable when creating the command rather than inside the exec.
and @code won't be defined inside the exec.

set @cmd = 'update #temp2 set ' + case when @foo = '2' then 'DiaFev ' else 'ToFev' end + ' = x from #temp where code = ' + @code

exec (@cmd)

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

Edited by - nr on 12/06/2001 13:14:28

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources