SQL equivalent of the "Eval" function of ASP/Javas

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)

SQL equivalent of the "Eval" function of ASP/Javas

Author

Topic

maloy
Starting Member

19 Posts

Posted - 2004-07-22 : 03:38:06

My situation is that my stored procedure has 20 input parameters named "@list1", "@list2", until "@list20". And for each of this value that is not null, I will call another stored procedure. I can simply do a long list of "if @list1 is not null" for @list1 to @list20, but trying to cut the code short and make it more readable, I try to do the following :

set @i=1
while @i <= 20
begin
set @current='@list'+ convert(varchar(2),@i)

if @current is not null
-- do something
set @i=@i+1
end

But I realised I can't get the value of @current to be the value of @list1 to @list20 as it evaluates to be a string "@list1", "@list2". etc instead.

Is there a SQL equivalent of the "Eval" function in ASP/Javascript that I can use here? Or is there another smarter method to loop through the parameters?

Thanks!
Maloy

Kristen
Test

22859 Posts

Posted - 2004-07-22 : 04:05:56

[code]
DECLARE @strSQL varchar(8000)
SELECT @strSQL = 'sp_who'
EXEC(@strSQL)
[/code]
Is that the type of thing you are after?

Kristen

maloy
Starting Member

19 Posts

Posted - 2004-07-22 : 04:59:16

I don't know, how that could be useful to this situation.
How do I loop over my input params without writing lengthy code?

maloy

Kristen
Test

22859 Posts

Posted - 2004-07-22 : 05:21:20

As you have it in your example, just replace "-- do something" with "EXEC(@strSQL)" where @strSQL represents the syntax to call the appropriate SProc

Kristen

maloy
Starting Member

19 Posts

Posted - 2004-07-22 : 08:21:46

It's not the 'do something' part that I'm after.

I want to use the @current so that I can loop over all the input params of my sproc, and then 'do something' ONLY IF the input param is not null.

Maloy

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-07-22 : 08:40:04

do a search in both these forums and in the articles (front page of this site) for passing and parsing CSV strings in your stored procedures.

you can accept 1 parameter, of type varchar, in which you enter the parameters seperated by commas:

1,4,6,9,8,3 .. etc ...

then parse that into a table variable, and off you go. I know in the last 2 days or so there has been a few posts about this.

- Jeff

maloy
Starting Member

19 Posts

Posted - 2004-07-22 : 08:50:20

I don't want help for the 'do something' part.

What I want is help for using @current- how to loop over the list of input params to my stored proc.
I'll use the 'do something' ONLY FOR those input params which are not null.

Maloy

Kristen
Test

22859 Posts

Posted - 2004-07-22 : 08:55:46

So you need to make @strSQL be
SELECT @strSQL = 'IF ''' + @current + '' is not null EXEC (''The Something You Want To Do'')'
and then do
EXEC(@strSQL)

where "@current" is some manipulation of your parameter - possibly the whole LOOP thing

Kristen

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-07-22 : 09:12:39

maloy --

i don't think i offered help on the 'do something' part. I think I offered help on how to pass multiple values to a stored procedure, and how to process those values w/o looping or having 20+ input parameters. Isn't that what your problem is? what happens if you need 21 values passed, and you have only 20 parameters? you've already experienced problems with accessing those params. the solution is to pass them all in 1 string of values and to parse that string into a table of values, which SQL Server can natively work with efficiently w/o looping if you desire.

Read more carefuly, and maybe take the advice given and do some searching and see what you can find. Try to think outside the box a little. your problem is not "how to dynamically access a variable", rather it's how to efficiently pass multiple values into a procedure.

- Jeff

maloy
Starting Member

19 Posts

Posted - 2004-07-23 : 03:45:38

Sorry Jeff,
I replied to Kristen- not you, and my browser froze- so there was an inadvertent double post.

I'm trying out what u've said. I found a good link at: http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm

<quote>
So you need to make @strSQL be
SELECT @strSQL = 'IF ''' + @current + '' is not null EXEC (''The Something You Want To Do'')'
and then do
EXEC(@strSQL)

where "@current" is some manipulation of your parameter - possibly the whole LOOP thing

Kristen
</quote>
This doesn't work Kristen, I tried this before I posted on the forum. Remember that @current is out of scope in EXEC or sp_executesql, as they run their arguments in a self-contained batch.

Thanks guys for all ur help!!
Maloy

Kristen
Test

22859 Posts

Posted - 2004-07-23 : 04:33:24

Sorry not to have understood your problem at the get-go. I was thrown by the "SQL equivalent of EVAL". I must stop reading the users perceived answer and cut to their problem!

You will need to use EXEC(@strSQL) or sp_executeSQL so that this is "dynamic"

To cross the "scope" either some sort of TABLE is needed to pass the data, or some messing around with sp_executeSQL's parameters.

I reckon you could do the latter (seeing as a table is just going to be a table-for-table's sake, and inefficient).


SELECT @strSQL = 'IF @list' + convert(varchar(2), @i) 
       + ' IS NOT NULL EXEC (''The Something You Want To Do'')'
EXEC sp_executeSQL @strSQL, N'@list1 int, @list2 int ...', @list1, @list2 ...

Note that sp_executeSQL doesn't give a tinker's cuss that some of its parameters are unused.

But given what a PITA this appears to be I would look for an alternative solution. If you want to tell me what the problem is you are trying to solve (i.e. why you currently have 20 parameters that need to be processed in a loop) I'll put my thinking-cap on.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources