Passing Multiple Selections To A Stored Proc Parameter

Jan 11, 2005

Hi,

I am currently in the process of building a stored procedure that needs the ability to be passed one, multiple or all fields selected from a list box to each of the parameters of the stored procedure. I am currently using code similar to this below to accomplish this for each parameter:

CREATE FUNCTION dbo.SplitOrderIDs
(
@OrderList varchar(500)
)
RETURNS
@ParsedList table
(
OrderID int
)
AS
BEGIN
DECLARE @OrderID varchar(10), @Pos int

SET @OrderList = LTRIM(RTRIM(@OrderList))+ ','
SET @Pos = CHARINDEX(',', @OrderList, 1)

IF REPLACE(@OrderList, ',', '') <> ''
BEGIN
WHILE @Pos > 0
BEGIN
SET @OrderID = LTRIM(RTRIM(LEFT(@OrderList, @Pos - 1)))
IF @OrderID <> ''
BEGIN
INSERT INTO @ParsedList (OrderID)
VALUES (CAST(@OrderID AS int)) --Use Appropriate conversion
END
SET @OrderList = RIGHT(@OrderList, LEN(@OrderList) - @Pos)
SET @Pos = CHARINDEX(',', @OrderList, 1)

END
END
RETURN
END
GO


I have it working fine for the single or multiple selection, the trouble is that an 'All' selection needs to be in the list box as well, but I can't seem to get it working for this.

Any suggestions?

Thanks

My plan is to have the same ability as under the 'Optional' section of this page:

http://search1.workopolis.com/jobshome/db/work.search_cri

View 1 Replies


ADVERTISEMENT

SQL2005 Passing GETDATE() As A Stored Proc Parameter

May 2, 2008

What happened to being able to pass GETDATE() to a stored procedure? I can swear I've done this in the past, but now I get syntax errors in SQL2005.
Is there still a way to call a stored proc passing the current datetime stamp? If so, how?
This code: EXEC sp_StoredProc 'parm1', 'parm2', getdate()
Gives Error: Incorrect Suntax near ')'
I tried using getdate(), now(), and CURRENT_TIMESTAMP with the same result
I know I can use code below but why all the extra code? And, what if I want to pass as a SQL command [strSQL = "EXEC sp_StoredProc 'parm1', 'par2', getdate()" -- SqlCommand(strSQL, CN)]?
DECLARE @currDate DATETIME
SET @currDate = GETDATE()
EXEC sp_StoredProc 'parm1', 'parm2', @currDate
Thanks!

View 5 Replies View Related

Passing Datetime Variable To Stored Proc As Parameter

Jun 12, 2006

Hello,

I'm attempting to pass a datetime variable to a stored proc (called via sql task). The variables are set in a previous task where they act as OUTPUT paramters from a stored proc. The variables are set correctly after that task executes. The data type for those parameters is set to DBTIMESTAMP.

When I try to exectue a similar task passing those variables as parameters, I get an error:

Error: 0xC002F210 at ax_settle, Execute SQL Task: Executing the query "exec ? = dbo.ax_settle_2 ?, ?,?,3,1" failed with the following error: "Invalid character value for cast specification". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

If I replace the 2nd and 3rd parameters with quoted strings, it is successful:
exec ?= dbo.ax_settle ?, '3/29/06', '4/30/06',3,1

The stored proc is expecting datetime parameters.

Thanks for the help.

Mike

View 3 Replies View Related

How Do Use Stored Proc Passing Parameter From Table In Selcet Query Statement

Aug 8, 2002

i want to use store procedure in select query statement. store procedure will take two parameters from table and return one parameter.

for example i want to use

select p1 = sp_diff d1,d2 from table1

sp_diff is stored procedure
d1,d2 value from table
p1 is the returning value

View 1 Replies View Related

Transact SQL :: Passing Multiple String Param Values To Stored Proc

Jul 21, 2015

CREATE TABLE Test
(
EDate Datetime,
Code varchar(255),
Cdate int,
Price int
);

[Code] ....

Now I have to pass multiple param values to it. I was trying this but didnt get any success

exec
[SP_test]'LOC','LOP'

View 10 Replies View Related

T-SQL (SS2K8) :: Passing Multiple Parameters With Table Valued Parameter To Stored Procedure?

May 21, 2014

Can we Pass table valued parameters and normal params like integer,varchar etc..to a single stored procedure?

View 1 Replies View Related

Passing A Qry In As A Parameter To Proc?

Jul 19, 2004

possible to pass in a query as a parameter to a stored proc?

Number of constraints right now would make it a lot easier if I could pass in a query that selects all the ID's, tried but couldn't come up w/anything, just have a simple proc that does the deletes on 1 ID @ a time...since there are up to 100 that will need to be deleted, the qry as a param would be much more convenient. below is the proc...Thx for any help.

Code:


CREATE PROCEDURE [dbo].[s_DeletePeople]

@PeopleID int
/* single id to be deleted, tried just passing
* in a query that resembled a string and using it
* but it didn't work either.
*/

AS

Deletefrom tProjectManager
whereManager_ID in (@PeopleID)

Deletefrom tMerchandiser
whereID in (@PeopleID)

Deletefrom tProjectCall
wheremerchandiser_id in (@PeopleID)

Delete from tManager
whereID in (@PeopleID)

Deletefrom tDistrictManager
whereID in (@PeopleID)

Deletefrom tPeople
whereID in (@PeopleID)

....and on and on
GO

View 2 Replies View Related

Passing Value From Stored Proc To Another

Jan 10, 2007

Guys
I have a simple 'black box' proc deriving start and end dates below (the actual values will be derived from more complex code but here for simplicity are hard coded)

create proc upGetDates
as
declare @start datetime ,
@end datetime

select@start = '2001-01-01' ,
@end = '2007-12-31'
go

I want to reuse this proc many times for different reports and using the date values in the calling sp

an example in pseudo code would be

create proc usedates
as
declare@rstart datetime ,
@rend datetime

exec upGetDates @rstart = @start , @rend = @end -- obtaining dates from ---called 'black box' proc
select * from tablename where datecreated >= @rstart and datecreated < @rend
go

Not sure how to 'trap' the dates from the called proc

Can it be done ?

Thanks in anticipation

View 3 Replies View Related

Passing A Column Into A Stored Proc?

Sep 7, 2006

I'm writing a simple voting script and have columns for each options.  I need to update the data based on whichever option the user picks.I.e...If the user picks option 1 then execute UPDATE mytable SET option1 = option1 + 1If the user picks option 2 then execute UPDATE mytable SET option2 = option2 + 1 Etc., etc.What's the best way to do that without building an ad-hoc SQL statement?  There could be many options so I dont want to have lots of redundant SQL statements.Can I just use a varible in a stored proc and do something like this? UPDATE mytable SET @optionUserpicked=@optionUserpicked + 1Thanks in advance 

View 2 Replies View Related

Passing Parameters To Stored Proc

Oct 23, 2007



I have created a stored proc for a report, which works fine. I want to have the user enter a project ID to filter the report, so set the stored proc accordingly. However, I want the user to enter a 10 digit ID, which is the equivilent of two fields in the stored proc. My where statement is :

Where actv.ProjID + '-' + actv.Activity = @project

This works fine under the data tab, I can enter a full project and get the results I want. But I cannot preview the report without getting an error. I'm not sure how to add this to the report parameters, as it is two fields concatenated together. Any help would be appreciated!

View 3 Replies View Related

Passing Multiple Value In Single Parameter

Mar 9, 2014

How do I pass the multiple value in the single parameter? Here is the select simple select statement with two fields (ID and Map_Area):

SELECT *
FROM TestParamOne

Here is the output:

ID Map_Are
12345KK45
657463IIY7
34345FGD
44342DFRE
4646DSAW
424245DSAW
12121DSAW
5753FRDE
575737FRDE
1121FRDE
1121F5FR
646462F5FR
8568F5FR

Here is my simple stored proc. Right know I only can execute with one value at the time but I am trying to run the stored proc with more than one value.

CREATE PROCEDURE TestParam1
@Map varchar(4)
AS
BEGIN
SET NOCOUNT ON;

SELECT ID, Map_Area
FROM TestParamOne
WHERE Map_Area=@Map
END
GO

View 7 Replies View Related

Passing Boolean To Stored Proc As SQLDBtype.bit Not Working

Mar 17, 2004

Hi I was hoping that someone might be able to help me with this.

I'm trying to figure out why my VB.net code below generates 0 or 1 but doesn't insert it when I can execute my stored procedure with: exec sp 0

myParm = myCommand.Parameters.Add("@bolProMembCSNM", SqlDbType.Bit)
myParm.Value = IIf(CBool(rblProMembCSNM.SelectedItem.Value) = True, 1, 0)

I've tried everything I used to use with Classic ASP and am stumped now.
Any ideas? I will have to do this for numerous controls on my pages.

Thanks in advance for any advice.

View 4 Replies View Related

Passing Table Variable To Stored Proc / Function

Nov 6, 2002

Hi all,
Is it possible to pass a table variable to a Stored proc or a function?
If it is can you give me the sentax.

TIA,

View 3 Replies View Related

Passing A Comma Delimited String Of Parameters To A Stored Proc

Jul 30, 2007

Hello,
I have a number of multi-select parameters which I would like to send to a stored procedure within the dataset for use in the stored procedure's IN() statement which in turn is used to filter on or out particular rowsets.

I considered using a hidden string parameter set = " ' " + join(parameter.value, ',') + " ' " so that the hidden parameter would then contain a comma delimiated string of the values selected, which would then be sent on to the stored proc and used in the WHERE clause of one of the queries internal to the stored proc.
But before I start dedicating time to do this I wanted to inquire if anyone here with far more expertise could think of a faster or less system heavy method of creating a single string of comma delimited parameter selections?

Thanks.

View 3 Replies View Related

Question On Passing Multi-value Parameter For Multiple Branches

Apr 6, 2007

Hello. We are using asp .net and reporting services, and trying to pass a multi-value parameter into reporting services that will show data for multiple branches.





Dim paramList As New Generic.List(Of Microsoft.Reporting.WebForms.ReportParameter)



paramList.Add(New Microsoft.Reporting.WebForms.ReportParameter("BranchNumber", 1))



ReportViewer1.ServerReport.SetParameters(paramList)



pInfo = ReportViewer1.ServerReport.GetParameters()





Let me know if you have any suggestions!



Thanks.

View 4 Replies View Related

Passing Multiple String Values Separted By A Comma As One Parameter

Oct 16, 2007



Hello,



I have a stored procedure that accepts one parameter called @SemesterParam. I can pass one string value such as €˜Fall2007€™ but what if I have multiple values separated by a comma such as 'Fall2007','Fall2006','Fall2005'. I still would like to include those multiple values in the @SemesterParam parameter. I would be curious to hear from some more experienced developers how to deal with this since I am sure someone had to that before.



Thanks a lot for any feedback!

View 6 Replies View Related

Multi Value Parameter - Limit Number Of Selections

Jun 12, 2007

Hello



Anyone know if it is possible to limit the number of selections in a multi value parameter? Eg: There are 50 values in the drop down combo, but I want the user to be able to select a maximum of 10?



Cheers

View 1 Replies View Related

Reporting Services :: Multiple Valued Parameter Passing In SSRS For Getting Performance

Aug 13, 2015

we are using SSRS 2012.Oracle 9i as back end database. Select A,B,C from view where A in (Param1) in above query when I am passing 1 value getting output in 30 sec. when I passed two values getting time out error in SSRS. how to pass multiple values in Oracle 9i in SSRS report.

View 4 Replies View Related

Need A Way To Handle Multiple Selections

Oct 31, 2007

I have a webform that lists all items (codes) on the left and selected items (codes) on the right. A user selects an item on the left and clicks a button to move it to the right side. An update changes the bit from 0 to 1. This uses the bit column in the table to determine what is listed on the left (0) or right (1) sides.
Then I can filter in my stored procedure on the bit column WHERE (dbo.tblCodes.CodeSelect = 1)
My problem with this is that if two or more users are doing this process on different sessions, they can trip over each others selections.
I'm hoping someone has a suggestion on how I might avoid the users having this problem.

View 3 Replies View Related

Stored Proc O/P Parameter

Jan 30, 2002

I have 2 stored proc.Stored proc1(sp1) will call stored proc2(sp2).sp2 will return one output parameter of VARCHAR(5000) to sp1.Sp1 will gets the o/p parameter and stores it to a table.

My problem is while returning sp2 output parameter will truncate the size of the o/p I'm getting a part of it's actaul output.I am using SQL server 2000.How we can solve this truncation?

View 1 Replies View Related

Parameter Passing To A Stored Procedure

Mar 31, 2004

Hey huys, I got this stored procedure. First of all: could this work?

--start :)
CREATE PROCEDURE USP_MactchUser

@domainUserID NVARCHAR(50) ,

@EmployeeID NVARCHAR(50) ,

@loginType bit = '0'
AS

INSERT INTO T_Login

(employeeID, loginType, domainUserID)

Values
(@EmployeeID, @loginType, @domainUserID)
GO
--end :)

then I got this VB.Net code in my ASP.net page....

---begin :)
Private Sub matchUser()
Dim insertMatchedUser As SqlClient.SqlCommand
Dim daMatchedUser As SqlClient.SqlDataAdapter
SqlConnection1.Open()
'conn openned
daMatchedUser = New SqlClient.SqlDataAdapter("USP_MatchUser", SqlConnection1)
daMatchedUser.SelectCommand.CommandType = CommandType.StoredProcedure
daMatchedUser.SelectCommand.Parameters.Add(New SqlClient.SqlParameter("@EmployeeID", SqlDbType.NVarChar, 50))
daMatchedUser.SelectCommand.Parameters.Add(New SqlClient.SqlParameter("@domainUserID", SqlDbType.NVarChar, 50))
daMatchedUser.SelectCommand.Parameters("@EmployeeID").Value = Trim(lblEmployeeID.Text)
daMatchedUser.SelectCommand.Parameters("@domainUserID").Value = Trim(lblDomainuserID.Text)
daMatchedUser.SelectCommand.Parameters("@EmployeeID").Direction = ParameterDirection.Output
daMatchedUser.SelectCommand.Parameters("@domainUserID").Direction = ParameterDirection.Output
SqlConnection1.Close()
'conn closed

End Sub
---

If I try this it doesn't work (maybe that's normal :) ) Am I doing it wrong. The thing is, in both label.text properties a values is stored that i want to as a parameter to my stored procedure. What am I doing wrong?

View 2 Replies View Related

Stored Proc Parameter For Table Name

Sep 29, 2004

Recently someone told me that I could use a Parameter in a Stored Proc as a text placeholder in the SQL Statement. I needed to update a table by looping thru a set of source tables. I thought NOW IS MY TIME to try using a parameter as a table name. Check the following Stored Proc

CREATE PROCEDURE [dbo].[sp_Update]
@DistributorID int,
@TableName varchar(50)
AS
UPDATE C
SET C.UnitCost = T.[Price]
FROM (tbl_Catalog C INNER JOIN @TableName T ON C.Code = T.Code)
GO

NEEDLESS TO SAY this didn't work. In reviewing my references this seems to be a no no.

Is it possible to use a parameter as a table name? OR is there another way to do this?

Thanks in advance for your help!

View 3 Replies View Related

Stored Proc With Getdate Parameter

Apr 7, 2004

trying to create SP with parameter and i want to use current date getdate() as parameter.. doesn't seem to work. do i have to use getdate in where clause?

here my SP

CREATE PROC report
(@date datetime)
SET @date = (getdate())-1
as
SELECT..here goes my select statement
where (@date = mydatecolumindatebase)

but im getting error on line 3 and 4
........
Server: Msg 156, Level 15, State 1, Procedure getdatetest, Line 3
Incorrect syntax near the keyword 'SET'.
Server: Msg 156, Level 15, State 1, Procedure getdatetest, Line 4
Incorrect syntax near the keyword 'as'.

View 14 Replies View Related

Problem With Stored Proc With More Than One Parameter

May 27, 2008

Hi!

I'm trying to execute a SP on a SQL Server 2000, using Delphi 2007 (win32) and DBExpress components.

Work on my computer. Don't work on computers without the delphi instaled.
its not a problem with DLLs. All the Necessary DLL are there (and I think that if one was missing, the windows will call for it hauauh)

Midas.dll is inside the apllication and he driver for the SQL Server is there too.

I don't know if this is the corect place to put my problem... But don't can think of other place...

The SP has this:





Code Snippet

IF EXISTS
(
SELECT *
FROM dbo.sysobjects
WHERE id = object_id(N'[dbo].[ms_TESTE]')
AND
OBJECTPROPERTY(id, N'IsProcedure') = 1
)
DROP PROCEDURE [dbo].[ms_TESTE]
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_NULLS ON
GO

CREATE PROCEDURE ms_TESTE
(
@Id varchar(12),
@Nome varchar(500)
)

AS

SELECT *
FROM wtDocAM
WHERE
convert(nvarchar(12), idDOcAM) LIKE @Id
and
nom LIKE @Nome
GO

SET QUOTED_IDENTIFIER OFF
GO

SET ANSI_NULLS ON
GO
I assure that I'm passing both the parameters.

One test was with 8% for the @Id and %a% for Nome.

Anyone have experienced this error?

I´d like to hear a solution if anyone can help me
Thanks in advance!

View 6 Replies View Related

Oracle Stored Proc With OUT Parameter

Nov 30, 2006

Hi,

I am calling an Oracle stored proc which contains an IN and an OUT parameter also.

To the stored proc, I pass two reports parameteres. I get following error when I execute the report:

PLS-00306: wrong number or types of arguments in call to <Procedure name>

Where am I going wrong?

TIA,

Tanmaya

View 3 Replies View Related

Running Stored Proc With Parameter

Sep 3, 2006

hi,

im getting an error when i run the stored proc with a string parameter in execute sql task object.

this is the only code i have:

exec sp_udt_keymaint 'table1'

I also set the 'Isstoredprocedure' in the properties as 'True' though, when you edit the execute sql task object, i can see that this parameter is disabled.

How do i do this right?

cherrie

View 3 Replies View Related

Passing A Parameter Into A Stored Procedure In A Report ..........

May 8, 2007

Hi,  I have found this question asked many times online, but the answers always consisted of getting a parameter from the user and to the report.  That is NOT what everyone was asking.  I would like to know how to pass a parameter I already have into my stored procedure. Using Visual Studio 2005, in the "Data" tab of a report calling something likeUnique_Login_IPsreturns correctly when that stored proc takes no params, but when I have one that takes the year, for example, I do not know how to pass this info in.  Note: the following does not work in this context : EXEC Unique_Login_IPs ParameterValue   nor does  EXEC Some_proc_without_paramsVisual studio seems to want only the procedure name because it returns errors that say a procedure with the name "the entire line above like EXEC Unique_Login_IPs ParameterValue" does not exist.... Thanks in advance,Dustin L 

View 1 Replies View Related

Passing Dropdownlistitem As A Parameter To Stored Procedure

Apr 25, 2008

I am having a dataentry screen. Some columns i will fill the columns by typing and for some columns i will choose an item from the dropdownlist and i have to send it as an input parameter to stored procedure. How to do that? Pls explain
Thanx

View 3 Replies View Related

Error On Passing Parameter To Stored Procedure

Jul 1, 2004

Hi,

I have a procedure that will save to table in sql server 200 via stored procedure. When I hit the save button it alwasy give me an error saying "Procedure 'sp_AddBoard' expects parameter '@dtmWarrantyStart', which was not supplied" even though I supplied it in the code

which is

Dim ParamdtmWarrantyStart As SqlParameter = New SqlParameter("@dtmWarrantyStart", SqlDbType.datetime, 8)
ParamdtmWarrantyStart.Value = dtmWarrantyStart
myCommand.Parameters.Add(ParamdtmWarrantyStart)

below is the stored procedure.

create Proc sp_AddBoard(@BrandID int,
@strPcName varchar(50),
@bitAccounted bit,
@dtmAccounted datetime,
@dtmWarrantyStart datetime,
@dtmWarrantyEnd datetime,
-- @strDescription varchar(500),
@intStatus int,
@ModelNo varchar(50),
@intMemorySlots int,
@intMemSlotTaken int,
@intAgpSlots int,
@intPCI int,
@bitWSound bit,
@bitWLan bit,
@bitWVideo bit,
@dtmAcquired datetime,
@stat bit output,
@intFSB int) as
if not exists(select strPcName from tblBoards where strPcName=@strPcName)
begin
insert into tblBoards
(BrandID, strPcName, bitAccounted,
dtmAccounted, dtmWarrantyStart,
dtmWarrantyEnd, --strDescription,
intStatus,
ModelNo, intMemorySlots, intMemSlotTaken,
intAgpSlots, intPCI, bitWLan,
bitWVideo, dtmAcquired,intFSB,bitWSound)

values

(@BrandID,@strPcName,@bitAccounted,
@dtmAccounted,@dtmWarrantyStart,
@dtmWarrantyEnd,--@strDescription,
@intStatus,
@ModelNo,@intMemorySlots,@intMemSlotTaken,
@intAgpSlots,@intPCI,@bitWLan,
@bitWVideo,@dtmAcquired,@intFSB,@bitWSound)
end
else
begin
set @stat=1
end

The table is also designed to accept nulls on that field but still same error occured.

Please help

View 1 Replies View Related

Passing Int Parameter To Stored Procedure Question.

Jul 1, 2005

Hi all,
I had created a stored procedure "DeleteRow" that can receive a parameter "recordID" from the calling function, however, I received a error msg "Procedure or function deleteRow has too many arguments specified." when run the  C# code.My code is showing below---------------------------------- --------------------thisConnection.Open();SqlParameter param = DeleteRow.Parameters.Add("@recordI D", SqlDbType.Int, 4); param.Value = key;SqlDataReader reader = DeleteRow.ExecuteReader(CommandBeh avior.CloseConnection) //program stop after runing this lineThe stored procedure code which is showing below:----------------------------------------------------------------CREATE PROCEDURE dbo.deleteRow @recordID INT AS DELETE FROM ShoppingCart WHERE RecordID = @recordIDCan anyone give me some ideal why this happen. Thank alot.wing

View 3 Replies View Related

Passing Fields As Parameter Of Stored Procedure

Jul 6, 2000

Does anyone know how to pass a list of fields into a stored procedure and then use those fields in a select statement. Here's what I'm trying to do.

I have a front end application that allows the user to pick the fields they want return as a record set. Currently, all this is being done in the application. But, I'd like SQL to do it, if it's possible.


I'd pass the following into a stored procedure.

Set @Fields = "First, Last, ID" -- This is done in the application

Exec usp_return @Fields

Obviously, the following fails stored procedure doesn't work ...

CREATE PROCEDURE @FIELDS varchar(255) AS

SELECT @FIELDS FROM MY_TABLE

~~~~~~~~~~~~~~~

Any ideas?

MAPMAN

View 1 Replies View Related

Passing A Stored Procedure Parameter Into An IN Clause

Jul 30, 2007

Hi All :)

I have a stored procedure which, initially, I had passed a single parameter into a WHERE clause (e.g ...WHERE CustomerCode = @CustCode). The parameter is passed using a DECommand object in VB6.

I now require the sp to return values for more than one customer and would like to use an IN clause (e.g ...WHERE CustomerCode IN(@CustCode). I know I could create multiple parameters (e.g. ...WHERE CustomerCode in (@CustCode1, @CustCode2,...etc), but do not want to limit the number of customers.

If I set CustCode to be KA1001, everything works fine. If I set CustCode to be KA1001, KA1002 it does not return any records.

I think the problem is in the way SQL Server concatenates the stored procedure before execution. Is what I am attempting to do possible? Is there any particular format I need to set the string parameter to? I've tried:

KA1001', 'KA1002 (in the hope SQL Server just puts single quotes either side of the string)

and

'KA1001', 'KA1002'

Both fail :(

Any ideas?

Regards

Xo

View 11 Replies View Related

STORED PROCEDURE - Passing Table Name As A Parameter

Nov 29, 2005

I am trying to develop a stored procedure for an existing application thathas data stored in numerous tables, each with the same set of columns. Themain columns are Time and Value. There are literally hundreds of thesetables that are storing values at one minute intervals. I need to calculatethe value at the end of the current hour for any table. I am a little newto SQL Server, but I have some experience with other RDBMS.I get an error from SQL Server, complaining about needing to declare@TableName in the body of the procedure. Is there a better way to referencea table?SteveHere is the SQL for creating the procedure:IF EXISTS(SELECTROUTINE_NAMEFROMINFORMATION_SCHEMA.ROUTINESWHEREROUTINE_TYPE='PROCEDURE'ANDROUTINE_NAME='udp_End_of_Hour_Estimate')DROP PROCEDURE udp_End_of_Hour_EstimateGOCREATE PROCEDURE udp_End_of_Hour_Estimate@TableName VarCharASDECLARE @CurrentTime DateTimeSET @CurrentTime = GetDate()SELECT(SELECTSum(Value)* DatePart(mi,@CurrentTime)/60 AS EmissonsFROM@TableNameWHERETime BETWEENDateAdd(mi,-DatePart(mi,@CurrentTime),@CurrentTime)AND@CurrentTime)+(SELECTAvg(Value)* (60-DatePart(mi,@CurrentTime))/60 AS EmissionsFROM@TableNameWHERETime BETWEENDateAdd(mi,-10,@CurrentTime)AND@CurrentTime)

View 15 Replies View Related







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