Declare table variable in sqldatasource (aspnet)

Question

I have a query that I try in Microsoft SQL Server Management Studio, and this query work fine:

DECLARE @tableVariable TABLE (fecha DATE, valor Float);
INSERT @tableVariable SELECT fecha, valor FROM table1;
SELECT * FROM @tableVariable

When I go to VisualStudio and paste this query in sqldatasource, not work.

Anybody can help me?

Thanks

SOLVED

Finally, the problem is solved remove the tag <SelectParameters> <asp:Parameter Name = "tableVariable" /> </SelectParameters> into sqldatasource.

Thanks to all!

why don't you just paste SELECT fecha, valor FROM table1 in SqlDataSource? I don't think it supports multiple SQL statements. — Cristian Lupascu
– Cristian Lupascu, Commented Jan 30, 2013 at 8:22
The real query is very long for put here, and very complex. It is an abbreviated example. Must be some way to be able to run in asp.net. I use sqldatasource for show the result in a GridView. — user2024475
– user2024475, Commented Jan 30, 2013 at 8:30
When I put this in my sqldatasource for it is asking me to Define Parameters, and @tableVariable is not a parameter in my application, but a temp table in sql statement. — user2024475
– user2024475, Commented Jan 30, 2013 at 8:35
in this case, just encapsulate the complex query in a stored procedure and use that in the data source — Cristian Lupascu
– Cristian Lupascu, Commented Jan 30, 2013 at 8:35
How i can encapsulate the complex query? Any example? Thanks! — user2024475
– user2024475, Commented Jan 30, 2013 at 8:41

Cristian Lupascu · Accepted Answer · 2013-01-30 09:22:09Z

3

Try creating a stored procedure, like so:

CREATE PROCEDURE ComplexQuery
as
    DECLARE @tableVariable TABLE (fecha DATE, valor Float);
    INSERT @tableVariable SELECT fecha, valor FROM table1;

    -- more complex logic in here

    SELECT * FROM @tableVariable

go

Then, in your data source specify the name of the procedure.

Example:

        <asp:SqlDataSource
            id="SqlDataSource1"
            runat="server"
            ConnectionString="..."
            SelectCommandType="StoredProcedure"                
            SelectCommand="ComplexQuery">
        </asp:SqlDataSource>

answered Jan 30, 2013 at 9:22

Cristian Lupascu

40.8k18 gold badges107 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user2024475 Over a year ago

Can I pass arguments (parameters) to a stored procedure? It is possible drop and insert rows dynamically? When I call to ComplexQuery, I need pass arguments o parameters.

Cristian Lupascu Over a year ago

@user2024475 sure, see the stored procedure link I posted for details on how to add parameters to a stored proc. Then, specify the parameters in the data source as explained here: msdn.microsoft.com/en-us/library/z72eefad(v=vs.100).aspx

user2024475 Over a year ago

Oh Thanks you very much! This is the best answer for my problem. I haven't got reputation and I can't click to "answer useful". Thanks you very much again!

Philiop · Accepted Answer · 2013-01-30 08:53:06Z

1

Is it not worth just converting the the declared table to a temp table i.e.

CREATE TABLE #tableVariable (fecha DATE, valor Float)

You would need a DROP TABLE #tableVariable later to make sure you are cleaning memory but it will have the desired affect I believe.

answered Jan 30, 2013 at 8:53

Philiop

4733 silver badges14 bronze badges

1 Comment

user2024475 Over a year ago

This option is computationally heavier, no?

markp3rry · Accepted Answer · 2013-01-30 09:01:28Z

0

I don't think you should be putting a long and complex query into SqlDataSource - that's really for binding a table or view to a datagrid without having to mess around in code behind. If you want to run a complex query you should either set up a stored procedure to do it and call that from SqlCommand, or put the text for the query into SqlCommand. Either way I don't think SqlDataSource is the way to go here.

answered Jan 30, 2013 at 9:01

markp3rry

73410 silver badges27 bronze badges

2 Comments

user2024475 Over a year ago

I don't need a Stored Procedure. If I put the text of query into SqlCommand, I can use a GridView for show the result?

markp3rry Over a year ago

All the SqlCommand is going to do is run a SQL statement against the database. If the statement is going to return some data (SELECT * FROM...) then you still need to get the results of that into something that you can bind to a GridView like a DataSet or DataTable. Maybe SqlDataAdapter is the way to go here - you can feed it some SQL and easily generate a DataSet from it.

Collectives™ on Stack Overflow

Declare table variable in sqldatasource (aspnet)

3 Answers 3

3 Comments

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related