Public Shared Sub My_UpdateCountsManaged( ByRef paramInOut As Integer)
'here I perform update statement using "paramInOut" passed form calling code
.......
'then I return value to the calling code
paramInOut = 555
End Sub
End Class
Calling code specifies a parameter like this:
Code Snippet
Dim param as Sqlparameter = New SqlParameter("@paramInOut", SqlDbType.Int)
param.Direction = ParameterDirection.InputOutput
param.Value = 999
cmd.Parameters.Add(param)
When I execute the code, it surely gets back "555" from SP, the problem is that SP never gets "999" from calling code despite ParamDirection is InputOutput. It always receives 0. I am afraid I don't understand something fundamental ?
I'm developing a web app using VS2005. I have a webpage with panel containing a gridview populated by a SQLdatasource. The SQLdatasource in turn is populated by a stored procedure that can take up to 5 parameters. The user types in up to 5 separate words (searchterms) in a text box which are then parsed and passed to the stored proc in the datasource which performs a fulltext search. The gridview then becomes visible. My problem is that unless the user types in 5 searchterms (no less), the gridview returns zero rows. 5 searchterms returns the proper results. Somehow, I need to be able to pass in null or empty values for unneeded parameters. I've tested the stored procedure in Query Analyzer and from within the SQLdatasource configuration (using Test Query) using 0 up to 5 parameters and it works fine, so that's not my problem. Here's the code that runs after the user types in their search term(s) and presses a button:Public Sub FTSearch_Command(ByVal sender As Object, ByVal e As CommandEventArgs) Handles btnFullText.Command Dim x As Integer pnlFullText.Visible = Falsefiltertext = Replace(txtSearchTxt.Text, "'", "''") If Not filtertext Is Nothing Then filtertext = filtertext.Trim Else Return End IfDim arrayString() As String = filtertext.Split(" ") Dim length As Integer = arrayString.LengthFor x = 0 To (length - 1) If Not arrayString(x) Is Nothing ThenSelect Case x Case 0 : lblFTParm1.Text = arrayString(0)Case 1 : lblFTParm2.Text = arrayString(1) Case 2 : lblFTParm3.Text = arrayString(2)Case 3 : lblFTParm4.Text = arrayString(3) Case 4 : lblFTParm5.Text = arrayString(4) End Select End If Next pnlFullText.Visible = "True" End Sub Any ideas? Thanks in advance.
I have a table with a foreign key field. I need to retrieve all the records where the foreign key matches any of a set. In plain ol' SQL this is accomplished with the IN(a,b,c) statement but I can't get that to work in a stored procedure.
How would I do this? I can imagine that I could parse the input string and create a temporary table and use that to do a join but that seems rather convoluted.
Any tips highly appreciated! If I'm not being clear I'll gladly post more details.
I am working through a tutorial and have stumbled into something that does not quite make sense to me. I was wondering if someone could help me understand this.
I have created this SP, this all makes sense to me due to the assignment of the artistname column value to the @artistname variable. In other words what is on the right of the equal sign is assigned to what is on the left.
create procedure ShowPopStyle @style varchar(30), @artistname varchar(30) output as select @artistname = artistname from artists where style = @style go
Now when you execute this SP, what does not makes sense to me is if I need to declare a variable to hold the output, which I presume is null, shouldn't the @returnname be on the left side of the equal sign instead of the right?
declare @returnname varchar(30) -- variable for the output from the procedure exec showpopstyle 'Pop', @artistname = @returnname output print @returnname
This is my SProc: CREATE PROCEDURE dbo.ap_Select_ModelRequests_RequestDateTime /* Input or Output Parameters *//* Note that if you declare a parameter for OUTPUT, it can still be used to accept values. *//* as is this procedure will very well expect a value for @numberRows */@selectDate datetime ,@selectCountry int ,@numberRows int OUTPUT AS SELECT DISTINCT configname FROM ModelRequests JOIN CC_host.dbo.usr_smc As t2 ON t2.user_id = ModelRequests.username JOIN Countries ON Countries.Country_Short = t2.country WHERE RequestDateTime >= @selectDate and RequestDateTime < dateadd(dd,1, @selectDate) AND configname <> '' AND interfacename LIKE '%DOWNLOAD%' AND result = 0 AND Country_ID = @selectCountry ORDER BY configname /* @@ROWCOUNT returns the number of rows that are affected by the last statement. *//* Return a scalar value of the number of rows using an output parameter. */SELECT @numberRows = @@RowCount GO And This is my code. I know there will be 100's of records that are selected in the SProc, but when trying to use the Output Parameter on my label it still says -1Protected Sub BtnGetModels_Click(ByVal sender As Object, ByVal e As System.EventArgs) Dim dateEntered As String = TxtDate.TextDim selectCountry As String = CountryList.SelectedValue Dim con As New SqlClient.SqlConnection con.ConnectionString = "Data Source=10.10;Initial Catalog=xx;Persist Security Info=True;User ID=xx;Password=xx"Dim myCommand As New SqlClient.SqlCommand myCommand.CommandText = "ap_Select_ModelRequests_RequestDateTime" myCommand.CommandType = CommandType.StoredProceduremyCommand.Parameters.AddWithValue("@selectDate", dateEntered) myCommand.Parameters.AddWithValue("@selectCountry", CInt(selectCountry))Dim myParam As New SqlParameter("@numberRows", SqlDbType.Int) myParam.Direction = ParameterDirection.Output myCommand.Parameters.Add(myParam) myCommand.Connection = con con.Open()Dim reader As SqlDataReader = myCommand.ExecuteReader()Dim rowCount As Integer = reader.RecordsAffected numberParts.Text = rowCount.ToString con.Close() End Sub
In a Database "AP" of my SQL Server Management Studio Express (SSMSE), I have a stored procedure "spInvTotal3":
CREATE PROC [dbo].[spInvTotal3]
@InvTotal money OUTPUT,
@DateVar smalldatetime = NULL,
@VendorVar varchar(40) = '%'
This stored procedure "spInvTotal3" worked nicely and I got the Results: My Invoice Total = $2,211.01 in my SSMSE by using either of 2 sets of the following EXEC code: (1) USE AP GO --Code that passes the parameters by position DECLARE @MyInvTotal money EXEC spInvTotal3 @MyInvTotal OUTPUT, '2006-06-01', 'P%' PRINT 'My Invoice Total = $' + CONVERT(varchar,@MyInvTotal,1) GO (2) USE AP GO DECLARE @InvTotal as money EXEC spInvTotal3 @InvTotal = @InvTotal OUTPUT, @DateVar = '2006-06-01', @VendorVar = '%' SELECT @InvTotal GO //////////////////////////////////////////////////////////////////////////////////////////// Now, I want to print out the result of @InvTotal OUTPUT in the Windows Application of my ADO.NET 2.0-VB 2005 Express programming. I have created a project "spInvTotal.vb" in my VB 2005 Express with the following code:
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Public Class Form1
Public Sub printMyInvTotal()
Dim connectionString As String = "Data Source=.SQLEXPRESS; Initial Catalog=AP; Integrated Security=SSPI;"
Dim conn As SqlConnection = New SqlConnection(connectionString)
Try
conn.Open()
Dim cmd As New SqlCommand
cmd.Connection = conn
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "[dbo].[spInvTotal3]"
Dim param As New SqlParameter("@InvTotal", SqlDbType.Money)
param.Direction = ParameterDirection.Output
cmd.Parameters.Add(param)
cmd.ExecuteNonQuery()
'Print out the InvTotal in TextBox1
TextBox1.Text = param.Value
Catch ex As Exception
MessageBox.Show(ex.Message)
Throw
Finally
conn.Close()
End Try
End Sub
End Class ///////////////////////////////////////////////////////////////////// I executed the above code and I got no errors, no warnings and no output in the TextBox1 for the result of "InvTotal"!!?? I have 4 questions to ask for solving the problems in this project: #1 Question: I do not know how to do the "DataBinding" for "Name" in the "Text.Box1". How can I do it? #2 Question: Did I set the CommandType property of the command object to CommandType.StoredProcedure correctly? #3 Question: How can I define the 1 output parameter (@InvTotal) and 2 input parameters (@DateVar and @VendorVar), add them to the Parameters Collection of the command object, and set their values before I execute the command? #4 Question: If I miss anything in print out the result for this project, what do I miss?
On SQL 2012 (64bit) I have a CLR stored procedure that calls another, T-SQL stored procedure.
The CLR procedure passes a sizeable amount of data via a user defined table type resp.table values parameter. It passes about 12,000 rows with 3 columns each.
For some reason the call of the procedure is verz very slow. I mean just the call, not the procedure.
I changed the procdure to do nothing (return 1 in first line).
So with all parameters set from
command.ExecuteNonQuery()to create proc usp_Proc1 @myTable myTable read only begin return 1 end
it takes 8 seconds.I measured all other steps (creating the data table in CLR, creating the SQL Param, adding it to the command, executing the stored procedure) and all of them work fine and very fast.
When I trace the procedure call in SQL Profiler I get a line like this for each line of the data table (12,000)
SP:StmtCompleted -- Encrypted Text.
As I said, not the procedure or the creation of the data table takes so long, really only the passing of the data table to the procedure.
Hi, i need to insert a record 1 or more times, depending of a variable in code-behind:dim amount as integeramount= value (e.g. 3) My problem is: how to pass that variable to the stored procedure?I tried with this but nothing happens: comd.Parameters.Add("@amount", SqlDbType.NVarChar, 10).Value = amount_of_details Maybe is my stored procedure wrong? Thanks T. Here is it:---------- ALTER PROCEDURE dbo.insert_table (@field1 nvarchar(10),...)ASDeclare @iLoopNumber intDeclare @amount intBEGIN TRAN SET @iLoopNumber = 1 SET @amountr While (@iLoopNumber <= @amount) BEGIN INSERT INTO table(field1,...) VALUES (....)) SET @iLoopNumber = @iLoopNumber +1 End COMMIT TRAN
Hi I have an if clause in my code to add the final parameter value to send to the database. If Page.User.IsInRole("MICMS") Then cmdCheckUser.Parameters.Add("@C_ID", 0) Else cmdCheckUser.Parameters.Add("@C_ID", Session("C_ID")) End If
If the user is in the role, the error is triggered saying that @C_ID is expected by the stored procedure. If i then change the value from 0 to 10, the stored procedure works fine.Is there any reason that the stored procedure is failing when the value 0 is used and not when any other value is used?Thanking you in advance.
hi,i am passing a xml file name to the stored procedure. the SP parses the file. but it is giving the error' INVALID AT THE TOP LEVEL OF THE DOCUMENT 'I expect this because of + and - in the xml file bafore the parent tags.how can i do the parser to eliminate these.
I have a two dimensional array in Front end (As for example Array contains 20 ECode and EmployeeName). I have a Stored Proc. where i have written a statement for inserting theses value in a table. so how i will pass this array to SP. Pls. give exmp. for Front end and SP also.
Hello,I read an article on how to use Yahoos API to GeoCode addresses. Basedon the article I created a stored procedure that is used as follows:SPGeocode '2121 15st north' ,'arlington' ,'va' ,'warehouse-test'Returns:Latitude Longitude GeoCodedCity GeoCodedState GeoCodedCountryPrecision Warning----------- ---------- ------------- ------------- ------------------------------ --------38.889538 -77.08461 ARLINGTON VA USPrecision Good No ErrorIt returns Latitude and Longitude and other information. Works great.In conjunction with Haversine formula, I can compute the distancebetween two locations if I know the Lat and Long of the two points.This can start to answer questions like "How many students do we havewithin a 10 mile radius of Location X?"(Marketing should go nuts over this :)My question is how can i use my data from a table and pass it to theSPGeocode via a select statement?The table I would use is:CREATE TABLE "dbo"."D_BI_Student"("STUDENT_ADDRESS1" VARCHAR(50) NULL,"STUDENT_ADDRESS2" VARCHAR(50) NULL,"STUDENT_CITY" VARCHAR(50) NULL,"STUDENT_STATE" VARCHAR(10) NULL,"STUDENT_ZIP" VARCHAR(10) NULL);This is so new to me, I am not even sure what to search.TIARob
Hi, I want to pass an xml file to stored procedure in SQL Server 2000 as a parameter. Can we pass the xml file path as a parameter? if not then how can it be done. I want to serialize data to XML and then pass it to a procedure to insert data.
if (conn != null) conn.Close(); return xmlDoc.InnerXml; }
I'm assuming this is because my Date is in the wrong format when .NET passes it. I've tested the stored procedure directly in SQL Server Managent Studio and it works (Format of date is '5/15/2008 9:16:23 PM').
In the snippet below, ExecuteSqlString is a stored procedure that accepts one parameter. SelectChangeDropdownRowsource is a function in my code behind page that generates the string I want to pass. I can't seem to find the correct syntax to get it to work. The way it is show below, the error comes back about incorrect syntax near ')' . Is this doable? <asp:SqlDataSource ID="ChangeInfo" runat="server" ConnectionString="<%$ ConnectionStrings:xxx %>" DataSourceMode="DataReader" ProviderName="<%$ ConnectionStrings:xxx %>" SelectCommandType=StoredProcedure SelectCommand="ExecuteSqlString"> <selectparameters> <asp:parameter name="sqlString" Type=String DefaultValue=SelectChangeDropdownRowsource()/> </selectparameters> </asp:SqlDataSource>
I am trying to inject dynamically generated text into a Sql2000 stored procedure. What am I doing wrong?A code behind routine generates the following string value based on a visitor entering 'sail boats' in TextBox1. The routine splits the entry and creates the below string.Companies.L_Keywords LIKE '%sail%' AND Companies.L_Keywords LIKE '%boats%' I am trying to place this string result in the WHERE statement of a Sql2000 Stored Procedure using parameter @VisitorKeywords. PROCEDURE dbo.KWsearchAS SELECT DISTINCT Companies.L_Name, Companies.L_ID, Companies.L_EnabledWHERE ( @visitorKeywords ) AND (Companies.L_Enabled = 1)ORDER BY Companies.L_Name I am wanting the resulting WHERE portion to be: WHERE ( Companies.L_Keywords LIKE '%sail%' AND Companies.L_Keywords LIKE '%boats%' ) AND (Companies.L_Enabled = 1) Thank you
hi, i searched a lot to find how to pass an orderBy parameter finally i used a case block in my code and it works now how can i add a second parameter for ascending and descending order(@sortAscOrDesc) when i use it after the end of case statement i get error here is my sp:CREATE PROCEDURE [userPhotos] @userID int,@orderBy varchar(100) ASSELECT ID,UserID,Photo,ALbumID,Title,views,date_added from userAlbumPic where userID=@userID and albumID=0 order by case @orderBy when 'date_added' then date_added when 'views' then [views] else date_added end GO
i am trying to pass a large XML file from VS2005 (web service layer) to stored procedure (SQL Server 2000)In my stored procedure, the input parameter takes as "nText" (which will be XML file)Question:While performing ExecuteNonQuery, i am getting request timeout i think this is coz of large XML file i am passing.can anyone plz tell me how to pass XML file to SP...it would be better if you can provide me with some codei am completely new to this XML file passing between web service and SP...... thanks a lot in advance.....
I have stored procedure that expects 2 input parameters (@UserID uniqidentifier and @TeamID int). My datasource is SQLDataSource. So i need to pass parameters to SP..When i add parameters ans execute i got an error "....stored procedure expects @TeamId parameter, which was not supplied" But i pass them. How should i pass parameters? Maybe the reason is some mismatching of parametrs. 1 Parameter [] param = new Parameter[2]; 2 3 param[0] = new Parameter(); 4 param[0].Name = "@TeamID"; 5 param[0].Type = TypeCode.Int32; 6 param[0].Direction = ParameterDirection.Input; 7 param[0].DefaultValue = "1"; 8 9 param[1] = new Parameter(); 10 param[1].Name = "@UserID"; 11 param[1].Direction = ParameterDirection.Input; 12 param[1].DefaultValue = "edf26fd8-d7cd-4b32-a18a-fc888cac63ef"; 13 param[1].Type = TypeCode.String; 14 15 dataSource = new SqlDataSource(); 16 dataSource.ID = "Source"; 17 dataSource.ConnectionString = settings.ToString(); 18 19 dataSource.SelectCommandType = SqlDataSourceCommandType.StoredProcedure; 20 dataSource.DataSourceMode = SqlDataSourceMode.DataReader; 21 dataSource.SelectCommand = "dbo.GetAprfAssessmentTeamData"; 22 23 24 dataSource.SelectParameters.Add(param[0]); 25 dataSource.SelectParameters.Add(param[1]); 26
Hi, I want to create a stored procedure which I can pass multi parameters. This is what I need, I have a gridview which is used for displaying customer info of each agent. However, the number of customers for each agent is different. I will pass customer names as parameters for my stored procedure. Here is a sample, CREATE PROCEDURE [dbo].[display_customer] @agentID varchar(20), @customer1 varchar(20), @customer2 varchar(20), ..... -- Here I do know how many customers for each agent AS SELECT name, city, state, zip FROM rep_customer WHERE agent = @agentID and (name = @customer1 or name = @customer2) Since I can not decide the number of customers for each agent, my question is, can I dynamically pass number of parameters to my above stored procedure? Thanks a lot!
hi friends,i need to select some of the employees from the EmpMaster using in clause. I tried to pass a string with the comma delemeters. it didn't produce all the records except the first in that string.shall i try with string functions in TSQL or any other options? Thanks and Regads,Senthilselvan.D
Either method is in the “ASPX� file This is a DataSource for a “DetailsView� which has on top of “DeleteCommand� an “InsertCommand� a “SelectCommand� and an “UpdateCommand�. It is related to a GridView and the “@DonationRecID� comes from this GridView. Method 1. Using an SQL Query – this works fine <asp:SqlDataSource ID="donationDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:FUND %>" DeleteCommand="DELETE FROM [Donations] WHERE [DonationRecID] = @DonationRecID"> Method 2. – using a stored procedure – this bombs because I have no clue as to how to pass “@DonationRecID� to the stored procedure "Donations_Delete". <asp:SqlDataSource ID="donationDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:FUND %>" DeleteCommand="Donations_Delete" DeleteCommandType="StoredProcedure"> How do I pass “@DonationRecID� to the "Donations_Delete" stored procedure? Does anyone have an example of how I can do this in the “ASPX.CS� file instead.
sSQL = "spBPT_Fuel_Set_Status_Approved" cmdDailyPrices.CommandText = sSQL cmdDailyPrices.Parameters.Add("@user", "Philippe") cmdDailyPrices.Parameters.Add("@verbose", "0") cmdDailyPrices.Parameters.Add("@Day_1_add", rowBand1.Cells(DayParameters.AddFactor).Value) cmdDailyPrices.Parameters.Add("@Day_1_multiply", rowBand1.Cells(DayParameters.MultiplyFactor).Value) cmdDailyPrices.Parameters.Add("@Day_2_add", "NULL") cmdDailyPrices.Parameters.Add("@Day_2_multiply", "NULL") For @Day_2_add and @Day_2_multiply parameters I want to pass the value as NULL not string "NULL" could you please let me know how to do this?
hi,i have created a stored procedure to read xml dataCREATE PROCEDURE InsertXML(@xml varchar(1000)) AS DECLARE @XmlHandle intEXEC sp_xml_preparedocument @XmlHandle output,@xmlinsert into Employee(Name,ID,Sal,Address) (SELECT Name,ID,Sal,AddressFROM OPENXML (@XmlHandle, 'emp:EmployeeDetails/emp:Employee',2) WITH (Name varchar(30) 'Name', ID int 'ID', Sal int 'sal', Address varchar(30) 'Address'))EXECUTE sp_xml_removedocument @XmlHandlebut it is taking only xml text as input.but i want to send the file name as input.how to do it.
Currently i am working in a project of report generation in MS ACCESS.
The tables are in sql server 2000. I have to write stored proc in ms access.
Illustration: I am having a stored proc as follows
name: myproc ------------------- Create procedure my_proc @f1 char(1), @f2 char(5) As select * from table1 where field1=@f1 and field2=@f2 ________________________________________________ and calling proc name: call_myproc
execute my_proc 'A','2004'
If i am getting the vales of field1/@f1 and field2/@f2 from forms in ms access.
How is it possible to pass values from ms access FORMS to a calling stored procedure.
I have followed the way of creating and executing the stored procedure as given in the article as follows. http://www.databasejournal.com/features/msaccess/article.php/10895_3363511_1
As per the given link. They did not give values dynamically.
Hi, I need to automate the procedure of selecting column with numeric and passing those column values as string to another stored procedure.
Here is sample code : CREATE procedure procdeure1 @Utility varchar(50) WITH RECOMPILE as if @Utility='Electricity' begin IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'gmmers1') DROP TABLE gmmers1 IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'gmmers2') DROP TABLE gmmers2 create table gmmers1(sitecode varchar(15), Gen_Electricity_Usage bigint ) create table gmmers2 (gmmerssitecode varchar(15), Gmers_Electricity_Usage bigint ) insert into gmmers1 Select site.Sitecode,sum(isnull(AcctRptdata.ElectricityUse,0)*factor) as 'Electricity Usage' From GEN..AcctRptdata AcctRptdata, GEN..site site,SuperAccesslevels.dbo.convfactor,SuperAccesslevels.dbo.countrysettings Where ((AcctRptdata.Year*100)+AcctRptdata.Month) >= 200511 and --((AcctRptdata.Year*100)+AcctRptdata.Month) < year(DATEADD(m,-1, GetDate()))*100+month(DATEADD(m,-1, GetDate())) and ((AcctRptdata.Year*100)+AcctRptdata.Month) >= 200511 and ((AcctRptdata.Year*100)+AcctRptdata.Month) <= 200707 and site.idsite = AcctRptdata.siteid and site.sitecode not like ('C%') and len(sitecode ) = 8 and AcctRptdata.idsvc in (100) and SuperAccesslevels.dbo.convfactor.[Default] = SuperAccesslevels.dbo.countrysettings.IdUnitElec and SuperAccesslevels.dbo.countrysettings.IdCountrySetting = Site.IdCustomerSetting and [user] = 1 and site.Sitecode not like '%B%' and site.sitecode not like '9999%' and AcctRptdata.ElectricityUse >0 --Added on 16th Aug and site.sitecode in (select left (sitecode,8) from GEN..site where len(sitecode) >8)--= 11) --and site.sitecode in (select left(sitecode,8) from gmers_prodn..facility_data where len(sitecode) = 11) Group by site.Sitecode having sum(AcctRptdata.ElectricityUse*factor) > 0 Order by site.Sitecode
insert into gmmers2 select left (sitecode, 8) BU, sum(isnull(KWh_Purchased_Quantity, 0 )) as 'electric use' from gmers_prodn..electricity_data where sitecode in --(select sitecode from facility_data where left (sitecode ,8) in (select sitecode from gen..site where sitecode not like 'C%' and len(sitecode ) > 8 ) -- and len (sitecode) = 11) and rpt_dt >= '2005-11-01' and rpt_dt < '2007-08-01' --and sitecode like 'A0916323%' and sitecode not like '%B%' and left(sitecode,8) not in ('N0010024','N0010088','N0010101','N0010173', 'N0010373','N0010447') and sitecode not like '9999%' group by left (sitecode, 8) order by left (sitecode, 8) -- yyyy-mm-dd
select *,(Gen_Electricity_Usage-Gmers_Electricity_Usage)as Diff from gmmers1,gmmers2 where gmmers1.sitecode=gmmers2.gmmerssitecode and (Gen_Electricity_Usage-Gmers_Electricity_Usage) <>0 end else if @UTILITY='GAS' begin IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'gengas') DROP TABLE gengas IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'gmmersgas') DROP TABLE gmmersgas create table gengas(sitecode varchar(15), Gen_Gas_usage bigint ) create table gmmersgas (gmmerssitecode varchar(15), Gmers_Gas_Usage bigint ) insert into gengas Select site.Sitecode,sum(isnull(AcctRptdata.NaturalGasUse,0)*factor) as 'Gas Usage' From GEN..AcctRptdata AcctRptdata, GEN..site site,SuperAccesslevels.dbo.convfactor,SuperAccesslevels.dbo.countrysettings Where ((AcctRptdata.Year*100)+AcctRptdata.Month) >= 200511 and --((AcctRptdata.Year*100)+AcctRptdata.Month) < year(DATEADD(m,-1, GetDate()))*100+month(DATEADD(m,-1, GetDate())) and ((AcctRptdata.Year*100)+AcctRptdata.Month) >= 200511 and ((AcctRptdata.Year*100)+AcctRptdata.Month) <= 200707 and site.idsite = AcctRptdata.siteid and site.sitecode not like ('C%')and len(sitecode ) = 8 and AcctRptdata.idsvc in (200,300) and SuperAccesslevels.dbo.convfactor.[Default] = SuperAccesslevels.dbo.countrysettings.IdUnitGas and SuperAccesslevels.dbo.countrysettings.IdCountrySetting = Site.IdCustomerSetting and [user] = 61 and site.sitecode not like '%B%' and site.sitecode not like '9999%' and site.sitecode in (select left (sitecode,8) from GEN..site where len(sitecode) >8) and AcctRptdata.NaturalGasUse >0 ----Added on 16th Aug --and site.sitecode in (select left(sitecode,8) from gmers_prodn..facility_data where len(sitecode) = 11) Group by site.Sitecode having sum(AcctRptdata.NaturalGasUse*factor) > 0 Order by site.Sitecode
insert into gmmersgas select left( sitecode, 8 ) BU , sum(Utility_Natural_Gas_Volume) as 'gas use' from GMERS_PRODN..fuel_data where sitecode in --(select sitecode from facility_data where left (sitecode,8) in (select sitecode from gen..site where sitecode not like 'C%' and len(sitecode ) > 8 ) -- and len(sitecode )= 11 ) and rpt_dt >= '2005-11-01' and rpt_dt < '2007-08-01' and sitecode not like '%B%' and sitecode not like '9999%' group by left( sitecode, 8 ) order by left( sitecode, 8 )
select *,(Gen_Gas_usage-Gmers_Gas_Usage)as Diff from gengas,gmmersgas where gengas.sitecode=gmmersgas.gmmerssitecode and (Gen_Gas_usage-Gmers_Gas_Usage)<>0 end
My aim is to pass sitecode having difference <>0 and from date and todate as an parameter to another stored procedure which updates sitewise for the utility so that in case a particular sitecode with same date range is included in one utility it should not be repeated in another utility.
Hi all,Seems like a fundamental question to me but I dont have a definiteanswer for it, Gurus please enlighten me.I have a table 'Table1' whose structure changes dynamically based onsome configuration values from another table. This table is being usedby a program, It was initially used by this program which ran as asingle task (executing at only a specific interval) but now the programhas to be run mutiple times some coinciding with each othe - whichmeant that table structure will change as 2 programs are runningsimultaneously... and therefore I have decided to use seperate tablenames that each has a structure of its now.I use this table name 'Table1' in about 10-15 stored procedures andUDF'sto make the long story short: Since I will not know which table I willbe using in the program I want to pass the table name as an argument tothe SP and UDF's and then access this param in the'select's/updates/inserts' - but this doesn't work unless I use DynamicSQL.Is there any other way of passing table names as parameters and thenusing then in the procs?any ideas will be really helpful.adi
