Create Query on the Fly

you can try this

declare @yourquery varchar(200)

set @yourquery = 'Select ... from yourtable where yourfield = ' + @yourparameter

exec @yourquery

No this won't work.  I need to specify the column only.  Not in the where clause.

DECLARE @SQL varchar (1000)

SET @SQL = 'SELECT ' + @ColumnNmeVariable + ' FROM Table WHERE Wahetver = ...'

EXEC (@SQL)

That was only an example, I mean

declare @yourquery varchar(200)


set @yourquery = 'Select' + @yourvariables + ' from yourtable where yourfield = ' + @yourparameter

exec @yourquery

where in your @yourvariables you put
set @yourvariables = 'column1, column2, column...'

I tried to incorporate what you two are saying (into testing it as a stored procedure).  What is wrong with this syntax.  I am getting an error saying, "Invalid operator for data type" Please advise.

Create            PROC dbo.Test(@ColumnNmeVariable text)
AS
DECLARE @SQL varchar (1000)
SET @SQL = 'SELECT ' + @ColumnNmeVariable + ' From Table'
EXEC (@SQL)

This is just a basic syntax error; I am not sure if it will work yet.  Thanks again.

Create            PROC dbo.Test(@ColumnNmeVariable varchar (1000))
AS
DECLARE @SQL varchar (1000)
SET @SQL = 'SELECT ' + @ColumnNmeVariable + ' From Table'
EXEC (@SQL)

sorry,didn't explain... you can not append text in the query just like that .... the correct type is varchar...

However, you have to know that you are having a BIG gateway to SQL injections by allowing your users input in this way... most especially that they are passing the column names back!

Einstine98 is right you have to declare @columnNmeVariable as varchar not as text.

The test stored procedure is working.  

When I put this in the UDF, I get an error saying "Line 42: Incorrect syntax near 'SELECT '"

This is what the UDF Function looks like:
Alter FunctionName
Returns
VarChar(20)
AS
Declare Variables Part...
IF tableName = 'table'
Begin
DECLARE field_cursor CURSOR FOR
'SELECT ' + @Col1 + ', ' + @Col2 + ' From tableName' --*Right here is the error
END
OPEN field_cursor

The function loops through each value in the two specified columns.  Please advise

DECLARE field_cursor CURSOR FOR
'SELECT ' + @Col1 + ', ' + @Col2 + ' From tableName' --*Right here is the error

will not work... if you are building a query on the fly, the whole of your code has to be inside it...

DECLARE @SQL varchar (1000)

SET @SQL = '
DECLARE field_cursor CURSOR FOR
SELECT ' + @Col1 + ', ' + @Col2 + ' From tableName' --*Right here is the error

should work... however you can only open this cursor from within the same query and NOT the CALLING query

Einstine & others,

Einstine, I used your revised query.  The  query will save, but not run. I get an error A cursor with the name 'field_cursor' does not exist." I think you have the field cursor as a string.

Please advise

This is what I have now:
Alter FunctionName
Returns
VarChar(20)
AS
Declare Variables Part... --Including @SQL varchar (1000)
IF tableName = 'table'
Begin
SET @SQL = '
DECLARE field_cursor CURSOR FOR
SELECT ' + @Col1 + ', ' + @Col2 + ' From TableName'
END
OPEN field_cursor

I need to leave soon for home, I may have to respond to other ideas tomorrow- sorry for that.  Thanks a lot!
 

Can you put what you want to do in words?

You have to open the cursor from inside the string @SQL.

Alter FunctionName
Returns
VarChar(20)
AS
Declare Variables Part... --Including @SQL varchar (1000)
IF tableName = 'table'
Begin
SET @SQL = '
DECLARE field_cursor CURSOR FOR
SELECT ' + @Col1 + ', ' + @Col2 + ' From TableName
OPEN field_cursor'
END

Einstine,

Thanks for your time! I will try this as soon as I get back into the office.

What I am doing is I am using a UDF that calculates a statistical value. The UDF calculates goes row by row for two specified columns.  I don't want to create a new UDF for every two column relationship.  I need the report user to be able to select which columns of data they want compared.  The parameter results will be fed from MS Reporting Services into the UDF.  

BTW,  Does "Open field_cursor" go first or "End" ?  I will try it...

if you are going to apply the same set of clalculation on the two values within the two columns, do this...

SELECT dbo.fnYourfunctionName (Col1, Col2)
FROM Table
where whatever

Then in fnYourfunctionName do this

CREATE FUNCTION FunctionName (@Value1 integer, @Value2 integer)
Returns Varchar (20)
AS

RETURN @Value1 * @Value2

this way you do your processing for each row

There are variety of calculations that are compiled at the end.  Will this last method still work?  Will the previous do the same (what is the difference)?

The UDF that I am using calculates the statistical value based on the entire rows worth of data.  I can't include the whole function here, because it is propietary.  After the "end" of what I wroteis where it starts it's row by row calculations.

What parts of the code do you need.  I may be able to provide it to you.    I'll do what we need to have it work.

Won't the code you provided at 07/10/2006 05:18PM EDT work?

I think my last posting is best suited for what you are trying to do as per the information you provided.. to be able to give you an alternate solution, you need to tell me what processing are you trying to do on the data...

you don't have to give me the exact logic, but what kind of operations?

Column1 *Column2 - 3? are they all arithmetic?

A cursor would work, but would also be really slow...

I tried the code you provided on 7/10 5:18 & received the error:"A cursor with the name 'field_cursor' does not exist"

Speed is not an issue.  I want this to work with as little work as possible, so I prefer not to change it dramatically by pulling it from a View.  Below is the function, I took out the propietary stuff, the comments have the test script.  If we can get this to work, the real function should work as well (you can modify the column names to work on your computer if you want)

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

/*
Select dbo.XYZ('Table', 'Col1', 'Col2')          *TestScript is here
*/

ALTER                      FUNCTION XYZ(@table_name varchar(15), @Col1 varchar (20), @Col2 varchar (20))
RETURNS
Varchar(20)

AS

BEGIN
DECLARE @field_working decimal(20,2), @field1_working decimal(20,2), @field2_working decimal(20,2),
@product decimal(20,2), @counter decimal(20,2), @one decimal(20,2), @Two decimal(20,2), @Three decimal(20,2),
@four decimal(20,2), @Final float, @SQL varchar(1000)
IF @table_name = 'Table'

Begin
SET @SQL = '
DECLARE field_cursor CURSOR FOR
SELECT ' + @Col1 + ', ' + @Col2 + ' From TableName
OPEN field_cursor'
END

OPEN field_cursor
FETCH NEXT FROM field_cursor INTO @field1_working, @field2_working
SET @product = 0
SET @counter = 0
SET @one = 0
SET @Two = 0
Set @Three = 0
Set @Four = 0
Set @Final = 0

WHILE @@FETCH_STATUS = 0

 BEGIN
   SET @counter = @counter + 1
   SET @product = @product + (@field1_working *
   @field2_working)
   Set @One = @One + (@field1_working)
   Set @Two = @Two + (@field2_working)



   FETCH NEXT FROM field_cursor INTO @field1_working, @field2_working
END
CLOSE field_cursor
DEALLOCATE field_cursor
Set @Final=  ((@Counter * @Product)- (@One * @Two))

Return
 Left(CAST(@Final AS Varchar), 5)
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

If you change the Select statement to:
Select Column1, Column2 from Table
--'SELECT ' + @Col1 + ', ' + @Col2 + ' From Table'

It will work.  Kindly advise.  Thanks a lot!!!

Maybe this would help, I heard this suggestion (but I don't know how to implement it)
"always take the output from a dynamic SQL using sp_executesql method. But first why do you want to open a cursor on that? if that is not required then a simple EXEC command will do..."

Einstine,

Now that I think about this, the View may work.  However, this would work for two columns.  I'll try it now.  However, there is still one problem. When the user selects a particular column, I'll need to have a view for the other 5 columns.  

What I mean by this is that if the user selects column1; I will need column1 and column1 in a view; run the UDF, need column1 and column2 in a view.. etc. until Column 1 and Column6.  How would I design this process?

I have created a view.  So far it looks OK, but when I try to put the execute command before or after the begin statement, I am getting a syntax error.    It works without the EXEC command.  Please advise regarding this syntax:


Begin
@SQL = 'ALTER VIEW View AS SELECT ' + @Col1 + ' AS Col1, ' + @Col2 + ' AS Col2 FROM Table'
EXEC (@SQL)

DECLARE field_cursor CURSOR FOR
Select Col1, Col2 from IndirectView

END
OPEN field_cursor

I will try to avoid using a cursor,

Please try this,

...
declare @query varchar(1000)
declare @counter ...
declare @one ...
declare @two ...
declare @product ...
declare @final ...

set @query = 'select @counter = count(*), @one = sum(' + @col1 + '), @two = sum(' + @col2 + ') @product = sum(' + @col1 + ' * ' + @col2 + ' from yourtable'

exec(@query)
Set @Final=  ((@Counter * @Product)- (@One * @Two))

Einstine,
You're very smart.  Using a view, helped me resolve this issue.  Thanlks a lot!!

Glad it worked... :-)

Hey, Thanks for the indifference,
I share my time with you at least you could have tell me if you have tried my last comment.

Ralmada,

I tried not to favor one person.  I tried your idea, but am getting errors.  The code needs to be modifed so it doesn't use a field curor.  Please paste the updated code here (if you would like).