February 26, 2002 at 1:43 am
I have a column "equation" in table "control". Equation is a string like "1+2". I can evaluate all kind of functions with
exec("select equation from control where idnr=" + idnr)
But how to store results to t-sql variable or to table ? Select into didn't work, neither select myvar=exec("blaah...").
I'm not interested in only to read results from the screen. Stored procedure must know these results also.
Thanks,
CA
February 26, 2002 at 4:20 am
Try something like this:
CREATE TABLE #temp(equation varchar(20))
INSERT INTO #temp(equation)
exec("select equation from control where idnr=" + idnr)
Or use a permanent table to insert into.
Robert Marda
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
February 26, 2002 at 5:01 am
Why do dynamic at all for this. Unless your example is no fully showing all I need to know you should be able to do
DECLARE @equation VARCHAR(20)
DELCARE @idnr whatever
SET @idnr = whatever
SELECT @equation = equation FROM control WHERE idnr= @idnr
Or something similar to suit your needs.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
February 27, 2002 at 2:36 pm
Why do we use dynamic equations at all? I don't know. Maybe because nobody has rights to change The Stored Procedure.
My example was wrong. Of course it should be
something like:
exec("select " + @equation + "from control where idnr=" + @idnr).
Thanks rmarda. Permanent tables work, I knew it but forgot that. I didn't know that temp -tables work also, if they are already created. I didn't try, because select into did not work.
If I have temp in RAM, that's fast enough. Permanent tables are too slow, even if checkpoint-value is small. System must decide in 50 ms, if tested product is OK or not. Stored Procedure is about 1000 rows long (many checks and so on).
February 27, 2002 at 3:35 pm
Good question. I have begun to wonder this myself. When I started at bigdough.com almost all stored procedures used dynamic SQL. Gradually, I have been converting them to static SQL. I find that they almost always run faster static and have recently learned how permissions are better controlled when dynamic SQL is avoided.
Now it is an enjoyable challenge for me to see how many ways I can develop a query and avoid dynamic SQL.
Robert Marda
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply