Passing SELECTed Rows To A Calling SP

Sep 11, 2006

Hi!

I'm a new T-SQL developer and just hit a roadblock.

I have a scenario that goes like this: I have 2 stored procedures,
spInner and spOuter. spInner has a SELECT statement which would
normally be used by a class using MS Enterprise Library and that output
goes into a DataSet. However, I need to get the output of the SELECT
statement to go into spOuter and that's what I can't seem to figure
out.

I know I may be asked to use functions that return tables in replies to
this post, but I can't do that as some parts of my application have an
EXEC(string) for dynamic SQL, instead of spInner.

Any help appreciated.

Cheers,
N.I.T.I.N.

View 2 Replies


ADVERTISEMENT

Passing A Selected Row Column Value To The Stored Procedure

Feb 17, 2006

I have a simple Gridview control that has a delete command link on it.If I use the delete SQL code in line it works fine.  If I use a stored procedure to perform the SQL work, I can't determine how to pass the identity value to the SP.   Snippets are below...The grid<asp:GridView ID="GridView2" runat="server" AllowPaging="True" AllowSorting="True"                    AutoGenerateColumns="False" DataSourceID="SqlDataSource2">                    <Columns>                        <asp:BoundField DataField="member_id" HeaderText="member_id" InsertVisible="False"                            ReadOnly="True" SortExpression="member_id" />                        <asp:BoundField DataField="member_username" HeaderText="member_username" SortExpression="member_username" />                        <asp:BoundField DataField="member_firstname" HeaderText="member_firstname" SortExpression="member_firstname" />                        <asp:BoundField DataField="member_lastname" HeaderText="member_lastname" SortExpression="member_lastname" />                        <asp:BoundField DataField="member_state" HeaderText="State" SortExpression="member_state" />                        <asp:CommandField ShowEditButton="True" />                        <asp:CommandField ShowDeleteButton="True" />                    </Columns>                </asp:GridView>                <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:rentalConnectionString1 %>"                    SelectCommand="renMemberSelect" SelectCommandType="StoredProcedure"                    DeleteCommand="renMemberDelete"  DeleteCommandType="StoredProcedure"                      OldValuesParameterFormatString="original_{0}"                     >                                         <DeleteParameters>                                             <asp:Parameter  Name="member_id" Type="Int32"  />                                              </DeleteParameters>                                            </asp:SqlDataSource>the SPCREATE PROCEDURE renMemberDelete@member_id  as intAs UPDATE [renMembers] SET member_status=1 WHERE [member_id] = @member_idGO

View 1 Replies View Related

Reporting Services :: Passing Multivalues From Selected Row Group Value

May 15, 2015

Below are screenshots from my report
                 
When a user clicks on a amount it should pass the sales regions , so that the a new detailed report opens up. Sales regions are passed when the report is in expanded view like on the right. But when its collapsed it just passes only the top most value and not all the sales region to the detailed report.

I've set this value as parameter to the detailed report 

=Fields!SalesRegion.Value

How do I change this so that all the sales regions from the selected row group are passed when collapsed? I've uploaded the image in case the page does not show up.

View 2 Replies View Related

Passing Multi-Selected Items To Reporting Server Using A URL

Nov 6, 2007

I have designed a report that allows the user to select multiple values to be passed to SSRS. When I deploy and run the report from Report Manager I can select 1 to n values and then run the report. It works great. Now I attempt to call Reporting Services from my Web Site. I format the URL and attempt to pass the mutliple values in the URL. An example would be http://YadaYada&rs:Command=Render&rs:Format=HTML4.0&rcarameters=false&OrderTypeID='1,2' The report uses a Stored Procedure that is expecting @Ordertype Varchar(1000). And Yes, the Stored Procedures handles the multiple values being passed to it. What I receive is an error message (rsReportParameterTypeMismatch). Any suggestions on how to pass these values?

Thanks in Advance
Tom

View 2 Replies View Related

Dts Only Selected Rows

Oct 13, 2006

I have to dts rows by timestamp. For example if my dts downloaded at 10 am then in the next run i want to grab rows updated in the AS400 after 10am. what is the best way to go in ssis?

