Count Records When RecordSource Is A Stored Procedure
Jul 20, 2005
I have a stored procedure named mySP that looks basically like this:
Select Field1, Field2
From tblMyTable
Where Field 3 = 'xyz'
What I do is to populate an Access form:
DoCmd.Openform "frmMyFormName"
Forms!myFormName.RecordSource = "mySP"
What I want to do in VBA is to open frmContinuous(a datasheet form) if
mySP returns more than one record or open frmDetail if mySP returns
only one record.
I'm stumped as to how to accomplish this, without running mySP twice:
once to count it and once to use it as a recordsource.
Thanks,
lq
View 1 Replies
ADVERTISEMENT
Dec 16, 2005
Hi...
I just begining to use the asp.net 2.0 and have tow littel problems... in my project the user makes a "list of problems on a house" when hi post the house number the page shoud to generete a master recod with a keynumber, the date an hour and the state of problems... and a form to insert a list of problems... but if the house dosn´t exist this forms are hidden... then i need to know the nomber of records that gets a recordsource if its more than cero show the forms else hiden it... if show the forms i need to store in a variable the keynumber to store it in the problem details table... ¿How i can khow the number of records of a RecordSource gets?... and ¿How I can store in a variable a field value retrived for a RecordSource?... I´m using VWD and SQL Express that means ADO.NET 2.0...
cheers.
View 1 Replies
View Related
Jul 20, 2005
This seems so easy....change the join to show all records, but thezero records still do not showI want to join 2 tables....basically Customers and Orders....get thetotal number of orders for each Customer within a date range...but Ican't seem to show records where the total for a particular Customeris zero (which is very important info)There must be an easy way???thanks,Paul
View 2 Replies
View Related
Apr 29, 2008
I have a stored procedure that among other things needs to get a total of hours worked. These hours are totaled by another stored procedure already. I would like to call the totaling stored procedure once for each user which required a loop sort of thing
for each user name in a temporary table (already done)
total = result from execute totaling stored procedure
Can you help with this
Thanks
View 10 Replies
View Related
Jul 6, 2007
Hi,
I would like to add custom paging to my ASP.Net pages. How can I use Count(*) in a query such as below to retrieve a record count?
set ANSI_NULLS ONset QUOTED_IDENTIFIER ONGO
ALTER PROCEDURE [dbo].[view_icon_photos] @Filenumber int = '0' , @Location nvarchar(50)= '-1' , @Rubric nvarchar(MAX)= '-1' , @Author nvarchar(75)= '-1' , @startRowIndex int= '-1' , @maximumRows int= '0'
As
IF @Filenumber = '0' AND @location > '-1' AND @Rubric = '-1' AND @Author = '-1' AND @startRowIndex > '-1' AND @maximumRows >'0'Select Icon, Filenumber, RowRankFrom(Select Icon, Filenumber, Row_Number () Over ( Order By Filenumber ASC ) AS RowRankFrom dbo.photosWhere Location = @Location)As IconRank Where RowRank > @startRowIndex AND RowRank <= (@startRowIndex + @maximumRows)
View 2 Replies
View Related
Jul 29, 2007
hello,
i have a list of Categories and each one contains a list of SubCategories, and i use a nested Repeater to show each Category with their SubCategories, but because are to many to show verticaly, i want to make 2 nested repeaters and in the first one to show only the first "n" Categories with their SubCategories and in the second Repeater should be the last n Categories, and like so they will be in 2 colums
How can i use the COUNT function in my stored procedure to take only first n Categories?
I used SELECT * FROM Categories WHERE CategoryID <= 5 but i don't want to order by the primary key i want to order by a COUNT or something like this...
here is my stored procedure (i use it for two nested Repeaters)ALTER PROCEDURE SubCategoriiInCategorii
CategoryID int)
AS
SELECT CategoryID, Name, Description FROM Categories WHERE CategoryID = @CategoryIDORDER BY Name
SELECT p.SubCategoryID, p.Name,p.CategoryID FROM SubCategorii p
ORDER BY p.Name
i hope you understand what i mean, thank you
View 4 Replies
View Related
Apr 19, 2005
what's wrong with the following code? COUNT for completed and exempted doesn't return correct values. they return same values. but when i remove one count it's returning right COUNT. i need to return the COUNT from two tables with a single query. how can i accomplish that??? is there anything wrong with my code below?
<code>
CREATE PROCEDURE dbo.Test AsSELECT s.StudentIdNum, c.[Name], COUNT(*) AS Completed, COUNT(*) AS exempted FROM StudentEnrolled AS s JOIN StudentCourse AS sc ON s.StudentKey = sc.StudentKey JOIN Courses AS c ON sc.CourseID = c.CourseIDLEFT JOIN ModuleEnrolment AS m ON sc.StudentCourseID = m.StudentCourseIDLEFT JOIN ModuleExemption AS e ON sc.StudentCourseID = e.StudentCourseIDWHERE m.Status = "Completed"GROUP BY s.StudentIdNum, c.[Name]
</code>
View 5 Replies
View Related
Feb 23, 2007
I have the following for sql server 2000...
Select b.courseName, a.courseId, count(a.courseId) as [count], avg(convert(INT, a.fldScore)) as [average], count(fldPass) as [passed], count(fldPass) as [failed] From tblTest a inner join tblTest2 b on a.courseId = b.courseId Group by a.courseId, b.courseName
Problem is the [passed] and [failed]
As it is, it's counting all of them.
I need to adjust it so passed will only read where fldPass = 'yes'
and fldPass = 'no' for the passed and failed.
Suggestions?
Thanks,
Zath
View 4 Replies
View Related
Apr 29, 2008
Hi all,
I'm using sql 2005. Can some one please tell me how to write dynamic sql for count. What i want is that i want to count the number of employees existing for the given department names and get the counted values as output into my vb.net 2.0 project. If any one know who to write this please send the code.. Please help me.. I want the below code to change to dynamic sql:
Alter proc GetCountforemp
@DestName varchar(200)=null,
@total int output
as
begin
SELECT @total = Count(distinct Employee.EmployeeID) FROM Employee
INNER JOIN Dest R on R.DestID=Employee.DestID
WHERE R.DestName in ('''+@DestName+''')
end
View 1 Replies
View Related
Aug 5, 2004
Would like to get a total count from Quantity Column for (Each individual ProductID # entered) from the ProductID Column. And insert (the New total Value count into the QtySold Column.
Does anyone have an idea how to write a stored Procedure for this?
The tables name is OrderItems which contains the following columns:
uid, OrderID, ProductID, Quantity, QtySold, ProductName, Price,
Many Thanks
View 5 Replies
View Related
Apr 19, 2005
How can i create a stored procedure that count or sum value of field
e.g.
f1 f2
f3 f4 f5
record 1
1 2
3 1
and get answer like this 1=4 - 2=1 - 3=1
View 1 Replies
View Related
Jun 16, 2005
Having a little trouble with this sp... just need to return the count.....CREATE PROCEDURE dbo.getTotalObjectives @courseId VARCHAR(20) ASBEGIN DECLARE @errCode INT
SELECT count(*) FROM objstructure WHERE courseId = @courseId
SET @errCode = 0 RETURN @errCode Can only return one thing, @errCode, but can return others in the select statement....I did it before like.....SELECT @lessonLocation = lessonLocation FROM cmiDataModel WHERE studentId = @studentIdand got the lessonLocationAnd, in code behind, I know this may be off as well....sObjNum = command.Parameters[ "@courseId" ].Value.ToString();The @courseId should be the count????Thanks all,Zath
View 4 Replies
View Related
Jul 20, 2005
I am trying to determine the number of columns that are returned froma stored procedure using TSQL. I have a situation where users will becreating their own procedures of which I need to call and place thoseresults in a temp table. I will not be able to modify those usersprocedures. I figure if I have the number of columns I can dynamicallycreate a temp table with the same number of columns, at which point Ican then perform an INSERT INTO #TempTableCreatedDynamically EXEC@UserProcCalled. With that said, does anyone have any idea how todetermine the number of rows that an SP will return in TSQL?Thanks!
View 2 Replies
View Related
Aug 21, 2007
Hi again, and so soon...Having just solved my previous issue, I am now trying to call this stored proc from my page.aspx.vb code. Firstly the stored proc looks like this:----------- ALTER PROCEDURE dbo.CountTEstAS SET NOCOUNT ON DECLARE @sql nvarchar(100); DECLARE @recCount int; DECLARE @parms nvarchar(100); SET @sql = 'SELECT @recCount1 = COUNT(*) FROM Pool 'SET @parms = '@recCount1 int OUTPUT' exec sp_executesql @sql, @parms, @recCount1 = @recCount OUTPUTRETURN @recCount -------------When tested from the stored proc, the result is: @RETURN_VALUE = 4 My code that calls this stored proc is: Dim connect As New SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|Database.mdf;Integrated Security=True;User Instance=True") connect.Open() Dim cmd As New SqlCommand("CountTEst", connect) cmd.CommandType = CommandType.StoredProcedure Dim MyCount As Int32 MyCount = cmd.ExecuteScalar connect.Close() So I expext MyCount = 4 but this code returns MyCount = 0 With the RETURN statement in the stored proc, and then calling it via MyCount = cmd.ExecuteScalar, I didn't think I need to explicitly define any other parameters. Any help please. thanks,
View 5 Replies
View Related
Jun 4, 2008
I seem to have a little problem with my SQL. I'm attempting to use a an .NET grid-view to page a list of search results using an ObjectDataSource (ODS). It's a very simple search setup. The ODS retrieves the Search keyword from the query-string sending it to the BLL's GetResult method which then utilises the DAL's version and retrieves the result. Not all of them of course because I'm paging so it only retrieves the first page (Lets say the first 10 items). This part of the SQL works fine as the same SQL method is used else where on the site and it's well tested. But to to use the Paging function I'm using the SelectCountMethod attribute of the ODS. Again this gets the Search term from the QueryString and sends it too the BLL which retrieves the information from the DAL. Now this is where it starts playing up. The Sql count method is as follows and is run as a Stored Procedure: 1 ALTER PROCEDURE dbo.StoreName_Store_GetProductSearchCount
2 (
3 @SearchQuery nvarchar
4 )
5 AS
6 SET NOCOUNT ON
7
8 SELECT COUNT(*)
9 FROM StoreName_Products
10 WHERE Title LIKE '%' + @SearchQuery + '%'
Nice and Easy. Not a Problem... Isn't it? When I run this query by selecting the database in the Server Explorer in VS2005 and running a 'New Query', it retrieves the correct results. (I only run this query from the Start of the SELECT Query in this mode, don't add the stuff above it)When I run this query through the website as intended and retrieves the int from the Stored Procedure, it always returns the entire count of the number of products in the table.At first I thought It might be a simple error with my caching system. Nope, it's not as after I purged it, it still returns the same value. I thought, well maybe just maybe the BLL or the DAL are some point converting it to this number but I'm quite sure it wasn't... and nope it wasn't that either because the ExecuteScalar method was found to be returning this number. So I ran the Stored Procedure by opening it up and simply right clicking the SQL Query and Executing it adding a value for @SearchQuery when it requested. Bingo. It's returning the full number of the products.So my question is... What's the deal? How can the same query return two completely different results? and most of all... How do I overcome this? Is this because it's in a stored procedure and I should be executing it as a SqlCommand from my DAL? I'm a bit confused. Is there a problem running this sort of query in a stored procedure that I don't know about and probably really should?Thankful for any answers,Regards,Luke
View 11 Replies
View Related
Jul 20, 2005
in my procedure, I want to count the number of rows that have erroredduring an insert statement - each row is evaluated using a cursor, soI am processing one row at a time for the insert. My total count tobe displayed is inside the cursor, but after the last fetch is called.Wouldn't this display the last count? The problem is that the count isalways 1. Can anyone help?here is my code,.... cursor fetchbegin ... cursorif error then:beginINSERT INTO US_ACCT_ERRORS(ERROR_NUMBER, ERROR_DESC, cUSTOMERNUMBER,CUSTOMERNAME, ADDRESS1, ADDRESS2, CITY,STATE, POSTALCODE, CONTACT, PHONE, SALESREPCODE,PRICELEVEL, TERMSCODE, DISCPERCENT, TAXCODE,USERCOMMENT, CURRENCY, EMAILADDRESS, CUSTOMERGROUP,CUSTINDICATOR, DT_LOADED)VALUES(@ERRORNUM, @ERRORDESC,@CUSTOMERNUMBER, @CUSTOMERNAME, @ADDRESS1, @ADDRESS2, @CITY,@STATE, @POSTALCODE, @CONTACT, @PHONE, @SALESREPCODE,@PRICELEVEL, @TERMSCODE, @DISCPERCENT, @TAXCODE,@USERCOMMENT, @CURRENCY, @EMAILADDRESS, @CUSTOMERGROUP,@CUSTINDICATOR, @DTLOADED)SET @ERRORCNT = @ERRORCNT + 1END --error--FETCH NEXT FROM CERNO_US INTO@CUSTOMERNUMBER, @CUSTOMERNAME, @ADDRESS1, @ADDRESS2, @CITY, @STATE,@POSTALCODE, @CONTACT,@PHONE,@SALESREPCODE, @PRICELEVEL,@TERMSCODE,@DISCPERCENT, @TAXCODE, @USERCOMMENT, @CURRENCY,@EMAILADDRESS,@CUSTOMERGROUP, @CUSTINDICATOR, @DTLOADED--IF @ERRORCNT > 0INSERT INTO PROCEDURE_RESULTS(PROCEDURE_NAME, TABLE_NAME, ROW_COUNT,STATUS)VALUES('LOAD_ACCOUNTS', 'LOAD_ERNO_US_ACCT', @ERRORCNT, 'FAILEDINSERT/UPDATE')END -- cursorCLOSE CERNO_USDEALLOCATE CERNO_US
View 1 Replies
View Related
Feb 9, 2006
Hi Guys,
I have a sql procedure that returns the following result when I execute it in query builder:
CountE ProjStatus
6 In Progress
3 Complete
4 On Hold
The stored procedure is as follow:
SELECT COUNT(*) AS countE, ProjStatusFROM PROJ_ProjectsGROUP BY ProjStatus
This is the result I want but when I try to output the result on my asp.net page I get the following error:
DataBinder.Eval: 'System.Data.DataRowView' does not contain a property with the name Count.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Web.HttpException: DataBinder.Eval: 'System.Data.DataRowView' does not contain a property with the name Count.Source Error:
Line 271: </asp:TemplateColumn>
Line 272: <asp:TemplateColumn>
Line 273: <itemtemplate> <%# DataBinder.Eval(Container.DataItem, "Count" )%> </itemtemplate>
Line 274: </asp:TemplateColumn>
Line 275: </columns>
My asp.net page is as follows:
<script runat="server">
Dim myCommandPS As New SqlCommand("PROJ_GetProjStatus")
' Mark the Command as a SPROC
myCommandPS.CommandType = CommandType.StoredProcedure
Dim num as integer
num = CInt(myCommand.ExecuteScalar)
'Set the datagrid's datasource to the DataSet and databind
Dim myAdapterPS As New SqlDataAdapter(myCommandPS)
Dim dsPS As New DataSet()
myAdapter.Fill(dsPS)
dgProjSumm.DataSource = dsPS
dgProjSumm.DataBind()
myConnection.Close()
</script>
<asp:datagrid id="dgProjSumm" runat="server"
BorderWidth="0"
Cellpadding="4"
Cellspacing="0"
Width="100%"
Font-Names="Verdana,Arial,Helvetica; font-size: xx-small"
Font-Size="xx-small"
AutoGenerateColumns="false">
<columns>
<asp:TemplateColumn HeaderText="Project Summary" HeaderStyle-Font-Bold="true" >
<itemtemplate> <%# BgColor(DataBinder.Eval(Container.DataItem, "ProjStatus" ))%> </itemtemplate>
</asp:TemplateColumn>
<asp:TemplateColumn>
<itemtemplate> <%# DataBinder.Eval(Container.DataItem, "Count" )%> </itemtemplate>
</asp:TemplateColumn>
</columns>
</asp:DataGrid>
Please help if you can Im havin real trouble here.
Cheers
View 3 Replies
View Related
Mar 12, 2015
I have a process that keeps check on the row counts of about 100 stored procedures. The input parameters and "certified" row counts for all of the stored procedures are stored in a database. The process runs every day and executes all of the stored procedures using the parameters from the database with syntax below. The row count returned is compared against the known "certified" row count. If the counts are different, we receive an email alerting us that something has changed with the data or the sp query.
(This code is dynamically generated for all 100 + stored procedures)
SELECT COUNT(*) FROM OPENQUERY(SQLSERVER,'EXEC 'usp_HR_My_Stored_Procedure @inputparam1="12345",@inputparam2="12345"')
This worked well until I upgraded from SQL Server 2008 R2 to SQL Server 2014. Evidently Microsoft fixed this for me. The error below is now received anytime we attempt to execute a stored procedure with dynamic SQL through OPENQUERY.
The metadata could not be determined because statement 'EXEC (@sql_str)' in procedure 'usp_HR_My_Stored_Procedure ' contains dynamic SQL. Consider using the WITH RESULT SETS clause to explicitly describe the result set.
The stored procedures that are monitored change frequently, so it isn't reasonable to create tables with fixed column structures for all for all of the stored procs.
View 5 Replies
View Related
Jul 14, 2015
I need to create a stored procedure for total count of the user's. If User from front end invites other user to use my tool, that user will be stored into a table name called "test",lets say it will be stored as"Invited 1 User(s)" or if he invites 2 users it will store into table as "Invited 2 User(s)."
But now we have changed the concept to get the ISID (name of the user) and now when ever the user invites from the front end, the user who have invited should stored in two tables "test" and " test1" table .
After we get the data into test and test1 table i need the total count of a particular user from both tables test and test1.
if i invite a user , the name of the user is getting stored in both test and test1 tables.Now i want to get the count of a user from both tables which should be 1,but its showing 2.
Reason: Why i am considering the count from 2 tables is because before we were not tracking the usernames and we were storing the count in single test table.,but now we are tracking user names and storing them in both tables(test and test1).
Here is my sample code:
I need to sum it up to get the total user's from both the table but I should get 1 instead of 2
SELECT
(select distinct COUNT(*) from dbo.test
where New_Values like '%invited%'
and Created_By= 'sam'
+
(select distinct count (*) from dbo.test1
where invited_by ='sam'
View 8 Replies
View Related
Jan 19, 2007
hi all,
I want to know that how can i write a stored procedure that takes an input param, i.e., a string and returns the count of the matching words between the input parameter and the value of a column in a table. e.g. I have a table Person with a column address. now my stored procedure matches and counts the number of words that are common between the address of the person and the input param string. I am looking forward for any help in this matter. I am using Sql server 2005.
View 1 Replies
View Related
Jul 9, 2014
SQL Server 2012 Standard SP 1.
Stored procedure A calls another stored procedure B. Rowcount is set properly in called procedure B, but does not seem to return it to calling procedure A. Otherwise the two stored procedures are working correctly. Here is the relevant code from the calling procedure A:
declare @NumBufferManagerRows int = 0;
exec persist.LoadBufferManager @StartTicks, @EndTicks, @TimeDiff, @NumBufferManagerRows;
print 'BufferManagerRows';
print @NumBufferManagerRows;
Print statement prints @NumBufferManagerRows as 0.
Here is the called stored procedure B:
CREATE PROCEDURE [persist].[LoadBufferManager]
-- Add the parameters for the stored procedure here
@StartTicks bigint,
@EndTicks bigint,
@TimeDiff decimal(9,2),
@NumRows int OUTPUT
[Code] ...
View 2 Replies
View Related
Sep 8, 2015
I am run a stored procedure using Execute SQL task in, I want to log information of number of record inserted update in my table. I want to enable SSIS logging after from where I get information number of record inserted update.
I am using SQL server 2008R2 standard edition.
View 4 Replies
View Related
Jun 2, 2008
How can I use the data returned from a stored procedure within another stored procedure?
For example, I trying to something along these lines:
select * from tbl_Test
union all
select * from (exec sp_Test)
View 2 Replies
View Related
Mar 23, 2006
I am trying to insert a record in a SQL2005 Express database. I can use the sp fine and it works inside of the database, but when I try to launch it via ASP.NET it fails...
here is the code. I realize it is not complete, but the only required field is defined via hard code. The error I am getting states it cannot find "sp_InserOrder"
===
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim conn As SqlConnection = Nothing
Dim trans As SqlTransaction = Nothing
Dim cmd As SqlCommand
conn = New SqlConnection(ConfigurationManager.ConnectionStrings("PartsConnectionString").ConnectionString)
conn.Open()
trans = conn.BeginTransaction
cmd = New SqlCommand()
cmd.Connection = conn
cmd.Transaction = trans
cmd.CommandText = "usp_InserOrder"
cmd.CommandType = Data.CommandType.StoredProcedure
cmd.Parameters.Add("@MaterialID", Data.SqlDbType.Int)
cmd.Parameters.Add("@OpenItem", Data.SqlDbType.Bit)
cmd.Parameters("@MaterialID").Value = 3
cmd.ExecuteNonQuery()
trans.Commit()
=====
I get an error stating cannot find stored procedure. I added the Network Service account full access to the Web Site Directory, which is currently running locally on Windows XP Pro SP2.
Please help, I am a newb and lost...as you can tell from my code...
View 4 Replies
View Related
Nov 14, 2000
What is the preferred method of returning a set of records from a stored procedure? Could you please provide a short example?
Thanks ahead of time!
G.
View 3 Replies
View Related
Jul 14, 2004
I am trying to count records in my stored procedure. Can someone please help me.
these are the two procedures I am using
Alter Procedure usp_rptQualityReport As
SELECT
tblRMAData.RMANumber,
tblRMAData.JobName,
tblRMAData.Date,
tblFailureReasons.LintItemID,
tblLineItems.Qty,
tblLineItems.Model,
tblLineItems.ReportDate,
tblFailureReasons.FailureReason,
tblTestComponentFailures.ComponentID,
tblTestComponentFailures.FailureCause
FROM
tblRMAData INNER JOIN ((tblLineItems INNER JOIN tblTestComponentFailures ON tblLineItems.ID = tblTestComponentFailures.LineItemID) INNER JOIN tblFailureReasons ON tblLineItems.ID = tblFailureReasons.LintItemID) ON tblRMAData.RMANumber = tblLineItems.RMANumber
WHERE
(((tblFailureReasons.FailureReason) <> N'NONE'))
ORDER BY
tblFailureReasons.FailureReason
Alter Procedure usp_rptQualityReport2 As
exec usp_rtpQualityReport
SELECT
usp_rptQualityReport.RMANumber,
usp_rptQualityReport.JobName,
usp_rptQualityReport.Date,
usp_rptQualityReport.LintItemID,
usp_rptQualityReport.Qty,
usp_rptQualityReport.Model,
usp_rptQualityReport.ReportDate,
usp_rptQualityReport.FailureReason,
usp_rptQualityReport.ComponentID,
usp_rptQualityReport.FailureCause,
(SELECT COUNT(FailureReason) FROM usp_rptQualityReport a WHERE a.FailureReason=usp_rtpQualityReport.FailureReason ) AS groupingLevel
FROM usp_rptQualityReport;
View 3 Replies
View Related
Sep 20, 2014
Creating a stored procedure to insert records into multiple rows.
Let's say we have 3 colums in procedure to insert, col3 has a max limit of 1000, if col3 data exceeds limit it has insert into next row and vice versa.
View 1 Replies
View Related
Oct 11, 2007
Hi,
I need to know whether a stored procedure returns only a single record or it can return more than one if the query if for example to return all records in a specific date range.
Thanks.
View 6 Replies
View Related
Mar 27, 2008
I have two tables called A and B and C. Where A and C has the same schema
A contains the following columns and values
-------------------------------------------
TaskId PoId Podate Approved
1 2 2008-07-07 No
3 4 2007-05-05 No
5 5 2005-08-06 Yes
2 6 2006-07-07 Yes
Table B contains the following columns and values
-------------------------------------------------
TaskId TableName Fromdate Approved_Status
1 A 7/7/2007 No
3 B 2/4/2006 Yes
Now i need to create a stored procedure that should accept the values (Yes/No) from the Approved_Status column in Table B and should look for the same values in the Approved column in Table A. If both values match then the corresponding rows in Table A should be archived in table C which has the same schema as that of Table A. That is the matching columns should get deleted from Table A and shoud be inserted into Table C. In both the tables A and i have the column TaskId as the common column
Pls provide me with full stored procedure code.
C.R.P RAJAN
View 1 Replies
View Related
Dec 1, 2006
Hi All,
I have a Execute SQL Task I get some values from a table onto three variables. Next step in a DFT, I try to execute a stored proc by passing these variables as parameters.
EXEC [dbo].[ETLloadGROUPS]
@countRun =?,
@startTime =?,
@endTime = ?
This is the syntax i use, in the parameters tab of the DFT I ensured that all the parameters are correctly mapped.
When I run the package, it executes successfully but no rows are fectched. I tried running the stored proc manually in the database, and it seems to work fine.
Am I missing something here ? Please Advice
Thanks in Advance
View 9 Replies
View Related
Mar 27, 2008
I have two tables called A and B and C. Where A and C has the same schema
A contains the following columns and values-------------------------------------------TaskId PoId Podate Approved
1 2 2008-07-07 No 3 4 2007-05-05 No 5 5 2005-08-06 Yes 2 6 2006-07-07 Yes
Table B contains the following columns and values-------------------------------------------------TaskId TableName Fromdate Approved_Status
1 A 7/7/2007 No3 B 2/4/2006 Yes
Now i need to create a stored procedure that should accept the values (Yes/No) from the Approved_Status column in Table B and should look for the same values in the Approved column in Table A. If both values match then the corresponding rows in Table A should be archived in table C which has the same schema as that of Table A. That is the matching columns should get deleted from Table A and shoud be inserted into Table C. In both the tables A and B i have the TaskId as the common column
Pls provide me with full stored procedure code.
View 2 Replies
View Related
Feb 11, 2004
Hi,
I have written the following stored procedure:
alter proc GetProducts
@prodcatint=null
as
select distinct pd.productcategory,pd.imagepath,pd.[description],p.productid,p.[name]
,p.designer,p.weight,p.price
from productdescription pd inner join products p on pd.productcategory=p.productcategory
where @prodcat=p.productcategory
order by p.productid
return
My Results are:
ProductCategory ProductID (Rest of the columns)
22 47
22 47
22 58
22 58
In my productdescription table there are 2 rows in the productcategory column which has number 22. In the products table there are 2 rows(productid 47&58) in the productcategory column which has number 22. I believe this is many to many relationship problem but I do not know how to correct it. My results need to show only 2 records and not 4.
Does anybody have any suggestions.
Thank you in advance,
poc1010
View 4 Replies
View Related
Apr 18, 2006
I have a stored procedure on an SQL Server database which displays statistical data for a single record. The example below selects a user ID as a parameter and displays the user's name and the total amount of transactions he has made:
CREATE PROCEDURE dbo.GetUserStats @UserID BIGINT AS
DECLARE @TempTable TABLE
(
UserID BIGINT,
UserName VARCHAR(60),
TotalAmt FLOAT
)
INSERT INTO @TempTable (UserID, UserName, TotalAmt)
SELECT u.RecID, u.LastName + ', ' + u.FirstName,
(SELECT SUM(t.Amount) FROM Transactions t WHERE t.UserID = @UserID)
FROM Users u
WHERE u.RecID = @UserID
SELECT * FROM @TempTable
GO
So if I execute this amount entering a single ID, it returns a single row for that user:
UserID UserName TotalAmt
--------------------------
1 Doe, John 100.00
What I would like to do is create another stored procedure which calls this one for every user returned in a query, thus returning the same data for several users:
UserID UserName TotalAmt
--------------------------
1 Doe, John 100.00
2 Smith, Bob 123.45
3 Blow, Joe 150.55
Is there a way to re-use a stored procedure within another, based on the results of a query?
View 7 Replies
View Related
