Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

execute string in SQL Server Query.

rakbatrakbat Member Posts: 1
Hi All,

I'm using SQL Server 2000.
There are two tables in my DB. Consider following stucture...

1. Formula_mst
==============
FormulaName Formula
------------ -----------
Mutiply col1*col2
Add col1+col2

2. DataMst
==========
col1 col2
---- ----
5 10


I'm looking for output as under.
---------------------------------
FormulaName output
----------------------
Multiply 50
Add 15

Can you please help me with the query?
Many thanks.

Rakbat

Comments

  • HackmanCHackmanC Member Posts: 441
    Eval'uate the function...

    [code]
    -- DROP TABLE Table1
    -- DROP TABLE Table2

    CREATE TABLE [Table1] (
    [Name] [char] (10) NOT NULL ,
    [Oper] [char] (50) NOT NULL ,
    CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
    (
    [Name]
    ) ON [PRIMARY]
    ) ON [PRIMARY]
    GO

    CREATE TABLE [Table2] (
    [ID] [int] NOT NULL ,
    [Value1] [int] NOT NULL ,
    [Value2] [int] NOT NULL ,
    CONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED
    (
    [ID]
    ) ON [PRIMARY]
    ) ON [PRIMARY]
    GO

    INSERT INTO [Contratos].[dbo].[Table1]([Name], [Oper]) VALUES('Multiply', 'Value1 * Value2')
    INSERT INTO [Contratos].[dbo].[Table1]([Name], [Oper]) VALUES('Add', 'Value1 + Value2')

    INSERT INTO [Contratos].[dbo].[Table2]([ID], [Value1], [Value2]) VALUES(1, 5, 10)
    INSERT INTO [Contratos].[dbo].[Table2]([ID], [Value1], [Value2]) VALUES(2, 75, 710)

    DECLARE @internal_function VARCHAR(520)
    SELECT @internal_function = Oper FROM Table1 WHERE Name = 'Multiply'
    EXEC('select ' + @internal_function + ' as total from Table2 where ID = 2')
    [/code]

    ADO.NET Calling ...
    Still don't know how to recover the 'eval' result from a stored proc :(

    [code]
    Dim c As New System.Data.SqlClient.SqlConnection("application name=Temp;data source=(local);initial catalog=Temp;password" & _
    "=MyPassword;persist security info=True;user id=sa;workstation id=SUEPRSDEV;packet size=4096")
    c.Open()

    Dim d As New System.Data.SqlClient.SqlCommand()
    d.Connection = c
    d.CommandText = "" + _
    "DECLARE @internal_function VARCHAR(520)" + vbCrLf + _
    "SELECT @internal_function = Oper FROM Table1 WHERE Name = 'Multiply'" + vbCrLf + _
    "EXEC('select ' + @internal_function + ' as total from Table2 where ID = 2')" ' PROVIDE THE ID
    d.CommandType = CommandType.Text

    Dim r As System.Data.SqlClient.SqlDataReader = d.ExecuteReader(CommandBehavior.SingleResult)
    r.Read()
    Debug.Write(r.GetValue(0))
    [/code]

    Im not encourage you to do silly things :)
    This should be very well implemented.
    Dont allow the user to insert the 'Function' or some other 'genius-ly' thing.

    [red]Good luck![/red]
    [blue]Hackman[/blue]
Sign In or Register to comment.