thanks...

kushpaw

 

View 5 Replies View Related

Count The Number Of Rows Selected

May 27, 2005

This is my SQL :
Select p.patientid,p.patientname,p.patientIc,pvi.DateOfAdmission,pvi.visitid,pvi.ward,pvi.bedno,pf.status,pvi.SurgeonName,(f.Title + ' ( Ver ' + (CAST(f.Version as Char(10))) + ')') as Title FROM patient p, patientvarianceinfo pvi,patientForm pf,Form f where (p.PatientName LIKE '%" & Name & "%' or p.PatientIc LIKE '%" & ic & "%' or pvi.Ward LIKE '%" & ward & "%' or pvi.Bedno LIKE '%" & bed & "%') and (p.patientid = pvi.patientid) and (p.patientid = pf.patientid) and (pvi.patientid = pf.patientid) and (pf.FormID = f.FormID)and p.patientid in (select patientid from patientform pf)how do i get the number of rows?

View 1 Replies View Related

Show Rownumber In Selected Rows

Apr 25, 2001

I want to show a row number for each row returned by a SELECT starting at 1 and incrementing by 1 for each row.

Anyone got any ideas?

View 2 Replies View Related

Returning A Set Of Randomly Selected Rows?

Jun 13, 2002

Say you have a table that contains 1000 rows, you create a simple select statement with a where clause that returns 100 or so of those rows. Easy enough.

Now, let's say that you wish to modify that select so that out of the 100 rows that match the where clause criteria, you only wish to return 10 rows randomly (i.e., you could run this query multiple times and get different results). How exactly would you go about doing this, efficiently?

I've thought about creating a stored procedure that will query the initial 100 rows into a temp table with an additional column (to number the rows from 1 to 100). Then setting up a loop (10 iterations) that will then generate a random number from 1 to 100 and select that row number into another temp table. At the end of the loop I'll have my table of randomly selected records. I am sure there is probably a better way to do this...

~Mike

View 2 Replies View Related

Retrieving Same Set Of Rows Selected Randomly

Nov 9, 2004

Hi All


by using this query

"select * from sample order by newid()" im getting a set of rows. On refreshing this query i need the same set of rows to validate.(provided sufficient data in the table).

Please provide me the query to use for this

Adv. Thanks
Hari...

View 2 Replies View Related

Change Selected Rows To Columns

Nov 19, 2007

Hello,

I start with this table,










Tagname
Timestamp
Value

A
12/12/2007 12:00:01
1

A
12/12/2007 12:00:02
1,1

A
12/12/2007 12:00:03
30

B
12/12/2007 12:00:01
12

B
12/12/2007 12:00:02
12

B
12/12/2007 12:00:03
12

C
12/12/2007 12:00:01
13,4

C
12/12/2007 12:00:02
14

C
12/12/2007 12:00:03
14,5

I would like the table to be as following










Timestamp
A
B
C

12/12/2007 12:00:01
1
12
13,4

12/12/2007 12:00:02
1,1
12
14

12/12/2007 12:00:03
30
12
14,5

Does anyone knows how to do this within a query?

Thx,
Nic

View 9 Replies View Related

Count Of Distinct Rows Of Only Selected Columns?

Aug 9, 2012

I am trying to just get a count of the number of distinct rows are in a table --- not looking at the entire row of fields, but only selecting a few.

i don't want to see the distinct rows, i just want a count of how many are in the table.

View 4 Replies View Related

Update The Selected Rows Before Returning Them In One Command.

Mar 14, 2008



Hello,

I have an external process that polls message rows from a table. apon taking them, it also needs to mark them as taken. there is a status column in the table. marking messages as taken will change the value of status.

How can i perform both these operations in one command? Select the top x rows where their status is equal 1, then update the status of those same rows to a value of 5 for example.

I could iterate through the result of hte intial select and change the status 1 by 1 using a cursor, but this seems like a slow option.

Any other ideas?

Thank you.

View 4 Replies View Related

How Do I Delete Selected Rows Without Deleting The Entire Table?

May 26, 2000

Hi Guys!

This SQL statement, though carefully written to delete only selected rows, deletes the entire A_Shift_Times table:

DELETE FROM A_Shift_Times
WHERE EXISTS (
SELECT 1
FROM
Users
WHERE
(A_Shift_Times.time_in >= CONVERT(DATETIME, '2000-05-29 00:00:00', 102)) AND
((Users.User_Password LIKE N'mrr%') OR (Users.User_Password LIKE N'work%')))

What gives? What am I doing wrong here?

View 1 Replies View Related

How To Return Number Of Selected Rows From All Queries Performed On DB

Feb 20, 2013

Recently I had an application developer approach me and asked if I could provided him with a list of sprocs by returned row count. We had an issue where the application passed in a number of parameters which attempted to return 200k plus rows of data and the application was timing out. He changed the required parameters in the application and a reasonable number of rows were returned as expected. Short term solution to this one problem.

However there are always timeout issues with this particular application and we got to thinking that maybe other sprocs that were called using parameters would also fail at some point in time because too much data was being returned.

View 5 Replies View Related

SQL Query - Updating Selected Rows/Creating New Row - Same Table

Jan 24, 2008

I have to write a couple scripts that will update a couple columns in two separate tables and also insert a new row with the same data except for a few calculated or provided values ...... see specs below ...




1. tGradeHist Table Script One (Needs to be run first)



a. Read tGradeHist Table and Select rows with GradeEndDate = NULL and GradeStartDate = '1/1/2007 12:00:00 A.M.'

b. Calculate New Step Amount = StepAmount * Incr% (Round To Nearest Whole Dollar)

c. Create New Row for this table using information from row read above and insert new information where indicated :


GradeCode - Same

GradeLocationCode - Same

Step - Same

GradeStartDate - '7/1/2007 12:00:00 A.M.'

GradeEndDate - NULL

GradeCurrencyCode - Same

StepAmount - Result of b (above)

GradeFrequencyCode - Same

RangeMaximumAmount - Same

RangeMidAmount - Same

RangeMinimumAmount - Same

GradeCurrentFlag - 'True'

MarketMaximumAmount - Same

MarketMidAmount - Same

MarketMinimumAmount - Same

GradeGUID - Same

TSCOL - Same


d. Update Row read in a (above) with GradeEndDate = '6/30/2007 12:00:00 A.M.' and GradeCurrentFlag = 'False'





2. tPersonBasePayHist Table Script Two (Needs to be run second)


a. Read tPersonBasePayHist Table and Select rows with PersonBasePayEndDate = NULL

b. Calculate New PersonBasePayAmount = PersonBasePayAmount * Incr% (Round To Nearest Whole Dollar)

c. Create New Row for this table using information from row read above and insert new information where indicated :



PersonGUID - Same

PersonBasePayStartDate - '7/1/2007 12:00:00 A.M.'

PersonBasePayEndDate - NULL

PersonBasePayCurrencyCode - Same

PersonBasePayAmount - Result of b (above)

PersonBasePayFrequency - Same

PersonBasePayPayrollFrequencyCode - Same

BasePayReasonCode - 'SA'

ConductedBasePayReviewDate - Same

ScheduledBasePayReviewDate - Same

PayrollCode - Same

PersonBasePayCurrentFlag - 'True'

ApprovedByPersonGUID - Same

PersonBasePayGUID - Same

TSCol - Same



d. Update Row read in a (above) with PersonBasePayEndDate = '6/30/2007 12:00:00 A.M.' and PersonBasePayCurrentFlag = 'False'














View 7 Replies View Related

How Do I Extract Data From Selected Rows From My Excel Source File?

Jun 21, 2006

The columns in my excel source contain data of different types with the column name being a string and the data in those columns being integers. Is there any way to only extract numeric data , in short I want column names to be omitted. Also the data is distributed unevenly , beggining at various rows in each column.

Thanking in advance :)

View 3 Replies View Related

Randomly Order Selected Rows From Table - Select Procedure Vs Application

Jun 21, 2012

I need to randomly order the selected rows from my table. Is this better to do on the Application level or in a stored procedure using "orderby NewID()"?

Which is faster? There will be about 100 rows returned with 10 columns.

View 5 Replies View Related

Reporting Services :: How To Display (All Selected) When Parameter (Select All) Is Selected In SSRS

May 6, 2015

Using SSRS 2008 r2...I have a report with a single-value parameter and three multi-value parameters, Class1, Name2 and Name3. I'm hoping for an explanation to one thing that I'm seeing and information on a second thing.

Class1 and Name2 both have the (Select All) parameter selected but Class1 is displaying the concatenated parameter variable list whereas Name2 is showing Null. Why is that? If anything, how can I get Class1 to be similar to Name2 and show Null?But my desired wish is to have Class1, Name2 and Name3 display the text"All Selected" when the parameter (Select All) is chosen.

View 3 Replies View Related

SQL 2012 :: Selected Subscriber Does Not Satisfy Minimum Version Compatibility Level Of Selected Publication

Feb 21, 2014

I have created a Transactional Replication Publication on my SQL 2012 server.When I log into another server on the domain running 2008R2 and try to subscribe to the 2012 Publication, I get the following error when clicking on "Add SQL Server Subscriber": "The selected Subscriber does not satisfy the minimum version compatibility level of the selected publication"

The 2012 DB is set as 2008 Compatibility Mode?Am I not able to Publish from 2012 to 2008?.I was using SSMS 2008 to connect to my 2012 Instance, thats why it didn't work...

View 0 Replies View Related

Passing Execute DTS Package Result (success/failure) To Calling SSIS Package

Mar 6, 2008

I have a SSIS job, one of the last steps it performs is to execute a SQL 2000 DTS package. This has to be done as a SQL 2000 DTS package as it is performing rebuilds of SQL 2000 Analysis Services dimensions and cubes. We've found that when the DTS fails the SSIS job is happily completing showing as a success, we would prefer to know it went wrong.

As far as I'm aware SSIS merely starts the DTS off and doesn't care about it's result. I've taken a look in to turning on the logging for the execute DTS package and thought that the ExecuteDTS80PackageTaskTaskResult would give me the answer I need...but is merely written to the log not available as an event-handler. It also looks like it is not safe to put a SQL task in as the next item to go look at the SQL 2000 system tables to look at the log for the DTS package as the SSIS documentation warns that the DTS package can continue to run after the execute DTS package task has ended.

Ideally I want any error raised within the DTS package to cascade up to be an error in the SSIS job, I can then handle it appropriately. I cannot find a way to do this. Is there a way?

If not, can anyone suggest how in the remainder of the SSIS tasks I can be sure that the DTS has completed before I start any other tasks that will check for the SQL 2000 log of its execution?

View 5 Replies View Related

Passing Parameters For Individual Rows

Aug 4, 1998

First of all, I`m a very new entrant to the world of SQL Server. So I`ll make some mistakes.

I need to know how to write a stored procedure that will insert or update INDIVIDUAL rows. All the documentation I`ve seen is pretty coy on how to actually do this.

Any actual examples, or any tips at all would be greatly appreciated.

Thank you very much.

View 1 Replies View Related

SQL Task - Passing In A Parameter - Now Rows Returned

Mar 29, 2007

Hi,



am trying to do something which I thought would be simple to do in SSIS, several hours am still struggling with it. Not sure if this a bug or a restriction of the product. Or if im hitting some kind of compatability issue because im trying to get to a Oracle database.



Have a sql task which passes in a parameter, I then query my Oracle database and am trying the result (single row) into another variable.



Variable:

Variable Name = Subsystem

Scope= Package

Value = pgc

Data Type = string



SQL:



SELECT SUBSYSTEM_DS AS SUBSYSTEM_DS FROM SYS_SUBSYSTEM WHERE SUBSYSTEM_ID = ?



Have also tried:



SELECT SUBSYSTEM_DS AS SUBSYSTEM_DS FROM SYS_SUBSYSTEM WHERE SUBSYSTEM_ID = ?0



Result Set = Single Row



Parameter Mapping:



VariableName = User:ubsystem

Direction = Input

Data Type=Varchar

Parameter Name= 0

Parameter Size= -1 (have also tried 3 - length of variable)



Oracle Table:



SQL> desc sys_subsystem
Name Null? Type
----------------------------------------- -------- ----------------------------
SUBSYSTEM_ID NOT NULL CHAR(3)
SUBSYSTEM_DS NOT NULL VARCHAR2(40)

....

....

...





The Error:



[Execute SQL Task] Error: An error occurred while assigning a value to variable "SubsystemName": "Single Row result set is specified, but no rows were returned.".



I have another SQL Task that performs an update on this same table and I also pass in the same variable but it works?



SQL:



UPDATE sys_subsystem
SET as_process_fg = 'X'
WHERE subsystem_id = ?0



The parameter mappings are the same as above.



Any assistance here would be much appreciated.



Thanks

Mick



View 5 Replies View Related

SQL Server 2012 :: Error When Passing Multiple Rows

Apr 20, 2015

I am getting error when I passed multiple rows in less than condition:

create table #t1
( ID int)
INSERT INTO #t1

SELECT 1 UNION ALL SELECT 5 UNION ALL SELECT 8
CREATE TABLE #t2
(ID int)
INSERT INTO #t2
SELECT 3 UNION ALL SELECT 20 UNION ALL SELECT 4

SELECT ID FROM #t2
WHERE ID < (SELECT ID FROM #t1)

Error is: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

How to pass multiple values in this condition?

View 2 Replies View Related

If There Is No Picture Selected

Apr 2, 2008

Dear readers,
On my page people can opload foto's with a <asp:FileUpload ID="myfile" runat="server" BorderWidth="3px" BorderColor="Silver" BorderStyle="Inset" />
and stored the filePath in the database
Now i want, that when there is no picture selected, the path to Noimage.jpg picture who is in my image map will stored on my datadbase.
my script start like this If Not (myfile.HasFile) Then

////how can i select a standard image from
my imageMap and store path to database /////


Dim cnn As Data.SqlClient.SqlConnection

Dim cmd As Data.SqlClient.SqlCommand

Dim strSQL As String

Dim connString As String = (ConfigurationManager.ConnectionStrings("Personal").ConnectionString)
strSQL = "Insert Into tblMateriaal(ArtikelGroep,Artikelnaam,ArtikelType,ArtikelMaat,Aantal,Prijs,ContactPersoon,EmailAdress,Aanvul) Values(@ArtikelGroep,@ArtikelNaam,@ArtikelType,@ArtikelMaat,@Aantal,@Prijs,@ContactPersoon,@EmailAdress,@Aanvul)"
cnn = New Data.SqlClient.SqlConnection(connString)

cmd = New Data.SqlClient.SqlCommand(strSQL, cnn)

Dim plaatje As New Data.SqlClient.SqlParameter("@plaatje", System.Data.SqlDbType.NVarChar)
plaatje.Value = " "
cmd.Parameters.Add(plaatje)

Dim ArtikelGroep As New Data.SqlClient.SqlParameter("@ArtikelGroep", System.Data.SqlDbType.NVarChar)
ArtikelGroep.Value = ddl1.SelectedValue
cmd.Parameters.Add(ArtikelGroep)

Dim ArtikelNaam As New Data.SqlClient.SqlParameter("@ArtikelNaam", System.Data.SqlDbType.NVarChar)
ArtikelNaam.Value = tb1.Text
cmd.Parameters.Add(ArtikelNaam)


cnn.Open()

cmd.ExecuteNonQuery()

cnn.Close()
End if  Lots of thanks

View 2 Replies View Related

Update From Selected Row

Apr 30, 2007

Hi,

I am having a problem updating one field in a table the update should be the product of two other fields from the same row.
There are atleast 3000+ records need to be updated here.


e.g.

update A
set A.b = A.c * A.D

here b c and d are from same row .. I was wondering if someone knows how to solve this problem.

Thanks in advance.

View 8 Replies View Related

WildCard If Not Value Selected

May 7, 2008

HI Guys, I have a question.
I am converting Access SQL to SQL Server. One of the statements calls for a wildcard if the user does not select a value for the designated parm field. The value is selected from a cbolist (of names).

Current Statement:
And tblRetailer_Contact.faxcontact LIKE *

I substituted:
And tblRetailer_Contact.faxcontact LIKE ‘%@faxContacts%’

This might work if the User selects a name but if the User leaves it blank it will not work. Any ideas on how I go about establishing a wildcard if not name is selected?

DECLARE @FaxContact as varchar (50)

SET @H_Date = (SELECT StartDate FROM tblRpt_Params WHERE RptID = 5)
SET @Start_Date = (REPLACE(REPLACE(CONVERT(VARCHAR (8), @H_Date, 112), '-', ''), ' ', ''))
SET @H_Date = (SELECT EndDate FROM tblRpt_Params WHERE RptID = 5)
SET @End_Date = (REPLACE(REPLACE(CONVERT(VARCHAR (8), @H_Date, 112), '-', ''), ' ', ''))
SET @FaxContact = (SELECT FaxContact FROM tblRpt_Params WHERE RptID = 5)

SELECT tblEData.Timestamp As [TimeStamp],
LTRIM(RTrim([ResultsCustName])) AS CustName,
LTRIM(RTrim([ResultsPH])) AS Phone, Status As [Status],
FaxContact AS FaxContact,
ResultsPKey As ResultsKey
INTO tmpE_Callbacks
FROM tblEData
LEFT JOIN tblContact
ON tblEData.RetailerPrefix = tblContact.Prefix
WHERE tblEData.Timestamp BETWEEN @Start_Date And @End_Date
AND FaxContact Like '%@FaxContact%'

Thanx so much,
Trudye

View 11 Replies View Related

Formatting A Selected Value

Jan 25, 2006

I am new to writing sprocs so forgive me if this is trivial. I am selecting fields from a table and placing them into a temp table in row format. (Row 1 in temp table is the first row in a file that will be created using DTS package). My question is: How can a format a field that I have selected that only has, say 3 chars, into a value of 5.
Ex: field in DB = aaa
I need to format it as: 2 spaces + aaa

But the length of the value will be varying from record to record.

View 7 Replies View Related

Selected Backup

Jan 23, 2008

I need to get the Bacup of my SQl 2000 database
but i need only last 100 records of all tables in my database.
or
i want to create new database from existing , schama is same but i need to import only 1000 record from all tables

View 2 Replies View Related

Selected Value Of Dropdown

Feb 20, 2007

How would I reference the selected value of a dropdown parameter in SSRS using VS2005?

For instance, the city Miami is selected I want to find out if both dropdowns match eachother...

iif(dropdown1.selected.value = dropdown2.selected.value,false,true)

View 9 Replies View Related

Selected Record's Id

Feb 28, 2008

How can I get an Id of selected record? something like




Code Snippet
DECLARE @Id uniqueidentifier

SELECT @Id = ID FROM DataTable




BUT! I need also retrieve a data in the same query. Is that possible or there is a different way? Thanks.

View 4 Replies View Related

No Cube Selected

Oct 8, 2007

Hello,
I've gotton both the sprocs in these tutorials to work in the C# app:
http://www.sqlserverdatamining.com/DMCommunity/TipsNTricks/4503.aspx
http://www.sqlserverdatamining.com/DMCommunity/TipsNTricks/2271.aspx


But when I try to call them in the Managment Studio I get the following error:
"No cube specified. A cube must be specified for this command to work."

What am I missing here?

Thanks in advance,
Adam

View 8 Replies View Related

Get Data From SqlDataSource For Selected Row

Feb 24, 2007

Hello all! How can I get data from SqlDataSource for row selected in GridView?

View 3 Replies View Related

Listbox Selected Values

Dec 1, 2005

Hi. With VWD i've produced the following code.<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:50469ConnectionString %>"SelectCommand="SELECT * FROM [ibs] WHERE ([liedID] = @liedID)"><SelectParameters><asp:ControlParameter ControlID="ListBox1" Name="liedID" PropertyName="SelectedValue" Type="Int16" />But the query is only returning one row of the table. Even when multiple values were selected in the ListBox1. Could someone tell me how to do?Thanks, Kin Wei.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved