Field Names as variables in stored procedures 1

[Dynapen]

Here the problem-

I am building a site that will use stored procedures to return record sets from the server to the web using ASP. The problem is that I want to allow my users to sort those reports based off the field headers. Now, if I was using a view instead of a stored procedure I could simply pass whatever field the user selected back to the SQL statement as a variable, and when processed the server wouldn't know the difference. i.e.

SQL = "Select * from employees where id = '" & strid & "' order by " & field_var & "" & dir_var & ""

But now I am trying to do it using a stored procedures. So that I can simply call a exec command on the stored procedure and pass the field to sort by and the direction as a parameter in the exec command i.e.

Exec sp_employees field, dir

To do that I have to have this in my procedure.

Create procedure employees
@field varchar(10)
@dir varchar(4)
@id varchar(4)
as
Select * from employees where id = @id
order by @field @dir

What happens when I try to save this procedure on the server is the following error.

Error 1008: The SELECT item identified by the ORDER BY number1 contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name.

Now I would understand it if I got a error after I tried to execute the procedure becuase the format of the value I put into the variable was wrong, but it won't even let me create the procedure with a variable in the order by clause.

Any ideas would be greatly appreciated. I am completely lost on this one.

 

[tlbroadbent]

Create and execute a dynamic SQL statement.

Create procedure employees
@field varchar(10)
@dir varchar(4)
@id varchar(4)
as

Declare @sql varchar(100)

Set @sql='Select * from employees' +
' where id = ' + @id +
' order by ' + @field + ' ' + @dir

Exec(@sql)
Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[Dynapen]

I could do that, but then I don't get the advantage of the stored procedure being in the memory on SQL Server. The example is simple but some of the select statements have table joins across 10-15 tables, and use various case and if statements in them to format the recordset. I need the stored procedure on the server already so that I get the speed boost over the referencing the view.

 

[tlbroadbent]

Use sp_executesql to execute the query. sp_executesql is more efficient than because it generates execution plans that are more likely to be reused by SQL Server.

exec sp_executesql @sql

See the documentation online at:

http://msdn.microsoft.com/library/en-us/acdata/ac_8_con_04_9uek.asp

Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[Dynapen]

But with the sp_executesql I still am passing a SQL statement to the server, and not really executing a procedure that it already has stored in memory. And I now have to write all the SQL for each different report into the page itself, or into a table to be called up by the page.

Right now I have a different page for each report. If i can get the stored procedure to see the variable as a column name, not a column number, then I can do all my reports on one page. Then I add new reports by creating a new procedure in the DB, listing that procedure in a table that the page uses to display the different reports that are available, and all i have to pass is 2 parameters instead of a huge SQL string. (Some of the strings could be 200-300 lines long.) And I get the advantage of the server holding the execution plan in memory so I get the speed as well, all while taking up less space in both web file and the DB.

Basically, I need to know how to tell the DB that the variable after the ORDER BY should be taken as a column name, not a column number. (i.e. First_Name vice 1)

 

[tlbroadbent]

You can use sp_executeSQL in your existing SP. Just use it in place of the Execute statement.

I'd like to note that the emphasis on the stored procedure precompilation ignores the need for query optimization. Query optimation is much more important to performance.

When you change the order by clause, a different query execution plan may be created. For example, ordering by a different column may change indexes selected or the order of query steps.

Changing the direction of the sort may impact the query plan because SQL can traverse an index forward or backward as needed. Thus it may avoid sorting the result set after the selection because it is sorted as it created.

Of course, query plans depend on many factors and the Order By clause may not have much impact. It is good practice to review query plans for various queries to see how SQL is handling them. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[Joiner]

Terry - I have a situation where the user is allowed to build their own WHERE clause. Would you recommend building the SQL string to support this in a stored procedure and executing with sp_executeSQL? Other than a single storage place, are there any advantages to using a stored procedure rather than simply executing this string from the client using sp_ExecuteSQL?
Thank you for your help.

 

[tlbroadbent]

If the columns in the select list, criteria or order by clause change, I don't perceive any advantage in using a stored procedure. Changing the columns will chnage the query execution plan. If the columns were static and only the criteria changes, then the query plan won't change. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[Joiner]

Thanks again.

 

[Dynapen]

Terry-
I agree with you on the fact that query optimization is the most important part to speeding things up. Unfortunately I don't get to decide the way the data is stored in the DB, i just have to work with it from my end, and I don't feasibly have the time to reorganize the entire database to make it more friendly to my needs.

So here's the question. If you had a large query to pull and then display as a recordset to a client using ASP and SQL Server, what is the fastest way to do it. Is it a procedure, view, string in the code........I am open for anything on this. Also, I got the variable to work using the dynamic SQL, but I was curious about if it can be done without the dynamic SQL so that I am not creating a new variable in the procedure to pass everything to.

Thanks for your help on this.