How To Pass Table_name And Col_name As Parameters To Stored Procedure

Apr 30, 2004

HI,

I HAVE WRITTEN A SIMPLE PROCEDURE CALLED AS MY_PROC :-

CREATE PROCEDURE MY_PROC
AS
DECLARE FETCHER CURSOR SCROLL
FOR
SELECT A.INTCUSTOMERID,A.CHREMAIL,B.INTPREFERENCEID
FROM CUSTOMER A
INNER JOIN CUSTOMERPREFERENCE B
ON A.INTCUSTOMERID = B.INTCUSTOMERID
OPEN FETCHER

WHILE @@FETCH_STATUS = 0
FETCH NEXT FROM FETCHER
CLOSE FETCHER


WHICH IS WORKING FINE WHEN I EXECUTE :-

EXEC MY_PROC

BUT I WANT TO PASS BOTH TABLE_NAMES AND COL_NAMES AS PARAMETERS :-

LIKE :-

EXEC MY_PROC [TABLE_NAME],[COL_NAME{1.....n}]

SO THAT I CAN CHANGE BOTH THE TABLE_NAME AND COL_NAME
HOW TO DO THIS ?

tHIS IS ONE MORE PROBLEM THAT I AM FACING.PLS HELP


tHANKS.

View 14 Replies


ADVERTISEMENT

Pass Parameters Into Stored Procedure?

Aug 11, 2007

How do I pass values from my ASP.NET page code into my Stored Procedure, to become parameters to be used in my Stored Proc?
Much thanks

View 2 Replies View Related

How To Pass Parameters To Stored Procedure That Use SQLDataSpurce

Feb 19, 2008

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
 

View 4 Replies View Related

Pass Multiple Parameters To Stored Procedure

Mar 26, 2008

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!
 

View 6 Replies View Related

Creating Stored Procedure With Temp Table - Pass 6 Parameters

Sep 9, 2014

I need to create a Stored Procedure in SQL server which passes 6 input parameters Eg:

ALTER procedure [dbo].[sp_extract_Missing_Price]
@DisplayStart datetime,
@yearStart datetime,
@quarterStart datetime,
@monthStart datetime,
@index int
as

Once I declare the attributes I need to create a Temp table and update the data in it. Creating temp table

Once I have created the Temp table following query I need to run

SELECT date FROM #tempTable
WHERE #temp.date NOT IN (SELECT date FROM mytable WHERE mytable.date IN (list-of-input-attributes) and index = @index)

The above query might return null result or a date .

In case null return output as "DataNotMissing"
In case not null return date and string as "Datamissing"

View 3 Replies View Related

How To Pass Values To A Stored Procedure Parameters By Using Script Task

Nov 27, 2007



Hi All,

I have One package that it contains one Execute SQL task in that i have placed a Stored procedure .
Now i want to pass values to Stored procedure parameters from a databse table by dynamically .For this i am trying to use " Script task "
How can i pass that table column values to that stores procedure thru using Script Task?

Regards,
Maruthi..

View 3 Replies View Related

Insert Into Table_Name Using Table_Name As A Variable

Jul 11, 2007

Hi guys,
I want to insert record to the table, the table name is a variable. I was tried like this,
declare @Table_Name as varchar(255)
declare @MM as varchar(2)
declare @YY as varchar(4)
set @MM = month(getdate())
set @YY = year(getdate())
set @Table_Name = 'tb_XXXX_' + @MM + @YY
INSERT INTO @Table_Name (column1,....) VALUES (@Column1,...)
If I print the @Table_Name variable it shows the corret Table name. But while executing the Procedure it shows a error message
'Must declare the sclar variable @Table_Name'
How do I implement this? Please anyone had a experience like this tell me a way. Thanks in advance.
Arun.

View 3 Replies View Related

SQL Server 2012 :: CLR Procedure Takes Ages To Pass TVP To Stored Procedure?

Jan 21, 2014

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.

View 5 Replies View Related

ADO.NET 2-VB 2005 Express Form1:Printing Output Of Returned Data/Parameters From The Parameters Collection Of A Stored Procedure

Mar 12, 2008

