Dynamic variable names

[robertkjr3d]

Ok here is the simple version of what my problem is:
declare @Field int
declare @Field2 int
declare @sql varchar(150)
set @Field = 2
set @Field2 = 2000
set @sql = 'select @Field' + Convert(varchar, @Field) + ' [Value]'
exec(@sql)

I get the error must declare the variable @Field2

It doesn't remember.

Why do I need this really is because of this situation:
(All of these variables are declared)

set @loop = 1
set @visits = 0
set @Field = 11
--Collect visits
while @loop = 1 and @Field <= 68 begin
set @loop = 0
set @command = 'if (@Field' + Convert(varchar, @Field) + ' is not null and @Field' + Convert(varchar, @Field + 1) + ' = ''T'' and @Field' + Convert(varchar, @Field + 2) + ' is not null) begin set @loop = 1 set @visits = @visits + 1 end'
exec(@command)
set @Field = @Field + 3
end

However I get the error, @loop, @visits, @Field not declared.

Is there anyway to handle a @ variable without knowing the complete name of it? Because I have variables passed to the procedure in the form of @Field1, @Field2, @Field3... and so on

 

[SQLDenis]

your variables are out of scope once you do an exec (SQL)
can you use a temp table and check the fields there?

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[robertkjr3d]

I did this. It is cumbersome, and requires me entering in all of them into a temp table.

The exec(@sql) function should not be out of scope. It should function much like the eval() in javascript. Or create an eval() in sql.

 

[SQLDenis]

exec is not a function
ltrim(), upper() and convert() are functions and those are available within an exec

example
declare @sql varchar(150)

set @sql = 'select getdate() '
exec(@sql)

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[robertkjr3d]

arguing symantics are we? Your definition of function is?

A function is a procedure that takes arguments (or lack there of) and returns or does something.

You mean because it does not return anything it is not a function? It still takes an argument and performs a task.

But neither here nor there... My point is that it would be nice to have a command like eval() which would be in scope, and not have to create a temp table.

 

[SQLDenis]

exec sp_who2
is exec a function here?

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[robertkjr3d]

umm yes... it takes the argument of a stored procedure and executes it

Function takes argument (or no arguments) and returns or performs something!

 

[robertkjr3d]

http://www.dictionary.com

8) Computer Science. A procedure within an application.

This is certainly true is it not?

Can you find a definition of function that doesn't fit? I asked you what your definition was.

 

[SQLDenis]

We are talking about SQL server functions here not procedural language functions

any function (AFAIK) in SQL server can be called with a select statement and exec can not be called like that

select LTRIM('wasabi ')
select abs(-200)
select dbo.MyOwnFunction()
select * from ::fn_helpcollations()

>>8) Computer Science. A procedure within an application.

in this case if you have a DB with 200 stored procedures are those procedures functions?

it doesn't matter what the definition is, the fact of the matter is that your variables are out of scope and that you could use a temp table to get around that problem



Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[robertkjr3d]

http://www.4guysfromrolla.com/webtech/020600-1.shtml

http://blogs.msdn.com/mrorke/archive/2005/10/24/484237.aspx

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=210272&SiteID=1

Apparently I'm not the only one that defies your language issue!

and the above links are only a few...

search for - "EXEC Function" sql - on google

 

[robertkjr3d]

Here is a more official article with the words "execute function" in the first bolded sentence.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlpro2k/html/sql00g7.asp

 

[gmmastros]

Well, that last article you mention gives you your answer.

The problem is, you can see the results of the stored procedure in the output window, but you can't directly capture the results of the stored procedure into your own code's variables.

The solution is to combine the EXEC command with the INSERT statement. The INSERT statement already has one ANSI variation, for which you can combine it with SELECT:

can you use a temp table and check the fields there?

You should probably *forget about* all this function business because it's not going to get you any closer to a solution. Until Microsoft implements your suggestion, you will have to do things that way that Microsoft (and Denis) suggest.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

This is what that article says
EXECUTE command and EXEC() function

EXEC is short for EXECUTE so what is it now? function or command?
declare @sql varchar(150)

set @sql = 'select getdate() '
execute(@sql)

from BOL

Syntax
Execute a stored procedure:

[ [ EXEC [ UTE ] ]
{
[ @return_status = ]
{ procedure_name [ ;number ] | @procedure_name_var
}
[ [ @parameter = ] { value | @variable [ OUTPUT ] | [ DEFAULT ] ]
[ ,...n ]
[ WITH RECOMPILE ]

Execute a character string:

EXEC [ UTE ] ( { @string_variable | [ N ] 'tsql_string' } [ + ...n ] )


If you want to call it a function then that's fine.
To me EXEC, SET, and DBCC are all commands

But so that this won't go on forever if you want to call it a function then that's fine and I will call it a function to ;-)

Now how are you going to deal with the variable problem? temp table?

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[robertkjr3d]

Oh yes... already did. But with 70 fields inputed, I didn't like it. It's ugly, but it works.

I was just having fun arguing the language issue.

 

[SQLDenis]

no problem haha
glad you worked it out

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/