How Can You Use A Variable Tablename And Retrieve The Output From The Insert?
Aug 28, 2007
We are trying to create a unique key from a table with indentity set in the table. We will have a number of these tables. Therefore, we will be creating a stored procedure and passing the table as a parameter. In this example we are setting the table.
When we run the the script, the output clause from the insert should give us a unique number from the given table in the temporary table. This example stores the output in a temporary table @tTemp.
How can you use a variable table name and retrieve the output from the Insert?
declare @tTestTable varchar (20)
set @tTestTable = 'mis.test_sequence'
--DECLARE @tTestTable TABLE ( sqVal [int] IDENTITY(1,1) NOT NULL, add_date datetime)
declare @testsql varchar (4000), @testseq int
DECLARE @tTemp table (mainpk int)
set @testsql = 'DECLARE @tTemp table (mainpk int) INSERT ' + @tTestTable + ' OUTPUT INSERTED.sqVal into @tTemp VALUES (getdate() ) SELECT @testseq=mainpk FROM @tTemp'
What C# code would capture the Scope_Identity value (CoDeptRowID) output by the code below? Do I even need to capture it or is it already available as a C# variable CoDeptRowID ? I can't seem to get my hands on it! SqlDataSource1.Insert();<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" InsertCommand="INSERT INTO [CompanyDepartment] ([User_Name], [FirstName], [LastName]) VALUES (@User_Name, @FirstName, @LastName); SELECT @CoDeptRowID = SCOPE_IDENTITY()" <insertparameters> <asp:sessionparameter Name="User_Name" Type="String" SessionField ="LoginName"/> <asp:controlparameter Name="FirstName" Type="String" ControlID="TextBox1" PropertyName ="text"/> <asp:controlparameter Name="LastName" Type="String" ControlID ="TextBox2" PropertyName ="text"/> <asp:Parameter Direction =Output Name ="CoDeptRowID" Type ="Int32" DefaultValue = "0" /> </insertparameters> </asp:SqlDataSource>
Can someone help me with the following function? I would like to use a table name as a variable.
Thanks in advance!
CREATE FUNCTION FAC_user.Overzicht_DTe (@tabel1 as nvarchar, @proces as nvarchar, @categorie as nvarchar) RETURNS numeric AS BEGIN declare @aantal numeric
if @proces = 'Inhuizen' begin if @categorie = 'open_op_tijd' begin SET @aantal =(SELECT Count(@tabel1 + '.Contractnummer') FROM @tabel1, Rapportageweek WHERE@tabel1.Verwerkingsdatum is null AND @tabel1.UiterlijkeVerwDatum >= Rapportageweek.Rapportagedatum AND @tabel1.ItemType = 'ZVHG' AND @tabel1.ItemType = 'ZVHN' AND @tabel1.ItemType = 'ZVIG' AND @tabel1.ItemType = 'ZVIN' GROUP BY@tabel1.Maand, @tabel1.Jaar) end
if @categorie = 'open_te_laat' begin SET @aantal =(SELECT Count(@tabel1 + '.Contractnummer') FROM @tabel1, Rapportageweek WHERE@tabel1.Verwerkingsdatum is null AND @tabel1.UiterlijkeVerwDatum < Rapportageweek.Rapportagedatum AND @tabel1.ItemType = 'ZVHG' AND @tabel1.ItemType = 'ZVHN' AND @tabel1.ItemType = 'ZVIG' AND @tabel1.ItemType = 'ZVIN' GROUP BY@tabel1.Maand, @tabel1.Jaar) end
I have a stored procedure that accepts the table name as a parameter. Is there anyway I can use this variable in my select statement after the 'from' clause. ie "select count(*) from @Table_Name"? When I try that is says "Must declare the table variable @Table_Name". Thanks!
I am trying to create a stored procedure for automating Bulk inserting into tables for one database to another. Is there any way i can pass the table name as variable to insert and select stmt
I am using a sql task to get all tablenames and then passing the output to another sql task inside a for each container.
So that the 2nd sql task will be executed for each table. My query looks like SELECT DISTINCT b.EmailAddress FROMÂ ? ......
Since I am passing the tablename as a variable (output from the 1st sql task), I get the following error:
[Task Execute SQL] Error:
Failed to execute the query 'SELECT DISTINCT b.EmailAddress FROM? AS a INNER... ':' Failed to extract
the result in a variable of type (DBTYPE_I4)'. Possible causes include the following: Problems with the query, not properly fixed ResultSet property, not properly set parameters or not properly established connection.
ok, I am on Day 2 of being brain dead.I have a database with a table with 2 varchar(25) columns I have a btton click event that gets the value of the userName, and a text box.I NEED to insert a new row in a sql database, with the 2 variables.Ive used a sqldatasource object, and tried to midify the insert parameters, tried to set it at the button click event, and NOTHING is working. Anyone have a good source for sql 101/ASP.Net/Braindead where I can find this out, or better yet, give me an example. this is what I got <%@ Page Language="C#" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><script runat="server"> protected void runit_Click(object sender, EventArgs e) { //SqlDataSource ID = "InsertExtraInfo".Insert(); //SqlDataSource1.Insert(); } protected void Button1_Click1(object sender, EventArgs e) { SqlDataSource newsql; newsql.InsertParameters.Add("@name", "Dan"); newsql.InsertParameters.Add("@color", "rose"); String t_c = "purple"; string tempname = Page.User.Identity.Name; Label1.Text = tempname; Label2.Text = t_c; newsql.Insert(); }</script><html xmlns="http://www.w3.org/1999/xhtml" ><head runat="server"> <title>mini update</title></head><body> <form id="form1" runat="server"> name<asp:TextBox ID="name" runat="server" OnTextChanged="TextBox2_TextChanged"></asp:TextBox><br /> color <asp:TextBox ID="color" runat="server"></asp:TextBox><br /> <br /> <asp:Button ID="Button1" runat="server" OnClick="Button1_Click1" Text="Button" /> <br /> set lable =><asp:Label ID="Label1" runat="server" Text="Label" Width="135px" Visible="False"></asp:Label><br /> Lable 2 => <asp:Label ID="Label2" runat="server" Text="Label"></asp:Label><br /> Usernmae=><asp:LoginName ID="LoginName1" runat="server" /> <br /> <br /> <br /> <br /> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConflictDetection="CompareAllValues" ConnectionString="<%$ ConnectionStrings:newstring %>" DeleteCommand="DELETE FROM [favcolor] WHERE [name] = @original_name AND [color] = @original_color" InsertCommand="INSERT INTO [favcolor] ([name], [color]) VALUES (@name, @color)" OldValuesParameterFormatString="original_{0}" SelectCommand="SELECT [name], [color] FROM [favcolor]" UpdateCommand="UPDATE [favcolor] SET [color] = @color WHERE [name] = @original_name AND [color] = @original_color"> <DeleteParameters> <asp:Parameter Name="original_name" Type="String" /> <asp:Parameter Name="original_color" Type="String" /> </DeleteParameters> <UpdateParameters> <asp:Parameter Name="color" Type="String" /> <asp:Parameter Name="original_name" Type="String" /> <asp:Parameter Name="original_color" Type="String" /> </UpdateParameters> <InsertParameters> <asp:InsertParameter("@name", "Dan", Type="String" /> <asp:InsertParameter("@color", "rose") Type="String"/> </InsertParameters> </asp:SqlDataSource> <asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True" AutoGenerateColumns="False" DataKeyNames="name" DataSourceID="SqlDataSource1"> <Columns> <asp:CommandField ShowDeleteButton="True" ShowEditButton="True" ShowSelectButton="True" /> <asp:BoundField DataField="color" HeaderText="color" SortExpression="color" /> <asp:BoundField DataField="name" HeaderText="name" ReadOnly="True" SortExpression="name" /> </Columns> </asp:GridView> </form></body></html>
I am able to perform my Stored Procedure, but I can't figure out how to get access to the OUTPUT parameters!Here is my code: Protected Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs)
Dim mySqlDataSource As SqlDataSource = New SqlDataSource
' Add Some Parameter Dim someParameter As Parameter = New Parameter("someParameterName", TypeCode.String) someParameter.DefaultValue = New String("Some Value") someParameter.Direction = Data.ParameterDirection.Output
I'm trying to use a store procedure to add an item into the database and retrieve the id of such item within and output parameter but this is not happening. The item is added into the db but the output param is not modified. Here is the code: SP CREATE procedure dbo.AddItem(@Desc nvarchar(100),@intItemID int output)as insert into RR_Item ( desc ) values ( @Desc ) select @intItemID = SCOPE_IDENTITY()GO I have tried in the last line of the SP select @intItemID = SCOPE_IDENTITY() select @intItemID = @@IDENTITY select @intItemID = max(itemid) from RR_Item but nothing seems to work. I'm calling the store procedure as follows from asp.net: Dim intItemID As New SqlParameter("@intItemID", SqlDbType.Int) intItemID.Direction = ParameterDirection.Output SqlHelper.ExecuteDataset(objConn.ConnectionString, "AddItem", desc, intItemID) MsgBox(intItemID.Value.ToString)
Hi, I'm having problems retrieving the output parameter from my stored procedure, i'm getting the following error An SqlParameter with ParameterName '@slideshowid' is not contained by this SqlParameterCollection code as follows: int publicationId = (int) Session["PublicationId"];string title = txtTitle.Text;int categoryid = Convert.ToInt32(dlCategory.SelectedItem.Value);SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);SqlCommand myCommand = new SqlCommand("sp_be_addSlideshow", myConnection);myCommand.CommandType = CommandType.StoredProcedure;myCommand.Parameters.Add(new SqlParameter("@publicationid", SqlDbType.Int));myCommand.Parameters["@publicationid"].Value = publicationId;myCommand.Parameters.Add(new SqlParameter("@title", SqlDbType.NVarChar));myCommand.Parameters["@title"].Value = title;myCommand.Parameters.Add(new SqlParameter("@categoryid", SqlDbType.Int));myCommand.Parameters["@categoryid"].Value = categoryid;myConnection.Open();myCommand.ExecuteNonQuery();string slideshowId = myCommand.Parameters["@slideshowid"].Value.ToString();myConnection.Close(); my stored procedure: CREATE PROCEDURE sp_be_addSlideshow( @publicationid int, @title nvarchar(50), @categoryid int, @slideshowid int = NULL OUTPUT)AS INSERT INTO Slideshow(publicationid,title,slideshowcategoryid,deleted) VALUES (@publicationid,@title,@categoryid,0) SELECT @slideshowid = SCOPE_IDENTITY()GO
I'm trying to output the number of rows that were effected by my stored proc.
Here is the stored proc:
ALTER Proc Update_IndividualMoves_GTPhone_NCOAPhone_Differ AS Update Results SET Results.home_phone = Results.NEWPhone, Results.Address1 = Results.NCOAADDRESS1, Results.CITY = Results.NCOACITY, Results.ST = Results.NCOAST, Results.ZIP_OUT = Results.NCOAZIP5, Results.ZIP4_OUT = Results.NCOAZ4 Where AddressServiceStatus = 'I' AND home_phone IS NOT NULL AND NEWPhone IS NOT NULL AND home_phone <> NEWPhone Return @@Rowcount
Here is the code from the DAL class that I'm calling the stored procedure from (I'm using the SQL Helper Class.)
Public Shared Function GetAddressIncorrect_HH_GTPhone_NCOAPhone_Differ()
Dim Rowcount As Integer Dim GlobalConnString As String = AppSettings("ConnectionString") ''Put proc in that gets this data out for household moves that have both ''GTPro and NCOA update phone numbers however they differ. Does not apply ''to DRC donors / < 12 month donors. Update to latest and greatest phone number ''from NCOA listing.
Try Return ExecuteDataset(GlobalConnString, CommandType.StoredProcedure, "Update_HouseholdMoves_GTPhone_NCOAPhone_Differ", New SqlParameter("@@Rowcount", Rowcount))
Catch ex As Exception Throw New ApplicationException("An error occured when calling this stored proc out Update_HouseholdMoves_GTPhone_NCOAPhone_Differ")
End Try End Function
I want to post how many rows were effected in a label that is located on my aspx page through referencing the function above:
What I'm doing is activating the function through an asp:button control and then I want to display the @@Rowcount result in the label next to it.
Here is what I have now:
<code>
Private Sub cmdHouseholdMove2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdHouseholdMove2.Click 'Dim rowcount As Integer GetAddressIncorrect_HH_GTPhone_NCOAPhone_Differ(New SqlParameter("@@rowcount", lblHouseholdMoves2.Text))
End Sub
If anyone knows how to do this please let me know:
Hi Forum,I have a split function.CREATE FUNCTION dbo.Split2(@List nvarchar(2000),@SplitOn nvarchar(5)) RETURNS @RtnValue table (Id int identity(1,1),Value nvarchar(100)) AS BEGINWhile (Charindex(@SplitOn,@List)>0)Begin Insert Into @RtnValue (value)Select Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1))) Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))End Insert Into @RtnValue (Value) Select Value = ltrim(rtrim(@List)) ReturnEND Courtesy p2p.wrox.com It returns a TableHow to take the return into a temporary table?I want it inside an SP.Because i have a varchar field named occasionTime (varchar) in a tablea sample data inside it is "January,21,EveryWeek"In my SP i have to get either "January" and compare it to something or "21" or "EveryWeek" for comparison.How to do it?Regards,Naveen
Can we retrieve data from table type variable as an array in oracle key values..
E.g.
ALTER PROCEDURE prn1( @p_prn as KeyValuePair readonly) -- proc which having parameter table type as parameter AS declare @v_formatted_str varchar(50) BEGIN set @v_formatted_str = v_formatted_str + @p_prn(1)(1) END
This problem is being seen on SQL 2005 SP2 + cumulative update 4
I am currently successfully using the output clause of an insert statement to return the identity values for inserted rows into a table variable
I now need to add an "instead of insert" trigger to the table that is the subject of the insert.
As soon as I add the "instead of insert" trigger, the output clause on the insert statement does not return any data - although the insert completes successfully. As a result I am not able to obtain the identities of the inserted rows
Note that @@identity would return the correct value in the test repro below - but this is not a viable option as the table in question will be merge replicated and @@identity will return the identity value of a replication metadata table rather than the identity of the row inserted into my_table
Note also that in the test repro, the "instead of insert" trigger actually does nothing apart from the default insert, but the real world trigger has additional code.
To run the repro below - select each of the sections below in turn and execute them 1) Create the table 2) Create the trigger 3) Do the insert - note that table variable contains a row with column value zero - it should contain the @@identity value 4) Drop the trigger 5) Re-run the insert from 3) - note that table variable is now correctly populated with the @@identity value in the row
I need the behaviour to be correct when the trigger is present
GO /************************************************ 2) - Create the trigger ************************************************/ CREATE TRIGGER [dbo].[trig_my_table__instead_insert] ON [dbo].[my_table] INSTEAD OF INSERT AS BEGIN
INSERT INTO my_table ( forename, surname) SELECT forename, surname FROM inserted
END
/************************************************ 3) - Do the insert ************************************************/
INSERT INTO my_table ( forename , surname ) OUTPUT inserted.my_table_id INTO @my_insert VALUES( @forename , @surname )
select @@identity -- expect this value in @my_insert table select * from @my_insert -- OK value without trigger - zero with trigger
/************************************************ 4) - Drop the trigger ************************************************/
drop trigger [dbo].[trig_my_table__instead_insert] go
/************************************************ 5) - Re-run insert from 3) ************************************************/ -- @my_insert now contains row expected with identity of inserted row -- i.e. OK
insert into #t(branchnumber) values (005) insert into #t(branchnumber) values (090) insert into #t(branchnumber) values (115) insert into #t(branchnumber) values (210) insert into #t(branchnumber) values (216)
[code]....
I have a parameter which should take multiple values into it and pass that to the code that i use. For, this i created a parameter and temporarily for testing i am passing some values into it.Using a dynamic SQL i am converting multiple values into multiple records as rows into another variable (called @QUERY). My question is, how to insert the values from variable into a table (table variable or temp table or CTE).OR Is there any way to parse the multiple values into a table. like if we pass multiple values into a parameter. those should go into a table as rows.
I am using C# in  Visual Studio 2008 and remote database as sql server 2008 R2. I want to read remote database table's field value and i have to move that read value to string variable. how to do it.Â
And my code is :
string sql = "Select fldinput from tmessage_temp where fldTo=IDENT_CURRENT('tmessage_temp')"; SqlCommand exesql = new SqlCommand(sql, cn); exesql.CommandType = CommandType.Text; SqlDataReader rd1 = default(SqlDataReader); rd1 = exesql.ExecuteReader();
(Newbie) Hi, I am trying to create in a session variable, the ID of the last inserted record. My reading suggests I should use Scope_Identity. I'm having trouble with the syntax/code structure. Also, is it good programming practise to directly assign the session variable e.g. "Session[var]=SqlDataSource.Select()"? The error I'm getting from my code below is "No overload for method SELECT takes 0 arguments". Thanks. Session["snCoDeptRowID"] = SqlDataSource1.Select(); <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT Scope_Identity" </asp:SqlDataSource>
I am a "newbie" and have been struggling with this for days! I have users enter their residence information and insert which generates houseid. I want to use/display that houseid on next page/step. I am VERY FRUSTRATED and would appreciate any assistance! <script runat="server"> Protected Sub SqlDataSource1_Inserting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles SqlDataSource1.Inserting e.Command.Parameters("@house").Size = 5 End Sub
Protected Sub SqlDataSource1_Inserted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles SqlDataSource1.Inserted Dim house = e.Command.Parameters("@house").Value Response.Write(house) End Sub Protected Sub Wizard1_FinishButtonClick(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.WizardNavigationEventArgs) End Sub Protected Sub SqlDataSource1_Selecting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs)
End Sub </script> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConflictDetection="CompareAllValues" ConnectionString="<%$ ConnectionStrings:ic_registerConnectionString %>" oninserted="SqlDataSource1_Inserted" oninserting="SqlDataSource1_Inserting" DeleteCommand="DELETE FROM [household] WHERE [householdid] = @original_householdid AND [housenum] = @original_housenum AND [streeraddr] = @original_streeraddr AND [aptnum] = @original_aptnum AND [city] = @original_city AND [state] = @original_state AND [zipcode] = @original_zipcode AND [HHPhone] = @original_HHPhone AND [timedate] = @original_timedate" InsertCommand="INSERT INTO [household] ([housenum], [streeraddr], [aptnum], [city], [state], [zipcode], [HHPhone], [timedate]) VALUES (@housenum, @streeraddr, @aptnum, @city, @state, @zipcode, @HHPhone, { fn NOW() }); SELECT @house = SCOPE_IDENTITY()" OldValuesParameterFormatString="original_{0}" SelectCommand="SELECT * FROM [household]" UpdateCommand="UPDATE [household] SET [housenum] = @housenum, [streeraddr] = @streeraddr, [aptnum] = @aptnum, [city] = @city, [state] = @state, [zipcode] = @zipcode, [HHPhone] = @HHPhone, [timedate] = @timedate WHERE [householdid] = @original_householdid AND [housenum] = @original_housenum AND [streeraddr] = @original_streeraddr AND [aptnum] = @original_aptnum AND [city] = @original_city AND [state] = @original_state AND [zipcode] = @original_zipcode AND [HHPhone] = @original_HHPhone AND [timedate] = @original_timedate" OnSelecting="SqlDataSource1_Selecting"> <InsertParameters> <asp:Parameter Name="housenum" Type="String" /> <asp:Parameter Name="streeraddr" Type="String" /> <asp:Parameter Name="aptnum" Type="String" /> <asp:Parameter Name="city" Type="String" /> <asp:Parameter Name="state" Type="String" /> <asp:Parameter Name="zipcode" Type="String" /> <asp:Parameter Name="HHPhone" Type="String" /> <asp:Parameter Type = "String" Name="house" Direction= "Output"/> </InsertParameters> Next question is this easier to do using a Wizard Control and DetailsView on a "step" or using seperate pages and FormView? Or does it matter?
hey folks... i am new to store procedures, crystal and sql server... ..one heck of a combination..yikes....neways.... writing a stored procedure in 6.5 to run a crystal report 7.0... want to create an output variable in the procedure that the report will see as a field... can i do this...and if so how.... any and all help muchos gracious.... the higher ups just don't get why i can't do all in 10 days of learning sp's on my own.... egads... management :-)
I know this has been dealt with a lot, but I would still reallyappreciate help. Thanks.I am trying to transform this tableYY--ID-Code-R1-R2-R3-R4...R402004-1-101--1--2-3-42004-2-101--2--3-4-2....2005-99-103-4-3-2-1Into a table where the new columns are the count for 4-3-2-1 for everydistinct code in the first table based on year. I will get the yearfrom the user-end(Access). I will then create my report based on theinfo in the new table. Here's what I've tried so far (only for 1stcolumn):CREATE PROCEDURE comptabilisationDYN@colonne varchar(3) '*receives R1, then R2, loop is in vba access*ASDECLARE @SQLStatement varchar(8000)DECLARE @TotalNum4 intDECLARE @TotalNum3 intDECLARE @TotalNum2 intDECLARE @TotalNum1 intSELECT SQLStatement = 'SELECT COUNT(*) FROMdbo.Tbl_Réponses_Étudiants WHERE' + @colonne + '=4 AND YY = @year'EXEC sp_executesql @SQLStatement, N'@TotalNum4 int OUTPUT', @TotalNum4OUTPUT INSERT INTO Comptabilisation(Total4) VALUES (@TotalNum4)GO
create procedure usp_test (@AccountID INT)asbegin DECLARE @getAccountID CURSOR SET @getAccountID = CURSOR FOR SELECT Account_ID FROM Accounts OPEN @getAccountID FETCH NEXT FROM @getAccountID INTO @AccountID WHILE @@FETCH_STATUS = 0 BEGIN PRINT @AccountID FETCH NEXT FROM @getAccountID INTO @AccountID END CLOSE @getAccountID DEALLOCATE @getAccountIDend i get nearly ten rows in the print statement how can i assign the output to a out variable where i get all ten rows.
What I'm trying to do is get an XML query from a database and use the XML to render an excel document. Can this be done using SSIS? Can I actually read a column from a database and store it in a variable and then create an excel spreadsheet with that variable in SSIS? Would this be done in the control flow using the XML task editor?
I've been looking into using the ability to select the table of an OLEDB destination task based on a package variable but would like to know if this could be utilised in the following example.
I have a CSV file (potentially there are many) with a number of rows. The rows do not necessarily contain the same number of elements. Each row has an EventID and this is used to determine the database table that the row is to be inserted in.
Initially i thought about using a conditional split to send each row down the path relating to its event ID. There would be multiple destinations depending on the table. With the possibility of 60 to 70 different event ID's this is very quickly going to get out of hand in the designer. I'd like to know if it would be possible to set up a case statement (or similar) in a script component and specify the table name as a variable depending on the event ID. I'd then use this table variable to set the destination task's table property.
Does this sound like a possibility or do i have to go down the route of multiple paths?
I want to use sql server as a back end for my asp.net shopping cart using vb.net..i have created an "news.mdf" under app_data folder of my website in visual studio2010...but i tried to connect it to database using below command...but showing error...
I have a table with an Identity column. Is there a way to get the automatically generated primary key after I insert something to the table? If so, what is the syntax?