Hi all,
From the "How to Call a Parameterized Stored Procedure by Using ADO.NET and Visual Basic.NET" in http://support.microsft.com/kb/308049, I copied the following code to a project "pubsTestProc1.vb" of my VB 2005 Express Windows Application:


Imports System.Data

Imports System.Data.SqlClient

Imports System.Data.SqlDbType

Public Class Form1

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

Dim PubsConn As SqlConnection = New SqlConnection("Data Source=.SQLEXPRESS;integrated security=sspi;" & "initial Catalog=pubs;")

Dim testCMD As SqlCommand = New SqlCommand("TestProcedure", PubsConn)

testCMD.CommandType = CommandType.StoredProcedure

Dim RetValue As SqlParameter = testCMD.Parameters.Add("RetValue", SqlDbType.Int)

RetValue.Direction = ParameterDirection.ReturnValue

Dim auIDIN As SqlParameter = testCMD.Parameters.Add("@au_idIN", SqlDbType.VarChar, 11)

auIDIN.Direction = ParameterDirection.Input

Dim NumTitles As SqlParameter = testCMD.Parameters.Add("@numtitlesout", SqlDbType.Int)

NumTitles.Direction = ParameterDirection.Output

auIDIN.Value = "213-46-8915"

PubsConn.Open()

Dim myReader As SqlDataReader = testCMD.ExecuteReader()

Console.WriteLine("Book Titles for this Author:")

Do While myReader.Read

Console.WriteLine("{0}", myReader.GetString(2))

Loop

myReader.Close()

Console.WriteLine("Return Value: " & (RetValue.Value))

Console.WriteLine("Number of Records: " & (NumTitles.Value))

End Sub

End Class

//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
The original article uses the code statements in pink for the Console Applcation of VB.NET. I do not know how to print out the output of ("Book Titles for this Author:"), ("{0}", myReader.GetString(2)), ("Return Value: " & (RetValue.Value)) and ("Number of Records: " & (NumTitles.Value)) in the Windows Application Form1 of my VB 2005 Express. Please help and advise.

Thanks in advance,
Scott Chang

View 29 Replies View Related

How To Pass A Variable To The Stored Procedure?

Feb 22, 2007

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
 

View 3 Replies View Related

Can't Pass 0 In Stored Procedure Parameter

Sep 19, 2005

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.

View 1 Replies View Related

How To Pass Xml File To A Stored Procedure

Nov 30, 2005

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.

View 1 Replies View Related

How Do You Pass A Parameter To A Stored Procedure

Mar 9, 2006

How can I pass a parameter to a stored procedure using Visual Web Developer 2005?  I have created a SQLDataSource that calls the SP. 
Thanks
--R

View 1 Replies View Related

How Array Will Pass To Stored Procedure

May 7, 2001

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.

View 3 Replies View Related

How Do I Use Pass Data To/from Stored Procedure

May 2, 2006

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

View 4 Replies View Related

Is It Possible To Pass Array To Stored Procedure

Jan 9, 2007

Dear All,

I am using sql2000, I want to know whether in stored procedure we can pass

array. Or is there any other solution to pass array of records

Please Guide Me



thanks

View 3 Replies View Related

VB.NET Stored Procedure, Can't Pass Param

Jun 18, 2007

Hello,



I have a VB.NET stored procedure as below:




Code Snippet

Partial Public Class StoredProcedures

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 ?

Any help would be appreciated.

Thanks a lot,Fly.

View 4 Replies View Related

Pass XML To Stored Procedure In SQL Server2000

Feb 1, 2008



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.

Kindly help ASAP.
Thanks

View 4 Replies View Related

How To Pass DateTime To A Stored Procedure

May 16, 2008

When I run the following code I get error "Incorrect syntax near 'MyStoredProcedureName".





Code Snippet
public static string GetWithDate(string date)
{
string connString = System.Configuration.ConfigurationManager.ConnectionStrings["Development"].ToString();
SqlConnection conn = new SqlConnection(connString);
conn.Open();
XmlDocument xmlDoc = new XmlDocument();

SqlCommand cmd = new SqlCommand("usp_SVDO_CNTRL_GetPalletChildWorkExceptions", conn); //sw.WriteLine(count++);
cmd.Parameters.Add(new SqlParameter("@date", date));
try
{
cmd.ExecuteReader();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
SqlDataReader rdr = cmd.ExecuteReader(); //<---Bombs

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').

View 3 Replies View Related

How To Pass Parameter B/n Stored Procedure?

Apr 29, 2008

How can I pass a parameter to a stored Procedure from another stored procedure in SQL 2005?
Thnak you,

View 3 Replies View Related

How To Pass Parameters In Url For A Report Published In Reportserver (report Built On Stocked Procedure) ?

Apr 30, 2007

Hi,



I have created a report with the report server project template.

the report is created from stoked procedure having defaut input parameters.

With visual studio, i publish my report on reportserver. whenever i access to my report on this url :

http://localhost/ReportServer/Pages/ReportViewer.aspx?%2fReport+Project4%2fReport4&rs:Command=Render. the created report is with the default parameters.



I would like to know if i can transmet parameters for the stocked procedure to build the report with the request i want.


I tried to put parameter directly in the url in this way

http://localhost/ReportServer/Pages/ReportViewer.aspx?%2fReport+Project4%2fReport4&rs:Command=Render&@region='toto'

but without success.

my error message is that one

An attempt was made to set a report parameter '@region' that is not defined in this report. (rsUnknownReportParameter)





My stocked procedure is :

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER procedure [dbo].[akli] @region varchar ='m'
as
select * from dbo.Report2 where region=@region

The request used to buid the report ?
DECLARE @region varchar
EXECUTE dbo.akli @region


What is wrong in that ?

Thanks for your help.

Arioule

View 1 Replies View Related

Using A Function To Pass A Parameter To A Stored Procedure

Apr 3, 2007

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>

View 6 Replies View Related

Can't Pass Search Text Into Stored Procedure

Jun 7, 2007

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
 

View 10 Replies View Related

Pass Sort Parameter In Stored Procedure

Oct 29, 2007

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

View 7 Replies View Related

How To Pass XML File To Stored Procedure (Urgent)

Dec 18, 2007

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..... 

View 7 Replies View Related

How To Pass Values For The In Clause To The Stored Procedure?

Apr 7, 2008

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 

View 4 Replies View Related

Pass A Parameter To A Stored Procedure In Asp:SqlDataSource

Jun 5, 2008

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.

View 3 Replies View Related

HOw To Pass The Null Value To The Parameter Of The Stored Procedure

May 11, 2005

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?
 
Thanks

View 1 Replies View Related

How To Pass Xml File Name As Parameter To Stored Procedure

Nov 30, 2005

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.

View 2 Replies View Related

How To Pass Values To A Calling Stored Procedure

Nov 17, 2006

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.

I have to get the values from forms in ms access.

I have to write the calling proc as follows

my_proc [forms]![form_a].[Combo4],[forms]![form_a].[text12]

But ms access throws syntax error.

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.

could you please help me to fix this problem ?

regards,
Krishna

View 1 Replies View Related

How To Pass Coulmn Values To A Stored Procedure

Aug 30, 2007

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.

View 1 Replies View Related

How To Pass Table Names To A Stored Procedure

Dec 16, 2005

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

View 5 Replies View Related

How To Call A Stored Procedure In T-SQL And Pass It To A Cursor

Dec 2, 2007

Hi,

I have a kind of problem. In SQL Server I have a stored procedure ressembling this:




Code Block
ALTER PROCEDURE procedure1
(

@param int
)

SELECT * FROM table WHERE param = @param




Now I want to call this procedure and pass it to a cursor. We all know you can do this:



Code Block

DELCARE cursor1 CURSOR for
SELECT * FROM table WHERE param = @param



.. , but I want something like this:




Code Block
DECLARE cursor1 CURSOR for
EXEC procedure1 @param




Is it possible? I could solve it in another, but then I have to connect 2x to the database, which is less performant.

I have also tried something like this:




Code Block
ALTER PROCEDURE procedure1
(

@param int
)
SELECT @test = id FROM table WHERE param = @param
RETURN @test

ALTER PROCEDURE procedure2
(

@param int
)
DECLARE @var varchar(100)
EXEC @var = procedure1 @param




But then it returns always 0.

So is there a way to pass a procedure's select to a cursor?

Thanks in advance

Stevevil0

View 1 Replies View Related







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