I'm using a stored procedure from sqlserver 2005 to get columns for my report. But, I don't know how to capture the returned values from the procedure and show it on the report.
Please advice.
I have a stored procedure that selects the unique Name of an item from one table.
SELECT DISTINCT ChainName from Chains
For each ChainName, there exists 0 or more StoreNames in the Stores. I want to return the result of this select as the second field in each row of the result set.
SELECT DISTINCT StoreName FROM Stores WHERE Stores.ChainName = ChainName
Each row of the result set returned by the stored procedure would contain:
ChainName, Array of StoreNames (or comma separated strings or whatever)
How can I code a stored procedure to do this?
I have a stored procedure that selects * from my table, and it seems to be working fine:
USE myDB
GO
IF OBJECT_ID ( 'dbo.GetAll', 'P') IS NOT NULL
DROP PROCEDURE GetAll
GO
CREATE PROCEDURE GetAll
AS
DECLARE ref_cur Cursor
FOR
SELECT * FROM myTable
Open ref_cur
FETCH NEXT FROM ref_cur
DEALLOCATE ref_cur
The problem is, I'm trying to create a DB class, and I'm not sure what Parameter settings I'm supposed to use for my returned values. Can anyone help me finish this?public class dbGet_base
{
public dbGet_base()
{
_requestId = 0;
}
public dbGet_base(Int64 RequestId)
{
this._requestId = RequestId;
getDbValues(RequestId);
}
public void getDbValues(Int64 RequestId)
{
getDbValues(RequestId, "GetAll");
}
public void getDbValues(Int64 RequestId, string SP_Name)
{
using(SqlConnection Conn = new SqlConnection(ConfigurationManager.AppSettings["WSConnection"]))
using (SqlCommand Command = new SqlCommand(SP_Name, Conn))
{
Command.CommandType = CommandType.StoredProcedure;
Command.Parameters.Add("@Request_Id", SqlDbType.Int).Value = RequestId;
Command.Parameters.Add(??
}
}
Hi, In stored procedure. I have to select a row, and I need many columns from the row, how do I do it in one select statement?
for example
set @field1 = SELECT TOP 1 field1 FROM table will select the first column from the table.
set @field2 = SELECT TOP 1 field2 FROM table - this select the 2nd field. How do I select multiple columns and assign to multiple different values?
Hello Group
I am new to stored procedure and I need some assistants. I am using the following stored procedure and I would like the return the fldPassword and fldFullName values. Both fields are in the same table. What I am trying to do is display the uses full name (i.e. Welcome <full Name>) and with the password I want to check that the password is not the default password if it is then do a redirect to the change password page.
Thank you
Michael
CREATE PROCEDURE stpMyAuthentication
(
@fldUsername varchar( 50 ),
@fldPassword Char( 25 ),
@fldFullName varchar( 75 ) OUTPUT
)
As
DECLARE @actualPassword Char( 25 )
SELECT
@actualPassword = fldPassword
FROM [tbUsers]
Where fldUsername = @fldUsername
IF @actualPassword IS NOT NULL
IF @fldPassword = @actualPassword
RETURN 1
ELSE
RETURN -2
ELSE
RETURN -1
GO
code
Sub Login_Click(ByVal s As Object, ByVal e As EventArgs)
If IsValid Then
If MyAuthentication(Trim(txtuserID.Text), Trim(txtpaswrd.Text)) > 0 Then
FormsAuthentication.RedirectFromLoginPage(Trim(txtuserID.Text), False)
End If
End If
End Sub
Function MyAuthentication(ByVal strUsername As String, ByVal strPassword As String) As Integer
Dim strFullName As String
' Variable Declaration
Dim myConn As SqlConnection
Dim myCmd As SqlCommand
Dim myReturn As SqlParameter
Dim intResult As Integer
Dim sqlConn As String
' Set conn equal to the conn. string we setup in the web.config
sqlConn = ConfigurationSettings.AppSettings("sqlDbConn")
myConn = New SqlConnection(sqlConn)
' We are going to use the stored procedure setup earlier
myCmd = New SqlCommand("stpMyAuthentication", myConn)
myCmd.CommandType = CommandType.StoredProcedure
' Set the default return parameter
myReturn = myCmd.Parameters.Add("RETURN_VALUE", SqlDbType.Int)
myReturn.Direction = ParameterDirection.ReturnValue
' Add SQL Parameters
myCmd.Parameters.Add("@fldUsername", strUsername)
myCmd.Parameters.Add("@fldPassword", strPassword)
myCmd.Parameters.Add("@fldFullName", strFullName)
' Open SQL and Execute the query
' Then set intResult equal to the default return parameter
' Close the SQL connection
myConn.Open()
myCmd.ExecuteNonQuery()
intResult = myCmd.Parameters("RETURN_VALUE").Value
Session("strFullName") = strFullName
myConn.Close()
Response.Write(strFullName)
' If..then..else to check the userid.
' If the intResult is less than 0 then there is an error
If intResult < 0 Then
If intResult = -1 Then
lblMessage.Text = "Username Not Registered!<br><br>"
Else
lblMessage.Text = "Invalid Password!<br><br>"
End If
End If
' Return the userid
Return intResult
End Function
Hi,
How to return values from stored procedures?? I have a value whose variable would be set thru this sp and it should return this value. How to do this?
Thanks,
Is It possible to have a stored procedure that returns 2 values?
and call it from a C# webforms application.
Thanks.
Hi All,
This is my stored procedure
CREATE PROCEDURE testProc AS
BEGIN
CREATE TABLE #tblTest(ID INT NOT NULL IDENTITY, Col1 INT)
INSERT INTO #tblTest(Col1)
SELECT colA FROM tableA ORDER BY colA
END
This is my simple procedure, I wanted to know whether the IDENTITY values created in #tblTest will always be consistent, I mean without losing any number in between. i.e. ID column will have values 1,2,3,4,5.....
or is there any chance of ID column having values like 1,2, 4, 6,7,8....
Please reply...
qa
I have a stored procedure. Into this stored procedure i need to pass values to a 'IN' statement from asp.net. So when i am passing it , it should b in like a string variable with the ItemIds separated by commas. the procedure i have is :
create procedure SelectDetails
@Id string
as
Select * from DtTable where itemid in(@Id)
Here the itemid field in DtTable is of type int. Now when i execute the produre it is showing error as the Itemid is int and i am passing a string value to it.
How can i solve this problem?
Hi,
I am trying to get the combination of results in my stored procedure, I am not getting what I need. Following are the things which I need to return from my stored proc.
1. I need to select distinct categories and their record count
2. I also need to select the records from the table.
3. Need to send both category, record counts and records.
First is it possible in stored procedure?
Following is helpful information.
Data in tables looks like this.
prod id, prod_name, prod_category
1, T shirts, Mens Apparel
2 , Shirts, Mens Apparel
3 , Pants , Mens Apparel
4, Tops , Women Wear
5, Bangles, Women Wear
And in User Interface I need to show like this.
Mens Apparel (3)
1 T Shirts
2 Shirts
3 Pants
Women Wear (2)
4 Tops
5 Bangles
Please help me if there is any way to return the complete data structure using stored procedure. If I do something in java code, I can get this, but I am trying to get directly from stored procedure only.
Thanks in advance...
Chandrasekhar
Before I start a small project I am interested in the best way to do it. I work for a college doing management information and generally finding problems with our data. A regular thing I end up with is a set of student ID's which I need to lookup. Through the front end this takes a while as I have to look them up individually and I often need to compare ID's.
What I have thought about making is a system where I can select a set of ID's and search for all of them. I will probably make this through C# pasting the set of id's into a datagridview and providing the results in another one.
The problem I have is I don't know how to send a set of ID's (so I would probably be using where IN (SET OF ID's). I read briefly a while back about passing a type table but am unfamiliar with how to use it. This is sql 2000 server.
hi this is my stored procedure.i am passing mu column nam and recordname has to be fetched.if run this proceedure i am getting null records only.but i am having records in my table
CREATE PROCEDURE HRUser_spsearch
(
@columnname varchar(50),
@recordname varchar(50)
)
As
if(@columnname !=' ' and @recordname !=' ')
begin
select userid,user_name,password,role_code,expiry_date from usermaster where '+@columnname+' like '+@recordname+"%"'
end
GO
can any one help to solve this please
Hi,
I am using a stored Procedure where I am passing some parameter values.Following is my Code.
CREATE proc Usp_Rpt_GetDetails
@Fromdt varchar(12),
@ToDt varchar(12),
@ApscId numeric,
@StatusCode varchar(1),
@val numeric
as
Begin
if @StatusCode = "C" then @ vall(1,2)
End
select
'' as unuseid,
substring(ltrim(rtrim(s.Spares_Code)),1,12) as Code,
oh.WO_Number AS Claim_Id,
ltrim(rtrim(sc.section_code)) AS section_code,
ltrim(rtrim(dc.defect_code)) AS defect_code,
ltrim(rtrim(at.Action_Taken_Code)) AS Repair_Code,
cs.Call_status_code
from [32_Trans_Work_Order_Spares_Detail] ws
inner join [32_Trans_Work_Order_Header] oh on oh.WO_Number = ws.WO_Number
inner join [11_Master_Spares]s on s.Spares_ID = ws.Spares_ID
inner join [31_Master_Section_Code] sc on sc.Section_ID = ws.Section_Code_ID
inner join [31_Master_Defect_Code] dc on dc.Defect_ID = ws.Defect_Code_ID
inner join [10_Master_Equipment_Status] e on e.Equipment_Status_ID = oh.Equipment_Status_ID
inner join [00_Master_Country] c on c.Country_ID = mp.Country_ID
where e.Equipment_Status_ID in (1,2) and cs.Call_Status_ID in (1,2) and oh.WO_Record_Date between @Fromdt and @ToDt
and oh.WO_Status='C'
My Problem is How to pass values to parameters
Status Code Consists of values C, V, R which i am passing from the Front End
along with Call_Status_ID which can be 1,2.
Thanks ...
how to pass array of values in stored procedure..
View 2 Replies View RelatedHi Using Following Stored Procedure,
Which always returns Null,
What s the error,
CREATE PROCEDURE prLoginAuth
(
@pStrUserName varchar(50),
@pStrPassword varchar(50),
@pOutput Varchar(20) Output
)
AS
Declare @V_Facilities Varchar(50)
SELECT Facilities=@V_Facilities From UserLoginFacilities where LoginID=(Select LoginID From UserLogin where LoginName=@pStrUserName and Password=@pStrPassword)
If(@V_Facilities=null)
Set @pOutput = @V_Facilities
Return @pOutput;
Else
Set @pOutput = @V_Facilities
Return @pOutput;
GO
Anyone correct this query , I want return the output from this procedure
Thanx in advance
Selva.R
Hi,
I have a requirement to get two count values from a stored procedure and use those values in other stored procedure.
How can I do that. I'm able to get only 1 value if i use the return key word.
Eg:
create proc test1 as
Begin
Declare scount int
Declare scount2 int
-- statements in stored procedure
return scount
return scount2
End
create proc test2
Declare variables...
Exec Test1
// here i want the values (scount and scount2 ), processed in stored procedure Test1 .
How can i get this.. please let me know..
Thanks,
srikanth
Hi
I am new to C# . I have a stored procedure which takes 4 parameters
GetSearchComplaint( Comp_ID , strViolator, strSts, FromDate, ToDate), These parameters can be null.
And i have 5 textboxes from which i send the parameters.
I am validating the input like this :System.Nullable<int> Comp_ID;
if ((txtsrchCompID.Text).Trim() == "")
{Comp_ID = null;
}else if ((txtsrchCompID.Text).Trim() == "")
{
try
{int i = int.Parse(txtsrchCompID.Text);
Comp_ID =i;
catch
{mesage += "Complaint ID is not valid";
}
}
When i run this i get this error ---'Use of unassigned local variable 'Comp_ID'
I get the same error for FromDate(DateTime) and ToDate(DateTime) . but not for string variables strViolator and strSts.
How do i pass the null value to the stored procedure? pls help..
I have a stored procedure which returns 3 different kind of values. I am checking whether a certain value entered by user is present in one of the columns of database table. Accordingly the SP returns 1 if present, -1 if not present and third value is SQL server 2005 error.But the problem is that I am only getting -1 everytime even if the value is present.I executed the SP alone to find out if it is the one which is returning the INCORRECT value. I found that that SP is returning the correct value.Therefore I came to the conclusion that it is the Table ADapter which got corrupted.I deleted the TableAdapter and created it again, but then it didn't solve the problem.I have now run out of ideas.
The code of the SP is:ALTER PROCEDURE spcheck_ServerName
(@Server_Name nvarchar(50)
)
ASDECLARE @Result int
IF EXISTS
(
SELECT
NULL
FROMServerDetails WITH (UPDLOCK)
WHERE
[SERVER NAME] = @Server_Name
)
BEGINSELECT @Result = 1
END
ELSE
BEGIN
SELECT @Result = -1
END
IF @@ERROR <> NULL
BEGIN
SELECT @Result = @@ERROR
END
RETURN @Result
And I am calling the tableAdapter method in the code behind file of the web form in the following manner:private int chkServerName(string sname1)
{
try
{Serverlist1TableAdapters.SERVERDETAILSTableAdapter nwAdapter = new Serverlist1TableAdapters.SERVERDETAILSTableAdapter();
int snval = (int)nwAdapter.spcheck_SName(sname1);return snval;
}catch (Exception ex)
{return ex.GetHashCode();
}
}
Any help will be greatly appreciated.
Hi all,
I’m returning two values from a stored procedure, one is a basic string confirming that an email has been sent and the other is the normal value returned from running an INSERT statement. So in my code I’m using the ExecuteNonQuery() method. I’m not sure how to handle both returned values in my code in my data layer. This is what I have:
ExecuteNonQuery(cmd);
return Convert.ToString(cmd.Parameters["@ReturnedValue"].Value).ToLower();
Obviously I’d need to return the value returned by the ExecuteNonQuery method as well, normally I’d simply convert the value to an int and precede this with the return keyword like so:
return (int)ExecuteNonQuery(cmd);
Obviously I can’t do this as I need to return two values, the normal value returned by the ExecuteNonQuery() method and my own output parameter value. Any ideas how I can do both? My current method containing the code further above returns a string but clearly this doesn’t help. I’m guessing that maybe I should return an object array so I can return both values? I haven’t encountered this problem before so I’m just guessing. Please help.
Thanks
Hi All, I'm a newbie learning windows applications in visual basic express edition, am using sqlexpress 2005 So i have a log in form with username and password text fields.the form passes these values to stored procedure 'CheckUser' Checkuser then returns a value for groupid. If its 1 they are normal user, if its 2 its admin user. Then opens another form called Organisations, and closes the log in form. However when i run the project, and enter a username and password and press ok ti tells me that there is incorrect syntax beside a line. I have no idea, and I'm sure that there is probably other things wrong in there. here is the code for the login button click event: Public Class Login Dim connString As String = "server = .SQL2005;" & "integrated security = true;" & "database = EVOC"
'Dim connString As String = _ '"server = .sqlexpress;" _ '& "database = EVOC;" _ '& "integrated security = true;"
Private Sub OK_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Login_Log_In.Click ' Create connection
Dim conn As SqlConnection = New SqlConnection(connString) ' Create command
Dim cmd As SqlCommand = New SqlCommand() cmd.Connection = conn cmd.CommandText = "CheckUser"
Dim inparam1 As SqlParameter = cmd.Parameters.Add("@Username", SqlDbType.NVarChar) inparam1.Value = Login_Username.ToString Dim inparam2 As SqlParameter = cmd.Parameters.Add("@Password", SqlDbType.NVarChar) inparam2.Value = Login_Password.ToString inparam1.Direction = ParameterDirection.Input inparam2.Direction = ParameterDirection.Input 'Return value
Dim return_value As SqlParameter = cmd.Parameters.Add("@return_value", SqlDbType.Int) return_value.Direction = ParameterDirection.ReturnValue cmd.Connection.Open() Dim rdr As SqlDataReader = cmd.ExecuteReader Dim groupID As Integer
groupID = return_value.Value
If groupID < 0 Then
MessageBox.Show("Access is Denied") Else Dim Username = Me.Login_Username Dim org As New Organisations org.Show() End If
conn.Close()
End Sub The stored procedure code is ok as i know it works as it should, but if it helps the code is: ALTER PROCEDURE [dbo].[CheckUser] -- Add the parameters for the stored procedure here
@UserName nvarchar(50) = N'', @Password nvarchar(50) = N''
ASBEGIN
-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements.
SET NOCOUNT ON; -- Insert statements for procedure here
IF (SELECT COUNT(*) FROM dbo.EVOC_Users WHERE Username=@Username AND Password=@Password)=1BEGINPRINT 'User ' + @Username + ' exists'
SELECT TOP 1 UserGroup FROM dbo.EVOC_Users WHERE Username=@Username AND Password=@PasswordRETURN (SELECT TOP 1 UserGroup FROM dbo.EVOC_Users WHERE Username=@Username AND Password=@Password)ENDELSE BEGINPRINT 'User ' + @Username + ' does not exist'
RETURN (-1)ENDEND
All help greatly appreciated to get me past this first hurdle in my first application!! CheersTom
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-------------------------------------------PoId Podate Approved
2 2008-07-07 No 4 2007-05-05 No 5 2005-08-06 Yes 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.
Pls provide me with full stored procedure code.
It is very urgent.
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 RelatedHello. im tryinng to build an application that has a girdview with values from 2 different tables. The select query i have used in a stored procedure works great but when i try to write something to update into 2 different tables I cant figure out how to do it.
This is the first time im writing stored procedures but from what i can tell i need to do 2 seperate updates to insert mu values.
im using the AdventureWorksLT database provided by microsoft to experiment with and my gridview consists of 2 tables populated by the following stored procedure:ALTER PROCEDURE dbo.GetSomething AS
SELECT Customer.CustomerID, Customer.LastName, Customer.FirstName, CustomerAddress.AddressID, CustomerAddress.AddressType FROM SalesLT.Customer, SalesLT.CustomerAddress
WHERE Customer.CustomerID = CustomerAddress.CustomerID
RETURN
What id like to do is to make the GridView editable and then send the all thee values back so the changes are saved in both tables. Could anyone help me write a stored procedure that gets the values from the fields in the gridview that is beeing changed and send them back to the tables?
I've got a field that might have spurious values in it (say, an admin adds a new row but doesn't have an entry for this field).
I'm trying to swap in the string no_image_EN.jpg if the value in the db does NOT end in .jpg. That way, any value rreturned is either a valid filename or no_image
I'm having trouble with the CASE statement, particularly testing just the last few cahracters of the string:
select product_code,
CASE can_image_en
?? When (can_image_en LIKE '%.jpg') then can_image_en
Else 'no_image_EN.jpg'
End as can_image_en,
none of these do the trick either (some are bad syntax obviously):
? When (can_image_en LIKE '%.jpg') then can_image_en
? When LIKE '.jpg' then can_image_en
? When '%.jpg' then can_image_en
? When right(can_image_en,4) = '%.jpg' then can_image_en This is the one that has correct syntax, though it seems to return false in ALL cases CASE can_image_en
When '%.jpg%' then can_image_en
Else 'no_image_EN.jpg'
Hi,I created the SQL 2005 stored procedure below:CREATE PROCEDURE [dbo].[STP_val_deliverable_path]@s_no smallint,@deliverable_path nvarchar(255) OUTPUTWhen I run in ASP.NET 2005 the stored procedure from server explorer Iget the value 'X:my directory.......'.When I run the procedure from code: Dim var_deliverable_path As String Dim cmm_select As New SqlCommand("STP_val_deliverable_path",connection) cmm_select.CommandType = Data.CommandType.StoredProcedure var_param = New SqlParameter var_param.ParameterName = "deliverable_path" var_param.Direction = Data.ParameterDirection.Output var_param.Value = "C:" cmm_select.Parameters.Add(var_param) cmm_select.Connection.Open() cmm_select.ExecuteNonQuery() cmm_select.Connection.Close() var_deliverable_path =CType(cmm_select.Parameters("deliverable_path").Value, String)The var_deliverable_path has the value of 'X' only, not the wholestring.What could be the problem ?
View 2 Replies View RelatedI am trying to use a stored procedure inside the scripter in a site server pipeline. Can anyone tell me how the scripter will read the the result which is a variable. The stored procedure is returning the right value when run in query analyzer but I don't know how to retrieve it inside the pipeline.
Thank you
JG
Here's the deal:
I import a flat file from a legacy system, and then convert it into a single table. That works simply enough.
Then I have a SP that querys that table using a parameter for an accountID. My business tier calls that SP and returns the results to the calling tier (my web application). Easy enough...
Now for the question. The people who created the flat file (written in COBOL) decided to use "codes" to represent data. So, for instance, if I'm looking for the account plan, I can expect to see characters like ], or [, or +, etc... These characters have a special meaning, like:
] = Plan A
[ = Plan B
+ = Plan C, and so on.
Currently, the web application displays those characters, but I want it to display the actual plan name. Is there a way that when I execute the SP, the SP could pull the necessary records, and whenever it encounters a certain "plan" character, it could convert it into a "readable" name? Say that it sees that the plan_type field has a value of "]" for twenty records, so it converts those twenty records' plan_type value from "]" into "Plan A"? I'm not sure if I can do that, but I want to at least evaluate the option if I can.
I've evaluated other options, like using a CASE statement in my code, but I shot that down quickly...for obvious reasons. I don't wanna be changing my web application or business tier each time these guys update a plan name, or add a new one, delete an existing one, etc...
I've also thought about creating a dictionary table than contains the plan's code and its name, and then just INNER JOIN the first table with the dict table. This would keep my SP very simple (it's very straight-forward right now, and I like that). That way, if a plan name is ever changed, or a new one is added, I simply update the dict table using a simple query. However, if my SP is doing the conversion, I could just as easily update the SP.
Either of these methods would work for me, and I *do* know how to do the latter (dict table). However, there are quite a few other fields that I may have to do this for. I believe when I left for the day on Friday, my last count was 14 fields total that needed translation. That would mean 14 different dict tables! That could certainly affect my SP performance with all those INNER JOINS!
Therefore, I'm certainly interested in figuring out if it's possible to do the former method (SP), and then I shall decide which method is best for my situation.
Feel free to include your thoughts on which process you think is better as well. I'm really riding the fence with this one. However, if I can't find out how to change field values in my SP, then obviously I'll make a decision very quickly...
Thanks in advance.
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
I am having trouble using Output parameters. I have set up an Execute SQL Task to call a Stored Procedure. I am passing an input parameter and indicating 1 global variable for output to retrieve a unique value from the stored procedure call. When I execute the step, it completes successfully but nothing is returned in my output parameter for the unique value??? Below is the call that I use:
EXEC SYS_GENERATE_ID ?
-in the Execute SQL Task I click the Parameters button to set up the input:
Status = Parameter1
and the Output Variable Type:
Rowset = GUID.
the stored procedure:
CREATE PROCEDURE dbo.SYS_GENERATE_ID
(@Statusvarchar(20),
@GUID uniqueidentifier = NULL OUTPUT)
AS
BEGIN
SET @GUID = NEWID()
INSERT INTO dbo.ACTIVITY (GUID, STATUS)
VALUES (@GUID, @Status)
SET @GUID = convert(varchar(50), @GUID)
RETURN @@ERROR
END
A table is correctly populated but the GUID value does not make it back to the calling task??? The Global Variable Type under Package Properties has been changed to "Dispatch" and the value is "Not Displayable"???
Any help that is offered will be appreciated GREATLY!!
I have several tables that are related with the same primary key that I need to make a consolidated list of values for. The schema of each table is similar but some have more or less fields than others. I want to make a list of all table names, field names, and the value of that field. I can select all tables included from sysobjects. The existence of the key is probable but not guaranteed in all tables. The basic table format is: Table1------ID Field1 Field2 Field31 0.0 4.1 3.92 0.5 1.3 0.23 7.1 8.8 9.3 Table2------ID Field10 0.41 3.32 2.73 5.7 Table3------ID Field1 Field22 2.4 4.63 4.3 8.1 Format of the result set:(specifying ID = 2) Table_Name Table_Field ValueTable1 Field1 0.5Table1 Field2 1.3Table1 Field3 0.2Table2 Field1 2.7Table3 Field1 2.4Table3 Field2 4.6
View 8 Replies View RelatedI have two tables and I need to update values in them via a stored procedure. Tried too much to update but some times it update the first table only, others the second or even fail due to cannot allow duplicates. Also when it updates the WHOLE data in the table becomes the same as the new updated ones. I've now reached to this error after all these lines of codes
Cannot insert the value NULL into column 'Emp_ID',table 'DatePics'; column does not allow nulls. UPDATE fails.The statement has been terminated
Here is the SQL code :
ALTER procedure [dbo].[UpdateEmp]
@EmpName nvarchar(100),
@Nationality nvarchar(30),
@Passport nvarchar(20),
@ContractDate date,
@HealthDate date
[Code] ......
my stored procedure performs actions of deletion and insertion. Both the inserted and deleted items are output in temp tables with single column.
Is there a way to return the content of these two tables?
Is there a way to return a table from the stored procedure?
Thanks in advance
waamax
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.