T-SQL (SS2K8) :: Input Values In Table With A Stored Procedure
Jun 8, 2015
I have the following Query.
SELECT CAST(DEL_INTERCOMPANYRETURNACTIONID AS NVARCHAR(4000)) COLLATE DATABASE_DEFAULT AS DEL_INTERCOMPANYRETURNACTIONID, 'SRC_AX.PURCHLINE.DEL_INTERCOMPANYRETURNACTIONID' FROM SRC_AX.PURCHLINE WHERE DEL_INTERCOMPANYRETURNACTIONID IS NULL UNION
SELECT CAST(DEL_INTERCOMPANYRETURNACTIONID AS NVARCHAR(4000)) COLLATE DATABASE_DEFAULT AS DEL_INTERCOMPANYRETURNACTIONID, 'SRC_AX.SALESLINE.DEL_INTERCOMPANYRETURNACTIONID'
[Code] .....
My tabel is HST_MASTER.Control.
I want to have this query in a stored procedure. What syntax stored procedure i need to make to fill my table.
I would like to know if the following sql can be used to obtain specific columns from calling a stored procedure with parameters:
/* Create TempTable */ CREATE TABLE #tempTable (MyDate SMALLDATETIME, IntValue INT) GO /* Run SP and Insert Value in TempTable */ INSERT INTO #tempTable (MyDate, IntValue) EXEC TestSP @parm1, @parm2
If the above does not work or there is a better way to accomplish this goal, how to change the sql?
table2 is intially populated (basically this will serve as historical table for view); temptable and table2 will are similar except that table2 has two extra columns which are insertdt and updatedt
process: 1. get data from an existing view and insert in temptable 2. truncate/delete contents of table1 3. insert data in table1 by comparing temptable vs table2 (values that exists in temptable but not in table2 will be inserted) 4. insert data in table2 which are not yet present (comparing ID in t2 and temptable) 5. UPDATE table2 whose field/column VALUE is not equal with temptable. (meaning UNMATCHED VALUE)
* for #5 if a value from table2 (historical table) has changed compared to temptable (new result of view) this must be updated as well as the updateddt field value.
create procedure t1 (@a int,@b int,@c int,@d int,@e int,@f int) as begin select no,name,department from emp where a = @a and b =@b orc =@c or d = @d or e = @e or f = @f end
my problem is while executing i may get null values as input to the stored procedure . how to validate them ? any ideas . are there any arrays. if c and d are null then my condition would be where a = @a and b =@b or e = @e or f = @f how many loops shall i have to write.
Hi, I need to be able to create a Stored Procedure that gets its information based on dates stored in another table.Does anyone have an idea on how I can acheive this??Regards..Peter.
Could someone help me get this stored procedure to work? I want to give the stored procedure a long list of departments and have them added to a temp table. This only gets the first dept. in the temp table. I'm confused. Open to other suggestions, but want to use a 1col temp table to hold the depts.
After this is done, an SQL query is run using the temp table. Input for test: --csi_crystal_xxxx "pc9xp,pc8,pc7,pc6,pc6543,pc945678" --select * from ##CrystalGetCosts
create procedure csi_crystal_xxxx
@DeptResp varchar(4000) AS SET NOCOUNT ON DECLARE @SQL varchar(8000) DECLARE @Dept varchar(10) DECLARE @iLen int DECLARE @iPtr int DECLARE @iEnd int
If Exists (Select name, Type From [tempdb]..[sysobjects] where name = '##CrystalGetCosts' And Type = 'U') Drop table ##CrystalGetCosts
CREATE TABLE ##CrystalGetCosts (Dept_Resp_No varchar(10)) Set @iLen=Len(@DeptResp) Set @iPtr = 1 While @iPtr < @iLen BEGIN SET @iEND = charindex(',',@DeptResp,@iPtr) Set @Dept= Substring (@DeptResp,@iPtr,@iEnd-1) INSERT INTO ##CrystalGetCosts Values (@Dept) Set @iPtr = @iEnd + @iLen END
My output is depends on the dist selection so made procedure
alter procedure LAT_TEST_DYNAM0 @dist_id int as begin create table #temp (pincode varchar(10) ,latitude numeric(18,10),longitude numeric(18,10) ,descp varchar(5000)) if @dist_id!=0
[Code] ....
Myself doubt is when @dist_id is null or "0" how to show default value ?
I'm working on building a report and asked a developer which table some data comes from in an application. His answer was the name of a 3500 line stored procedure that returns 2 result sets. I could accomplish what I'm trying to do using the second result set, but I'm not sure how to put that into a temporary table so that I could use it.
Here's my plan according to the Kübler-Ross software development lifecycle:
Denial - Ask the developer to make sure this is correct (done) Despair - Look hopelessly for a solution (where I am now) Anger - Chastise developer Bargaining - See if I can get him to at least swap the order that the resultsets are returned Acceptance - Tell the users that this can't be done at present.
I have 2 identical tables one contains current settings, the other contains all historical settings.I could create a union view to display the current values from table A and all historical values from table B, butthat would also require a Variable to hold the tblid for both select statements.
Q. Can this be done with one joined or conditional select statement?
DECLARE @tblid int = 501 SELECT 1,2,3,4,'CurrentSetting' FROM TableA ta WHERE tblid = @tblid UNION SELECT 1,2,3,4,'PreviosSetting' FROM Tableb tb WHERE tblid = @tblid
i m creating one google map application using asp.net with c# i had done also now that marker ll be shown from database (lat,long)depends on the lat,long i wanna display customer,sales,total sales for each makers in html table format.
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.
This should be relatively easy but for some reason it isn't. I'm trying to simply add parameters to a stored procedure that performs a simple input and I can't do it... I keep getting an error that the parameters are not found when I am explicitly stating them. I could do this with VB ASP.NET 1.x but with all these radical changes with 2.0, I'm pulling my hair out.... I can get to work if I declare a sqlStatement in the code but don't want to go that route (but will if there is no other choice) Any help would be great: Code: Dim cmd As New SqlDataSource cmd.InsertCommandType = SqlDataSourceCommandType.StoredProcedure cmd.InsertParameters.Add("@firstName", txtFirstName.Text) cmd.InsertParameters.Add("@lastName", txtLastName.Text) cmd.InsertParameters.Add("@address1", txtAddress1.Text) cmd.InsertParameters.Add("@address2", txtaddress2.Text) cmd.InsertParameters.Add("@city", txtCity.Text) cmd.InsertParameters.Add("@state", ddlState.SelectedItem.Value) cmd.InsertParameters.Add("@zipCode", txtZipCode.Text) cmd.InsertParameters.Add("@telephone", txtTelephone.Text) cmd.InsertParameters.Add("@email", txtEmail.Text) cmd.InsertParameters.Add("@agegroup", ddlAgeGroup.SelectedItem.Value) cmd.InsertParameters.Add("@birthday", txtBirthday.Text) cmd.InsertParameters.Add("@emailnotification", rbEmail.SelectedItem.Value) cmd.InsertParameters.Add("@magazine", rbEmail.SelectedItem.Value) cmd.InsertParameters.Add("@question", txtquestion.Text) cmd.ConnectionString = "Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|ASPNETDB.MDF;Integrated Security=True;User Instance=True" cmd.InsertCommand = "sp_insertCustomer"
ERROR: Procedure or Function 'sp_insertCustomer' expects parameter '@firstName', which was not supplied. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Procedure or Function 'sp_insertCustomer' expects parameter '@firstName', which was not supplied. Source Error: Line 24: cmd.InsertCommand = "sp_insertCustomer"Line 25: Line 26: cmd.Insert()Line 27: Line 28:
I am trying to Execute a Stored Procedure using Call OSQL from a .bat file, and passing a DOS variable to the Stored Procedure.
The DOS variable is established in the DOS command...
set SERVERJOB=JOBNAME
I have tried...
EXEC sp_procedure %SERVERJOB%
With this, I get an error...
sg 170, Level 15, State 1, Server ABCDEFGH, Line 20 Line 20: Incorrect syntax near '%'.
If I put the variable name in quotes on the EXEC statement above, the value used is the variable name, %SERVERJOB% itself rather than the value the variable was set to, which would be JOBNAME in the above example.
I want to convert a SQL query as shown below into a stored procedure:
select name from namelist where town in ('A','B','D')
If I want to make the town as the input variable into the stored procedure, how should I declare the stored procedure? As far as I know, stored procedure could only handle individual values, and not a range of values.
I am trying to send XML as an input parameter for a stored procedure. I have seen many articles that do a good job of describing different variations but all the examples show the stored procedure only pulling one value (field) per record from the XML input. I need to pull 3 fields for each record.
Here is an example of the XML being passed: <object> <property @propID="14" @propType="4" @propValue="Blah blah text" /> <property @propID="217" @propType="2" @propValue="Some other text" /> </object>
I have a table like this in my database: CREATE TABLE SCENE_PROPERTY_LINK (ID INT, OBJ_ID INT, PROPERTY_ID INT, PROPERTY_VALUE NTEXT) and I want a stored procedure that will accept XML and update this table. Here is what I am trying: CREATE PROCEDURE sp_UpdateObject @inValues XML AS BEGIN --create a temporary table DECLARE @props TABLE(PROPID INT, PROPTYPE INT, PROPVALUE NTEXT)
--And then insert the values from the @inValues XML argument into the temporary table --I am sure the SELECT statement is VERY wrong
INSERT INTO @props(PROPID, PROPTYPE, PROPVALUE) SELECT @inValues('@propID', INT), @inValues('@propType', INT), @inValues('@propValue', NTEXT) FROM @inValues.nodes('/object/property')
--...and then I will use the temp table to update the DB table (SCENE_PROPERTY_LINK) for each record where SCENE_PROPERTY_LINK.PROPERTY_ID = @props.PROPID AND @props.PROPTYPE != 6
END
I am sure it would be more efficient to update the DB table directly from the XML argument, without using the temporary table. But, I will settle for this solution using the temp table. I have done some work creating XML output from several stored procedures but, this is the first time I have been faced with consuming XML input in SQL.
I apologize for the long post. Thanks in advance for any help you can provide.
I am facing a problem while i pass xml as an intput to stored procedure. The problem is that there are ceratin special characters which when used as a part of xml give error.Like the input which i give to my sp is :
Declare @XMLString XML Set @XMLString = N'<Company CompanyName = "Hilary Group & Sons" Code = "HGS" > </Company>' Exec sproc_Insert_Company @XMLString
The error which i get on execution is: Msg 9421, Level 16, State 1, Line 2 XML parsing: line 2, character 34, illegal name character..
Its being generated because of the '&' being used in CompanyName.
I have a project which will be a tools to edit different tables.
Now I need a stored_procedure to select data from different table.
For example I have a table name "TableFields" which have "tableID","FieldName", "DataType"and so on columns. It has the following records. "1","EmployeeID","Varchar" "1","FirstName","varchar" "1","LastName","varchar" "1", "EmployedDate","date"
It has the following records. "2","AddressID","int" "2","ApartNo","varchar" "2", "Address","varchar"
Then I have table named "Employee" has columns "employeeID","FirstName","LastName","EmployedDate" which have the following data, "001","Susan","Daka","1999-09-09", "002","Lisa","Marzs","1999-08-08", "003","David","Smith","2000-01-01",
I also have address table has columns "AddressID","ApartNo","Address" and has the following data "1","1101","1208 Mornelle Crt, Toronto", "2","1209","1940 Garden Drive, Toronto"
I need to create a stored procedure to select data from table "employee " or table "address" or even other tables according to information from "TableFields." So the table's name can be know as a input parameter, but the fields name will be a list of values and it all depends on tables.
I want to use fields name as a long string separated by",", like I have input "EmployeeID, FirstName,LastName" as an input parameter. But I don't know how to split the string.
Second, I need to create a stored procedure to insert or update data into these dynamically table.
HiThe scenario:The price of products are determined by size.I have a Prices table that contains 3 columnsWidth Length and Price.User inputs their own width and length values as inWidth and inLength.It is unlikely that these values will exactly match existing lengths and widths in the price table.I need to take these User Input values and round them up to the nearest values found in the Prices table to pull the correct price.What is the most efficient way of achieving this?Thanks for your time.C# novice!
I have a stored procedure that takes an input string parameter defined as @name nvarchar(24). The stored procedure takes this string and insert it into a table with the column also defined as nvarchar(24). When I execute this stored procedure with a string of more than 24 characters, the input string somehow get truncated and inserted successfully into the table without giving an error. Why is this the case?
If I simply execute an insert statement with a string longer than 24 characters, it will give me an error message. I tried enclosing the insert statement with a try-catch block in the stored procedure but I still can't trap any error.
is there a way i could split those values for input into the database? no, right? i would need a seperate stored procedure that would take each value one at a time...correct?
could someone please let me know if i am declaring the parameter wrong or have the wrong VB CODE. I receive my 3 column headers in my datagrid but the parameter isn't doing anything STORED PROCEDURE CREATE PROCEDURE USP_Machcusidsearch @Machcusid nvarchar OUTPUTAS SELECT dbo.Machine.machcustomID, dbo.Problem.ProblemDesc, dbo.Request.ReqDateFROM dbo.Machine INNER JOIN dbo.Request ON dbo.Machine.machID = dbo.Request.MachID INNER JOIN dbo.Problem ON dbo.Request.ProblemID = dbo.Problem.ProblemIDwhere machcustomID = @MachcusidGO VB.NET CODE Private Sub LSBmachcusid_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles LSBmachcusid.SelectedIndexChanged SqlDataAdapter2.Fill(DsMachcusidsearch1) SqlSelectCommand2.Parameters("@Machcusid").Value = LSBmachcusid.SelectedItem.Value DGstatussearch.DataBind() End Sub End Class
I've been tasked with creating a stored procedure which will be executed after a user has input one or more parameters into some search fields. So they could enter their 'order_reference' on its own or combine it with 'addressline1' and so on.
What would be the most proficient way of achieving this?
I had initially looked at using IF, TRY ie:
IF @SearchField= 'order_reference' BEGIN TRY select data from mytables END TRY
However I'm not sure this is the most efficient way to handle this.
Is it possible to have an entire sql select statement as the input variable to a stored procedure? I want the stored procedure to execute the select statement.
ie.
exec sp_SomeFunc 'select * from table1 where id=1'
It may sound weird, but I have my reason for wanting to do it this way. Is this possible? if so, how do I implement this inside the stored procedure?
I have 2 source tables emp_ass,aprvl_status these tables are not having common column to join. and 1 target table Time_Card, i have a stored procedure with 4 input parameters, emp_ass_id,status_id,start date,end date,i am inserting data into timecard based on emp_ass_id, my week start date is sunday and end date is saterday if emp start date is sunday i am just incremnting the start date by 7 days as end date is saterday and inserting that row, if employe statrt date is other than Sunday. i am just insering start date with to reach end date saterday, this work fine when i give the input parameters, now my reqirement is i need to automate this process as i need to get new emp_ass_id which is not in target table and insert his records based on his start date and end date, ex: if emp_ass_id is 1001, start date 1/1/2008 and end date is 2/1/2008 then i need to insert
the stored procedure will insert these records if i give the input parameters, now i need to automate this process by using SSIS. please help me,i need to get emp_ass_id,start_date,end_date dynamically from source table if emp_ass_id is not in target table.
Hi I have this procedure it is creating the proc but when I execute it gives error Msg 137, Level 15, State 1, Line 1 Must declare the scalar variable "@ID". Msg 137, Level 15, State 1, Line 1 Must declare the scalar variable "@nextCode".
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO
CREATE PROCEDURE [dbo].[GetNextAction]
( @Code char(10), @Track varchar(30)
) AS BEGIN
SET NOCOUNT ON; Declare @ID int; DECLARE @SQL1 VARCHAR(2000) SET @SQL1='Select @ID = Sequence from'+' '+ @Track+ ' where Code=@Code' EXEC(@SQL1); Declare @nextCode varchar; DECLARE @SQL2 VARCHAR(2000) SET @SQL2 ='Select @nextCode= Code from '+' '+ @Track+ ' where sequence =(@ID+1)' EXEC(@SQL2); Declare @NextAction varchar(30); Select @NextAction= nextAction from [dbo].[CaseStage] where Code=@nextCode; Select @NextAction;
I have a small problem with a join clause, because i need to return all values from my table BL:
my code is:
SELECT cast(0 as bit) as 'Escolha',data, contado , ollocal ,origem, ousrdata,ousrhora FROM ( SELECT noconta,banco, u_area FROM BL
[code]....
In fact, i need to return 2 accounts (16,35) - x.NOCONTA IN (16,35), but I know that the problem is on the WHERE clause.How can do that, because i need all the condition on WHERE clause regarding my table OL, but also, i need to return my two accounts (16,35).
