stored procedure/user defined function param question

[PreacherUK]

Hi guys,

I'm planning an application that will allow a user to select their choice of fields from a table into a spreadsheet/report.

I'm still very much an access user/programmer but have been using SQL more recently (so my thinking is probably not completly SQL orientated and I'm doing things the wrong way round) therefore any pointers from the SQL gurus would be most appreciated.

What I thought I would do is have a Vb front end that allows users to check a number of tick boxes selecting their chosen fields, this is sent to a table in SQL.

Then an sp/udf is executed that will build a query to return the data requested. Something like this:

Exec DynamicSQL (Select * from UserChoiceTable)


Would this work? Is there a more established way of doing this, building dynamic SQL strings?

 

[skuhlman]

That's pretty close to how I did it when I first needed to accomoplish something similar. Where I ran into problems was with the limit of parameter lengh of 255 characters when calling the stored procedure. Often my select statement was much longer, so I couldn't pass the entire statement.

Now I've started doing it a couple of different ways based upon the situation.

Method 1) pass only the variable pieces of information to the stored procedure (table name, field list, where clause...) and have the SP build the select statement with all of it's joins and other clauses based upon known common usages combined with the variable pieces passed. Once the SP has built the syntax, it then executes it and returns the record set.

Method 2) build the select statment in its entirety and store it to a text field within a table in the database. I then pass the key of that record to the stored procedure which in turn pulls the text field having the key passed, and executes the string found in the text field. Again, the record set is returned to the calling application.

I've found that you can get quite a flexible data set out of one SP based upon passing it a wide variety of information in small pieces.

 

[PreacherUK]

I can see how to do method one in my head (kudos by the way, smart idea) but I don't yet have the experience to see how the second method would be done (placing the whole sql string into a tables field I can see) , I'm not too sure how I'd go about the second part.

Would you happen to have any example code?

 

[donutman]

With a VB frontend, there's not much point in having the server do the field selection. Either build the query in VB and execute it as a commandtext type commandobject or retrieve the entire table and do the filtering when you either create the spreedsheet or the report.
Building dynamic SQL statements to be run by Stored Procedures defeats one of the main reasons for creating a SP...precompiled T-SQL.
-Karl

 

[PreacherUK]

I was under the impression that as I will probably be dealing with a very law number of records that it would be better to let the server take the strain than someones out of date desktop PC.

Is there a preferred way of doing this?

 

[donutman]

Based upon the users choices, build an SQL statement (in VB) that will select the data that you want. Then execute that SQL statement via the command object in VB. You can write code in VB to populate an excel spreadsheet. It is no less efficient doing it this way as opposed to building a dynamic SQL statement on the server and it is more clear to a "reader" of a program if all the code is in one place.
-Karl