Why isn't this working?? - 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)

Why isn't this working??

Author

Topic

Jules_S
Starting Member

13 Posts

Posted - 2012-03-20 : 12:59:21

Hi all

I'm trying to do something that I thought should be fairly simple. In summary, I'm using a cursor (yes, I know...) to read rows from a control table. One of the columns of the table contains the name of a stored procedure, and for each row read from the control table, I want to execute the appropriate procedure.

So, I have the fetch command retrieving the value of this column into a variable called @proc. Within the cursor loop, and after some other code that works fine, I have this:

if @proc != null
begin
set @cmd = @proc
exec(@cmd)
end

N.B. @cmd is a re-usable varchar.

For some reason, this isn't working. If I test this by commenting-out the "if", "begin" and "end" then @cmd is set and the procedure is executed. But as soon as I un-comment those rows of code and try running it... nothing.

It must be something really obvious, but for the life of me I can't work it out!! Any ideas?

Many thanks

Jules

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-03-20 : 13:58:26

Try this:
set @cmd = 'exec ' + @proc

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-20 : 15:05:23

if @proc != null
wont work as != operator ignores NULL values under default conditions
you should be using IS NOT NULL instead
try


if @proc is not null
begin
set @cmd = @proc
exec(@cmd)
end

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Jules_S
Starting Member

13 Posts

Posted - 2012-03-21 : 06:11:54

Ah! That'll be it then... I knew it would be a schoolboy error - thankyou everyone for your help.

Jules
(hangs head in shame....)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-21 : 10:24:50

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-21 : 11:03:55

null is not equal to anything, including itself

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-21 : 12:16:39

quote:
Originally posted by X002548

null is not equal to anything, including itself

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

under default conditions

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources