Transact SQL :: Adding Values In Row Using Stored Procedure
Aug 18, 2015
I'm trying to write a stored procedure that performs a select statement of the RequestID column and the total of the disk size for that row. ie the values on RequestAdditionalDisk1Size + RequestAdditionalDisk2Size + RequestAdditionalDisk3Size where the Requester equals a certain value. I can perform the select statement fine on the individual values, how to add the values of the Disk sizes together and present that back in the select statement.So far the code looks like but is giving me an error around the line performing a SUM.
-- Author:<Author,,Name>
-- Create date: <Create Date,,>
-- Description:<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[spMyRequests]
-- Add the parameters for the stored procedure here
I have a SQL server instance being used as our data warehousing environment. The instance consists of several databases that I am snapshotting as part of our high availability strategy for data. I've created a stored procedure that takes the source database as an argument and that will create a new snapshot when a new one needs to be created and will also automatically remove the old snapshot. It also updates some synonym tables that point to the new snapshot but that might not be an important detail.
I would like to have the stored procedure stored some place global to all of the databases that I am routinely snapshotting, but that would mean putting it in the master database. Although having it there makes things significantly better in terms of usability, it seems like there's something wrong with putting any stored procedures in the master database. Am I wrong? Is it OK to put stored procedures there in situations like this?
If I create a stored procedure and do not specify a return value or type, why does SSMS show that the stored procedure returns an int in the object explorer? Â Is that simply the success flag?
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)
I have dw schema in the database, owned by user dw.The login name is dw. The login had db_owner right in the database. The default schema for the login on the database is dw.Now Once I assign 'sysadmin' serverrole to dw login, I started seeing stored proc not found error, if try to execute stored proc without mentioning dw.spname;Also I am seeing table not found error while quering tables under dw schema, after the change.
calculate age:CREATE PROCEDURE selectage ASSELECT Age = datediff(yy, birthday, getdate()) + case when datepart(dy, birthday) <= datepart(dy, getdate()) then 0 else -1endFROM nopaymy ? is how do fix this problem:I add inWHERE (((Age)>= @age1 And (Age)<= @age2)) and it will complain about Age is not a column, so how would i store the underlined and bold Age aboves value?
Currently I add a new stored procedure in Enterprise Manager by right-clicking Stored Procedure and clicking New Stored Procedure then copy/paste text of procedure into window displayed. Whenever we set up new customer, this process is repeated several times to get all of our stored procedures loaded. Is there a way to automate this process? Thanks
Hi all,I am creating a ASP.Net 2.0 website and in it I had to create a CLR stored procedure to do a complex sql procedure. Coming to the problem I created the CLR stored procedure as a different database project . I wanted to know whether its possible to add the CLR managed code into my exisiting ASP.Net project in which case I should get the dll for this stored procedure in order to deploy the stored proc in the SQL Server.Or is there some simplified approach to using clr stored procedures in an ASP.Net project.
Hi,I just try to add a variable (ORDER BY) to the end of a select statement and it doesn't work. I try so many things but im a newbie in SQL.Help!!!I want to add @OrderBy at the end of :-----------------SELECT *FROM AnnoncesWHERE CategoryID = @CategoryID AND DateOnCreate >= @DateFinale-----------------My declaration :Declare @OrderBy nvarchar(50)If @Sort = 'date_de' set @OrderBy = ' ORDER BY DateOnCreate DESC'Else set @OrderBy = ' ORDER BY DateOnCreate ASC'
The stored procedure below was working fine and I have added a inner join to it and it stopped working. I have highlighted the new code I have added to the stored procedure in red. Any suggestions on how to solve this issue?
I am getting the below error Server: Msg 209, Level 16, State 1, Procedure AIG_GetRECON_TRANSACTION, Line 53 Ambiguous column name 'REINS_TYPE_CD'.
below is the query i changed.
ALTER PROCEDURE [dbo].[AIG_GetRECON_TRANSACTION] @RECON_TRNSCTN_ID int=NULL, @RECON_ITEM_ID int=NULL, @DIVISION_ID int=NULL, @REINS_TYPE_CD int=NULL, @TRANSACTION_NO char(10)=NULL, @TRANSACTION_NAME varchar(100)=NULL, @REF_NUMBER varchar(20)=NULL, @POLICY_CRS_REF_NO varchar(20)=NULL, @GL_DTFrom datetime=NULL, @GL_DTTo datetime=NULL, @TRANSACTION_CD int=NULL, @AMOUNT money=NULL, @FILE_STATUS_CD int=NULL, @TRNSCTN_OWR_CD int=NULL, @ISSUE_CD int=NULL, @SUPP_ISSUE_CD int=NULL, @IRC_CLASSIFICATION_CD int=NULL, @UNDER_90_DAYS money=NULL, @OVER_90_DAYS money=NULL, @AGING_DAYS_CNT int=NULL, @VOCHER_NO varchar(50)=NULL, @LOADED_DTFrom datetime=NULL, @LOADED_DTTo datetime=NULL, @SPUsageMode TINYINT = 0 -- This should be the last parameter
AS
------------------------------------------------------------------------------- -- SP Usage Audit Info -- DO NOT REMOVE -- All Stored Procedure code MUST be placed between Section 1 and 2 of -- SP Usage Audit code ------------------------------------------------------------------------------- -- SP Usaged Section 1 - Declare ------------------------------------------------------------------------------- DECLARE @SPStartTime DATETIME SELECT @SPStartTime = GETDATE() DECLARE @SPEndTime DATETIME DECLARE @AuditCount INT -------------------------------------------------------------------------------
FROM AIGNET.dbo.RECON_TRANSACTION AS RE INNER JOIN REINSURANCE_TYPE REI ON RE.REINS_TYPE_CD = REI.REINS_TYPE_CD WHERE (@RECON_TRNSCTN_ID IS NULL OR @RECON_TRNSCTN_ID=RE.RECON_TRNSCTN_ID) AND (@RECON_ITEM_ID IS NULL OR @RECON_ITEM_ID=RE.RECON_ITEM_ID) AND (@DIVISION_ID IS NULL OR @DIVISION_ID=RE.DIVISION_ID) AND (@REINS_TYPE_CD IS NULL OR @REINS_TYPE_CD=RE.REINS_TYPE_CD) AND (@TRANSACTION_NO IS NULL OR @TRANSACTION_NO=RE.TRANSACTION_NO) AND (@TRANSACTION_NAME IS NULL OR @TRANSACTION_NAME=RE.TRANSACTION_NAME) AND (@REF_NUMBER IS NULL OR @REF_NUMBER=RE.REF_NUMBER) AND (@POLICY_CRS_REF_NO IS NULL OR @POLICY_CRS_REF_NO=RE.POLICY_CRS_REF_NO) AND ((RE.GL_DT IS NULL) OR (RE.GL_DT BETWEEN @GL_DTFrom AND @GL_DTTo)) AND (@TRANSACTION_CD IS NULL OR @TRANSACTION_CD=RE.TRANSACTION_CD) AND (@AMOUNT IS NULL OR @AMOUNT=RE.AMOUNT) AND (@FILE_STATUS_CD IS NULL OR @FILE_STATUS_CD=RE.FILE_STATUS_CD) AND (@TRNSCTN_OWR_CD IS NULL OR @TRNSCTN_OWR_CD=RE.TRNSCTN_OWR_CD) AND (@ISSUE_CD IS NULL OR @ISSUE_CD=RE.ISSUE_CD) AND (@SUPP_ISSUE_CD IS NULL OR @SUPP_ISSUE_CD=RE.SUPP_ISSUE_CD) AND (@IRC_CLASSIFICATION_CD IS NULL OR @IRC_CLASSIFICATION_CD=RE.IRC_CLASSIFICATION_CD) AND (@UNDER_90_DAYS IS NULL OR @UNDER_90_DAYS=RE.UNDER_90_DAYS) AND (@OVER_90_DAYS IS NULL OR @OVER_90_DAYS=RE.OVER_90_DAYS) AND (@AGING_DAYS_CNT IS NULL OR @AGING_DAYS_CNT=RE.AGING_DAYS_CNT) AND (@VOCHER_NO IS NULL OR @VOCHER_NO=RE.VOCHER_NO) AND ((RE.LOADED_DT IS NULL) OR (RE.LOADED_DT BETWEEN @LOADED_DTFrom AND @LOADED_DTTo))
End
------------------------------------------------------------------------------- -- SP Usage Audit Info -- DO NOT REMOVE -- SP Usage Section 2 - INSERT Audit Info ------------------------------------------------------------------------------- SELECT @AuditCount = @SPUsageMode + (SELECT ParameterFlag FROM DBPerfMon.dbo.SPUsageParameters WITH (NOLOCK) WHERE Parameter = 'SPUsageByPass')
------------------------------------------------------------------------------- -- Absolutely NO Stored Procedure code written beyond this point -------------------------------------------------------------------------------
Below is the stored procedure i have it works fine if i have 1 value passed to @invited_by but i want to modify but i want this code to be working for multiple inputs .Lets say if i do
exec [dbo].[sp_GetInvitationStatusTest] 'Test1 . I get the desired output but i want this procedure to work for exec [dbo].[sp_GetInvitationStatusTest] 'Test1,Test2'. USE [merck_acronyms] GO
'SELECT E.EmployeeID FROM dbo.EmployeeGroupMapToEmployee E, dbo.Per_Budget B WHERE E.EmployeeID = B.PER_PERSONAL_ID AND B.PEB_Budget_id = 243 AND E.EmployeeGroupID IN (SELECT H.Id FROM dbo.EmployeeGroup H WHERE H.InstitutionsId = 22) GROUP BY E.EmployeeID '
If i Replace @EmpFilterAddDuty with this in a QUERY, it gives me the expected result, but if i try to execute the stored procedure.:
DECLARE@return_value int EXEC@return_value = [dbo].[EP_Conterbalances] @Start_Date_For_Totals_Date = N'20120831', @EmpFilterAddDuty = 'SELECT E.EmployeeID FROM dbo.EmployeeGroupMapToEmployee E, dbo.Per_Budget B
[Code] .....
I get this error code:
Conversion failed when converting the varchar value 'SELECT E.EmployeeID FROM dbo.EmployeeGroupMapToEmployee E, dbo.Per_Budget B WHERE E.EmployeeID = B.PER_PERSONAL_ID AND B.PEB_Budget_id = 243 AND E.EmployeeGroupID IN (SELECT H.Id FROM dbo.EmployeeGroup H WHERE H.InstitutionsId = 22) GROUP BY E.EmployeeID ' to data type int.
I really do not understand why SQL 2012 tries to convert the value to an int, and I want to know how to pass the text string.
Hello:I have a stored procedure for generating our invoices in CrystalReports. I have added a new field to the SP, but when I try to add thefield to my Crystal Report invoice, the field isn't available in thelist. However, if I create a new, blank report using the same storedprocedure as the datasource, the field is available. I've seeminglytried every iteration of "Verify Database" to no avail.The obvious answer would be to simply drop the sp from my existingreport and then re-add it. However, if you do this, all your fields onthe report are dropped.Any ideas?Thanks,Scott
I have two tables. UserIds is a collection of users, with UserId as the primary key. Contacts simply maps pairs of users. Think of it like IM, where one user can have multiple contacts.
Contacts ------------- UserId - int, the UserId of the user who has the contact ContactUserId - int, the UserId of the contact
I also have a stored procedure named GetConnectedUserIds that returns all the contacts of a given user. It takes a single parameter, @UserId, the UserId of the user whose contacts I want to get. That's pretty simple:
SELECT ContactUserId FROM Contacts WHERE UserId=@UserId.
Now here's where I get over my head. I actually want that stored procedure to return the user's contacts AND the user's own ID. I want the SELECT statement above, but tack on @UserId to the end of it.
How do I do that?
Thanks in advance for any help. Feel free to answer here or to point me to a useful resource.
My task is to add income by taking few variables from webpage. I had take User ID(From database), Field value by selecting it from DropDownBox( Which value is once again taken from database), Income Description, Date, Amount . I had completed this task successfully by binding DropDownBox to database by query string and added income using stored procedure as below.
I need this to be done using only single Stored Procedure for binding Field Value to DropDownBox and for adding income. Plz tell me how to do this? ASPX.CS file protected void Page_Load(object sender, EventArgs e) { SqlConnection con = null;
con = DataBaseConnection.GetConnection(); DataSet ds = new DataSet(); SqlDataAdapter da = new SqlDataAdapter("select PA_IFName from PA_IncomeFields where PA_UID=@PA_UID", con); da.SelectCommand.Parameters.Add("@PA_UID", SqlDbType.Int).Value = (int)Session["PA_UID"]; da.Fill(ds);
How we can get to know what DML action(INSERT,UPDATE,DELETE) is present inside a Stored procedure, and i want to know if we have any specific columns are present in system tables or system related dynamic views which gives this info. I am aware of the SQL profiler, but in my scenario i want to use it through system tables or system related dynamic views.
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.
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
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
' 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
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?
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....
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.
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
) As if(@columnname !=' ' and @recordname !=' ') begin select userid,user_name,password,role_code,expiry_date from usermaster where '+@columnname+' like '+@recordname+"%"' end GO
