Writing on the Fly Queries - SQL Server Forums

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

Transact-SQL (2000)

Writing on the Fly Queries

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-07-21 : 09:44:52

Rick writes "I need to create a query that can be created "on the fly" using MS SQL 2000.

What I mean by that is that I need to parameterize the column names. So the user can select which columns they want. The results will be fed into a UDF.

**You Don't Need to read below unless you need further info

The UDF goes row by row and calculates a statiscal value from the two columns. The user defined function looks basically like this:

Alter FunctionName
Returns
VarChar(20)
AS
Declare Variables Part...
IF tableName = 'table'
Begin
DECLARE field_cursor CURSOR FOR
SELECT Col1, Col2 From tableName
END
OPEN field_cursor

- I need to paramertize the column names where it says, "SELECT Column1, Column2 from Table" and parameterize the column names."

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-07-21 : 10:39:35

U may need to use dynamic SQL (But the Senior members of this Forum advices to avoid Dynamic SQL as much)
There u can pass the table names, where criteria, ... and create the SQL Query and execute it.

There is another non recommended way (unfortunately, this is having security threats as well) for ur requirement, to create the Query in the front end and pass to the SQL server.

Also, the cursor u r using is also not recommended.

For Dynamic SQL refer:
http://www.sommarskog.se/dynamic_sql.html.

Srinika

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources