Passing a result set to a stored procedure

Hi All,
I have sometimes used the following sort of query to pull data from one
table to another:
INSERT INTO Table1
SELECT fname, lname
FROM Table2
Now, let's suppose that I had created a stored procedure to do the
insert (and any other logic i was concerned about) and I did something
like this:
EXECUTE Table1 _Insert
SELECT fname, lname
FROM Table2
It won't work, giving an error that looks something like this:
Server: Msg 201, Level 16, State 3, Procedure Table1_Insert, Line 0
Procedure 'Table1_Insert' expects parameter '@.fname', which was not
supplied.
I assume I'm not doing things right... how would I pass a result set to
a stored procedure, with each row corresponding to an input parameter
of the stored procedure?Hi
INSERT INTO TableName EXEC mySP
<joshbeall@.gmail.com> wrote in message
news:1144068457.520507.208200@.i40g2000cwc.googlegroups.com...
> Hi All,
> I have sometimes used the following sort of query to pull data from one
> table to another:
> INSERT INTO Table1
> SELECT fname, lname
> FROM Table2
>
> Now, let's suppose that I had created a stored procedure to do the
> insert (and any other logic i was concerned about) and I did something
> like this:
>
> EXECUTE Table1 _Insert
> SELECT fname, lname
> FROM Table2
>
> It won't work, giving an error that looks something like this:
>
> Server: Msg 201, Level 16, State 3, Procedure Table1_Insert, Line 0
> Procedure 'Table1_Insert' expects parameter '@.fname', which was not
> supplied.
>
> I assume I'm not doing things right... how would I pass a result set to
> a stored procedure, with each row corresponding to an input parameter
> of the stored procedure?
>|||Your Table1_Insert proc is expecting a parameter. How about posting the
code for the proc?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
<joshbeall@.gmail.com> wrote in message
news:1144068457.520507.208200@.i40g2000cwc.googlegroups.com...
Hi All,
I have sometimes used the following sort of query to pull data from one
table to another:
INSERT INTO Table1
SELECT fname, lname
FROM Table2
Now, let's suppose that I had created a stored procedure to do the
insert (and any other logic i was concerned about) and I did something
like this:
EXECUTE Table1 _Insert
SELECT fname, lname
FROM Table2
It won't work, giving an error that looks something like this:
Server: Msg 201, Level 16, State 3, Procedure Table1_Insert, Line 0
Procedure 'Table1_Insert' expects parameter '@.fname', which was not
supplied.
I assume I'm not doing things right... how would I pass a result set to
a stored procedure, with each row corresponding to an input parameter
of the stored procedure?|||Do you mean that you want to pass a table through as an argument, in
which case you can use a table variable something like:
DECLARE @.table table(Col1 int, Col2 varchar(40))
INSERT INTO @.Table(Col1, Col2)
SELECT *
FROM table2
exec table1_insert @.Table
(untested).|||Will,
Table variable cannot be used as an input parameter
"Will" wrote:

> Do you mean that you want to pass a table through as an argument, in
> which case you can use a table variable something like:
> DECLARE @.table table(Col1 int, Col2 varchar(40))
> INSERT INTO @.Table(Col1, Col2)
> SELECT *
> FROM table2
> exec table1_insert @.Table
> (untested).
>|||lol - had a suspicion there was something wrong with that example
(hence the caveat (untested)).|||See the article:
http://www.sommarskog.se/share_data.html
Anith|||Tom Moreau wrote:
> Your Table1_Insert proc is expecting a parameter. How about posting the
> code for the proc?
> --
> Tom
The stored procedure is simply an INSERT statement. In this simplified
example there's no real reason you would have a stored procedure, but
I'm trying to figure out the method to handle this, so in the event
that you had a more substantial reason to use a stored procedure, you
could pass every row from a table to that stored procedure. Or more
specifically, you could select specific columns from another table, and
pass them to a stored procedure, one row at a time.
At any rate, here is the code for my stored procedure:
CREATE PROCEDURE DeleteMe_Insert
@.fname varchar(50),
@.lname varchar(50)
AS
INSERT INTO DeleteMe VALUES(@.fname, @.lname)|||The proc you have below actually has two parameters:
@.fname varchar(50),
@.lname varchar(50)
Thus, when you call it, you must feed it values for both of these
parameters. Therefore, this proc would be called like:
EXEC DeleteMe_Insert 'Joe', 'Smith'
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
<joshbeall@.gmail.com> wrote in message
news:1144082359.964230.311950@.u72g2000cwu.googlegroups.com...
Tom Moreau wrote:
> Your Table1_Insert proc is expecting a parameter. How about posting the
> code for the proc?
> --
> Tom
The stored procedure is simply an INSERT statement. In this simplified
example there's no real reason you would have a stored procedure, but
I'm trying to figure out the method to handle this, so in the event
that you had a more substantial reason to use a stored procedure, you
could pass every row from a table to that stored procedure. Or more
specifically, you could select specific columns from another table, and
pass them to a stored procedure, one row at a time.
At any rate, here is the code for my stored procedure:
CREATE PROCEDURE DeleteMe_Insert
@.fname varchar(50),
@.lname varchar(50)
AS
INSERT INTO DeleteMe VALUES(@.fname, @.lname)|||Will wrote:
> Do you mean that you want to pass a table through as an argument?
No, I want to pass each row to the stored procedure, separately.
Presumably what I would have to do is some sort of loop construct that
reads a row out of the source table, and passes it to the stored
procedure...?

Pass sorting parameter to Stores Procedure

I used Datagrid to show "Title", "Location" and "Date", It works very well.
I want to sort DataGrid data, that is when user click the "Title", "Location" or "Date",
my asp.net code will through class and send "Sort" parameter to stores procedure to get the new data and bind to DataGrid.

Here is my stores procedure:

CREATE Procedure JobSearch
(
@.Search varchar(150),
@.Sort varchar(50)
)
AS

SELECT
JobTitle,
JobLocationCity,
JobLocationState,
PostDate

FROM
Job

WHERE
JobTitle LIKE '%' + @.Search + '%'
OR
JobKeywords LIKE '%' + @.Search + '%'

IF @.Sort = "Title"
ORDER BY JobTitle
IF @.Sort = "Location"
ORDER BY JobLocationState, JobLocationCity
IF @.Sort = "PostDate"
ORDER BY PostDate DESC

When I test stores procedure in SQL Server, I got the error about "Error 156: Incorrect syntax near the keyword 'ORDER' ".

Who has experience about stores procedure, please help me to correct this error.I am doing the same thing by working around using a string. Such as

Declare @.sql as varchar(2048)
...
exec(@.sql)|||Why you cannot use datagird to do the sorting?|||Very instersting, where's your code? In class or stores procedure? Could you show me more detail you code?

Thanks!|||I don't understand what you said. I just want to use datagrid to do the sorting.

The procedure as following:

1. Using the datagrid's AllowSorting property.
2. Writing a sortcommand event handler (retrieve e.SortExpression value, pass this value to component-class, class call SQL Server-stores procedure to retrieve new sort data and return to sortcommand event handler, and finally call bind function).

This is tree tiers program. If verything (include dataconnection and stores procedure) write in the same aspx page (two tiers program), it works.

I can write six different stores procedure in SQL Server, six classes in Component, then create a sub SortDataGrid function in aspx page to call these class and stores procedure, but this is not best way. I try to find the best way, one calss and one stores procedure in SQL Server. This stores procedure accept one parameter to do any kind of sorting.

What's your best way, could you show me?

Thanks!|||I think you'll need to use dynamic sql to achieve your desired effect. I've sketched it out below. Just make sure that the strings are closed/opened where appropriate. I don't have query analyzer available right now, so I'm sure there's a missing quote or an extra one somewhere below. But that's basically how you can do it.

CREATE Procedure JobSearch
(
@.Search varchar(150),
@.Sort varchar(50)
)
AS
DECLARE @.sql varchar(200)
SET @.sql =
'SELECT
JobTitle,
JobLocationCity,
JobLocationState,
PostDate
FROM Job
WHERE
JobTitle LIKE ''%'' + @.Search + ''%''
OR
JobKeywords LIKE ''%'' + @.Search + ''%'' '
IF @.Sort = "Title"
SET @.sql = @.sql + ' ORDER BY JobTitle'
IF @.Sort = "Location"
SET @.sql = @.sql + ' ORDER BY JobLocation, JobLocationCity'
IF @.Sort = "PostDate"
SET @.sql = @.sql + ' ORDER BY PostDate DESC'
exec(@.sql)

|||Here is how you can sort the datagird by usingDataView.|||You don't HAVE to use dynamic SQL. I'm pretty sure you can do this:

select ...
from ...
order by case when @.Sort = 1 then Column1 when @.Sort = 2 then Column2 end|||I just checked this, and yes, it works. When I tried this last night at home on a laptop with no sql server installed, I used some website's online sql server interpreter to run a query similar to this, and it gave me an error, so I figured it was wrong. I just checked at work, and it works fine.|||The only problem with doing it this way is that Column1, Column2, etc either have to be the same data type or you need to CAST them to become the same data type.

select ...
from ...
order by case when @.Sort = 1 then Column1 when @.Sort = 2 then Column2 end

You might find it better to use this method:

select ...
from ...
order by case when @.Sort = 1 then Column1 end, case when @.Sort = 2 then Column2 end

Terri|||Thanks Vito1281, Tmorton, and Pierre gave me about stores procedure suggestions. I will test these ways today and will report the result to you.

JimmyM suggest use Dataview, consider the two potential downsides:
1. there is the potential for the cached data to become stale.
2. Viewstate is maintained as a hidden HTML field, adding large objects to the viewstate can add several kilobytes, cause load the page very slowly.

So use viewstate isn't first choose in this case.

Thanks everyone !|||You may not quite understand what DataView is. DataView has nothing to do with caching data or Viewstate, it is a view (like the view you use at database) of a datatable in a dataset.
You can do something like filtering and sorting on the view and bind the view to your disply control. Here is one way of how to use it:

[Visual Basic]
Dim prodView As DataView = New DataView(prodDS.Tables("Products"), _
 "UnitsInStock <= ReorderLevel", _
 "SupplierID, ProductName", _
 DataViewRowState.CurrentRows)
[C#]
DataView prodView = new DataView(prodDS.Tables["Products"],
 "UnitsInStock <= ReorderLevel",
 "SupplierID, ProductName",
 DataViewRowState.CurrentRows);

|||JimmyM,

I am so sorry about it I made mistake to be DataView as ViewState. I never use Dataview in my program and I will learn about it. I will try to run the code follow your link.

I just test the stores procedure, which Vito1281 show to me, dynamic SQL. It work very well in SQL Server, and I will use this way first.

Amorton and Pierre have another suggestion (select ... from ...order by case when @.Sort = 1 then Column1 when @.Sort = 2 then Column2 end), I will test it later.

Thanks for everyone's help!