Loop through #Temp_1
-Execute Sproc_ABC passing in #Temp_1.Field_TUV as parameter
-Store result set of Sproc_ABC into #Temp_2
-Update #Temp_1 SET #Temp_1.Field_XYZ= #Temp_2.Field_XYZ
End Loop
It appears scary from a performance standpoint, but I'm not sure there's a way around it. I have little experience with loops and cursors in SQL. What would such code look like? And is there a preferable way (assuming I have to call Sproc_ABC using Field_TUV to get the new value for Field_XYZ?
Hi. This is a SQL question. I am trying to wrap a stored procedure with a UDF so I can do selects against the result. The following doesn't work, but is it possible to do something like: Create Function test()returns @tmp table ( myfield int)asbegin insert into @tmp (field1) Exec dbo.MySprocWhichRequires3ParmsAndReturnsATable 5, 6, 10 output returnend Thanks
Hi. It seems to be very simple, actually, but I don't know if it isfeasible in TSQL. I have a sproc which gathers in one place many callsto different other sprocs, all of them taking a 'StoreGroupe'parameter. I would like to add a case where if the call has NOStoreGroupe parameter, the sproc should LOOP thru all records in tableStoreGroupeTable, read the column StoreCode, and pass that value as aparam to the other sprocs, as in:CREATE PROCEDURE MySproc(@StoreGroupe nvarchar(6) = NULL)ASif (@StoreGroupe is not null)BeginExec _Sproc1 @StoreGroupeExec _Sproc2 @StoreGroupeExec _Sproc3 @StoreGroupeExec _Sproc4 @StoreGroupe...............EndElseBeginA 'Group Code' has NOT been specifiedI want to take all the StoreGroups in tableStoreGroupeTable, in turn.I would like to do SOMETHING LIKE THIS:Do While not [StoreGroupeTable].EOFRead [Code] from [StoreGroupeTable]Set @StoreGroupe = The value I just readExec _Sproc1 @StoreGroupeExec _Sproc2 @StoreGroupeExec _Sproc3 @StoreGroupeExec _Sproc4 @StoreGroupe...............LoopEndGOIs that feasible in a sproc, or do I have to do this in the client(ADO) ?Thanks a lot.Alex.
The first query returns me the results from multiple databases, thesecond does the same thing except it puts the result into a #temptable? Could someone please show me an example of this using the firstquery? The first query uses the @exec_context and I am having achallenge trying to figure out how to make the call from within adifferent context and still insert into a #temp table.DECLARE @exec_context varchar(30)declare @sql nvarchar(4000)DECLARE @DBNAME nvarchar(50)DECLARE companies_cursor CURSOR FORSELECT DBNAMEFROM DBINFOWHERE DBNAME NOT IN ('master', 'tempdb', 'msdb', 'model')ORDER BY DBNAMEOPEN companies_cursorFETCH NEXT FROM companies_cursor INTO @DBNAMEWHILE @@FETCH_STATUS = 0BEGINset @exec_context = @DBNAME + '.dbo.sp_executesql 'set @sql = N'select top 10 * from products'exec @exec_context @sqlFETCH NEXT FROM companies_cursor INTO @DBNAMEENDCLOSE companies_cursorDEALLOCATE companies_cursor-------------------------------------------------------------------------------------CREATE TABLE #Test (field list here)declare @sql nvarchar(4000)DECLARE @DBNAME nvarchar(50)DECLARE companies_cursor CURSOR FORSELECT NAMEFROM sysdatabasesWHERE OBJECT_ID(Name+'.dbo.products') IS NOT NULLORDER BY NAMEOPEN companies_cursorFETCH NEXT FROM companies_cursor INTO @DBNAMEWHILE @@FETCH_STATUS = 0BEGINset @sql = N'select top 10 * from '+@DBNAME+'.dbo.products'INSERT INTO #Testexec (@sql)FETCH NEXT FROM companies_cursor INTO @DBNAMEENDCLOSE companies_cursorDEALLOCATE companies_cursorSELECT * from #TestDROP TABLE #Test
I'm new to sql and could do with some help resolving this issue.
My problem is as follows,
I have two tables a BomHeaders table and a BomComponents table which consists of all the components of the boms in the BomHeaders table.
The structure of BOMs means that BOMs reference BOMs within themselves and can potentially go down many levels:
In a simple form it would look like this:
LevelRef: BomA
1component A 1component B 1Bom D 1component C
What i would like to do is potentially create a temporary table which uses the BomReference as a parameter and will loop through the records and bring me back every component from every level
Which would in its simplest form look something like this
LevelRef: BomA
1......component A 1......component B 1......Bom D 2.........Component A 2.........Component C 2.........Bom C 3............Component F 3............Component Z 1......component C
I would like to report against this table on a regular basis for specific BomReferences and although I know some basic SQL this is a little more than at this point in time i'm capable of so any help or advice on the best method of tackling this problem would be greatly appreciated.
also i've created a bit of a diagram just in case my ideas weren't conveyed accurately.
i would like to loop through the table and concatenate the pocket filed for all the records that has the same pk. and insert the pk and the concatenated string into another table in a timely manner.
I want to take the contents from a table of appointments and insert theappointments for any given month into a temp table where all theappointments for each day are inserted into a single row with a columnfor each day of the month.Is there a simple way to do this?I have a recordset that looks like:SELECTa.Date,a.Client --contents: Joe, Frank, Fred, Pete, OscarFROMdbo.tblAppointments aWHEREa.date between ...(first and last day of the selected month)What I want to do is to create a temp table that has 31 columnsto hold appointments and insert into each column any appointments forthe date...CREATE TABLE #Appointments (id int identity, Day1 nvarchar(500), Day2nvarchar(500), Day3 nvarchar(500), etc...)Then loop through the recordset above to insert into Day1, Day 2, Day3,etc. all the appointments for that day, with multiple appointmentsseparated by a comma.INSERT INTO#Appointments(Day1)SELECTa.ClientFROMdbo.tblAppointments aWHEREa.date = (...first day of the month)(LOOP to Day31)The results would look likeDay1 Day2 Day3 ...Row1 Joe, PeteFrank,FredMaybe there's an even better way to handle this sort of situation?Thanks,lq
I keep getting 0 returned, in both Query Analyzer and in my code. My double 'rate' just always ends up 0 or Null, and I've tried many different approaches. Here is the general idea. This is all for phone number searching.
I have a table with three columns - country, countrycode, and rate. The country code is two or three digits. In the code, I strip the first two digits of phoneNumber to make codeTwo and then strip the first three to make codeThree. That part works. I then pass codeTwo and codeThree to the sproc.
There are no overlapping codes (ie 23 and 237), so I search for a country code matching codeTwo and return that value. If none is found, then I search for a country code matching codeThree and return that value. If none is still found I return 0.
Here is the SQL 2000 stored procedure:
CREATE PROCEDURE getRate
( @codeTwo int, @codeThree int, @rate int OUTPUT ) AS
SELECT @rate = rate FROM rates WHERE countrycode = @codeTwo
IF @@rowcount = 0
SELECT @rate = rate FROM rates WHERE countrycode = @codeThree
Hi, with SQL server 2005. Could I write a SPROC with C# or VB.NET that calls methods within the .net library or Could I package a .net methods into a SPROC to let other SPROC invoke it?
I'm trying to analyze a 2-Table Select Statement and compare to variables.
if x > y then do math and run an update command
if y > x then do other math and run update command
It's very slow.. about 1 record a second.
I haven't used store procs yet, but from what I understand, should increase performance.
However, I'm having a difficult time tracking down any examples of this simple procedure as a SPROC. I could try using Managed Code in SQL 2005 and writing the sproc in C#.. but that has a learning curve all it's own.
Can someone either point me to a SPROC Tutorial online, or outline the basics for the above function above.. it's too late to go to Borders and the Library didn't have any SQL books not checked out.
Hi,I have a sproc with 5 params that takes about 40 seconds to return.But when I Create a Temp table and do aInsert Into #tempExec sproc param1, param2, param3, param4, param5it never returns...any ideas?Thanks,Bill
Hi friends, I've been stumped on this for almost a week now. Everything works in the stored procedure code below except for the 'INSERT INTO @Uppdates' block of code. I have a SQL Analyzer test driver and when the code gets to the SELECT statement below the INSERT INTO @Updates line the value of the select line is displayed on the screen and nothing gets written to @Updates. I hope I'm being clear about this. Any ideas? IF @Edit=1 AND LEN(@Changes) > 0 BEGIN --Split and parse changes DECLARE @curRec int, @nxtRec int, @Record varchar(8000), @TNum int, @TNam varchar(50), @PDesc varchar(512), @PChk varchar(8), @SNum varchar(12), @NScr varchar(10), @OScr varchar(10), @curField int, @nxtField int, @curSRec int, @nxtSRec int, @subRec varchar(8000), @curSField int, @nxtSField int
WHILE @curRec IS NOT NULL BEGIN SET @nxtRec = NULLIF(CHARINDEX(CHAR(1), @Changes, @curRec), 0) SET @Record = SUBSTRING(@Changes, @curRec, ISNULL(@nxtRec,8000)-@curRec) --Extract a class record SET @curField = 1 SET @nxtField = NULLIF(CHARINDEX(CHAR(2), @Record, @curField), 0) SET @TNum = SUBSTRING(@Record, @curField, ISNULL(@nxtField,1000)-@curField) -- Extract Teacher Number SET @curField = @nxtField + 1 SET @nxtField = NULLIF(CHARINDEX(CHAR(2), @Record, @curField), 0) SET @TNam = SUBSTRING(@Record, @curField, ISNULL(@nxtField,1000)-@curField) -- Extract Teacher Name SET @curField = @nxtField + 1 SET @nxtField = NULLIF(CHARINDEX(CHAR(2), @Record, @curField), 0) SET @PDesc = SUBSTRING(@Record, @curField, ISNULL(@nxtField,1000)-@curField) -- Extract Project Description SET @curField = @nxtField + 1 SET @nxtField = NULLIF(CHARINDEX(CHAR(3), @Record, @curField), 0)-- Step over existing checksum SET @PChk = RIGHT('0000000' + dbo.int2base(Checksum(@PDesc),16),8)-- Calculate new checksum based on project description that may have been changed. SET @curField = @nxtField + 1
INSERT INTO @NewProj (ProjectID, SchoolNumber, ArtTeacherNumber, TeacherNumber, TeacherName, ProjectDescription, [Checksum]) SELECT DISTINCT Students.ProjectID, @SchoolNumber, @ArtTeacherNumber, @TNum, @TNam, @PDesc, @PChk FROM @Students Students WHERE Students.SchoolNumber=@SchoolNumber AND Students.TeacherNumber=@TNum
SET @curSRec = 1 WHILE @curSRec IS NOT NULL BEGIN SET @nxtSRec = NULLIF(CHARINDEX(CHAR(3), @Record, @curField), 0) SET @subRec = SUBSTRING(@Record, @curField, ISNULL(@nxtSRec,8000)-@curField) -- Extract a score sub record. Consists of Student Number, new Score, old Score. SET @curSField = 1 SET @nxtSField = NULLIF(CHARINDEX(CHAR(4), @subRec, @curSField), 0) SET @SNum = SUBSTRING(@subRec, @curSField, ISNULL(@nxtSField, 1000)-@curSField) -- Extract Student Number SET @curSField = @nxtSField + 1 SET @nxtSField = NULLIF(CHARINDEX(CHAR(4), @subRec, @curSField), 0) SET @NScr = SUBSTRING(@subRec, @curSField, ISNULL(@nxtSField, 1000)-@curSField) -- Extract new Score SET @curSField = @nxtSField + 1
IF @curSField > LEN(@subRec) SET @Oscr = NULL-- If no Old Score specified ELSE BEGIN SET @nxtSField = LEN(@subRec) + 1 SET @OScr = SUBSTRING(@subRec, @CurSField, ISNULL(@nxtSField, 1000)-@curSField) -- Extract old Score END
-- Check for errors IF ISNUMERIC(@SNum) = 0 OR @NScr IS NULL OR LEN(ISNULL(@PChk,0)) <> 8 BEGIN SET @UpdateErr = 1 BREAK END
-- Update the updates table and find ProjectID from existing data table INSERT INTO @Updates (ProjectID, StudentNumber, NewScore, OldScore) SELECT DISTINCT Students.ProjectID, @SNum, @NScr, @OScr FROM @Students Students WHERE Students.StudentNumber=@SNum
SET @curField = @nxtSRec + 1 SET @curSRec = @nxtSRec + 1 select * from @Updates END IF @UpdateErr = 1 BEGIN BREAK END SET @curRec = @nxtRec + 1 END Thanks in advance for looking at this,
i am getting some weird behaviour in my sql server 2000 code pasted below. When using the step-through, it seems that i get to the line: While objReader.Read() and then the compiler jumps to "End Try" without going inside the objReader.Read() statement. I am new to this and would appreciate some insight. why isn't entering the conditions within the while loop? I am new to sql and stored procedures so, i'd appreciate any advice at the moment.
thanks in advance.
Code:
Private Function VerifyCredentials(ByVal emailAddress As String, _ ByVal password As String) As Boolean '<sumamry> ' ||||| Declare Required Variables ' ||||| Access appSettings of Web.Config for Connection String (Constant) '</summary> ' ||||| First is the Connection Object for an Access DB Dim MyConn As SqlConnection = New SqlConnection("server=ARIA;database=dushkinmedia;Integrated Security=SSPI")
'<sumamry> ' ||||| Create a OleDb Command Object ' ||||| Pass in Stored procedure ' ||||| Set CommandType to Stored Procedure '</summary>
' ||||| To Access a Stored Procedure in Access - Requires a Command Object Dim MyCmd As New SqlCommand("sp_ValidateUser", MyConn) ' ||||| To Access a Stored Procedure in SQL Server - Requires a Command Object
MyCmd.CommandType = CommandType.StoredProcedure ' ||||| Create Parameter Objects for values passed in Dim objParam1, objParam2 As SqlParameter '<sumamry> ' ||||| Add the parameters to the parameters collection of the ' ||||| command object, and set their datatypes (OleDbType in this case) '</summary> objParam1 = MyCmd.Parameters.Add("@emailAddress", SqlDbType.VarChar) objParam2 = MyCmd.Parameters.Add("@password", SqlDbType.VarChar)
'' ||||| Set the direction of the parameters...input, output, etc objParam1.Direction = ParameterDirection.Input objParam2.Direction = ParameterDirection.Input '' ||||| Set the value(s) of the parameters to the passed in values objParam1.Value = _emailAddress.Text objParam2.Value = _password.Text
' ||||| Try, catch block! Try ' ||||| Check if Connection to DB is already open, if not, then open a connection If MyConn.State = ConnectionState.Closed Then ' ||||| DB not already Open...so open it MyConn.Open() End If
' ||||| Create OleDb Data Reader Dim objReader As SqlDataReader objReader = MyCmd.ExecuteReader(CommandBehavior.CloseConnection) ' ||||| Close the Reader and the Connection Closes with it
'PROBLEM HERE: NEVER ENTERS CONDITIONS OF WHILE LOOP While (objReader.Read()) If CStr(objReader.GetValue(0)) <> "1" Then Return False 'lblMessage.Text = "Invalid Login!" Else objReader.Close() ' ||||| Close the Connections & Reader Return True End If End While Catch ex As Exception lbTEMP.Text = ex.ToString 'tmp errorhandling Return False 'lblMessage.Text = "Error Connecting to Database!" End Try
I have gridview display a list of users. I have added a column for a check box. If the box is checked I move the users to another table.I need to pass some parameter of or each row to a stored proc. My question. In the loop where I check if the checkbox is selected I need to call the stored procedure.Currently I do an open and closed inside the loop.What is best and most effficent method of doing this should I open and close the connection outside the loop and change the procs parameters as loop through. System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection( System.Configuration.ConfigurationManager.ConnectionStrings["connString"].ConnectionString); System.Data.SqlClient.SqlCommand commChk = new System.Data.SqlClient.SqlCommand("storedProc", conn); commChk.CommandType = System.Data.CommandType.StoredProcedure; commChk.Parameters.AddWithValue("@mUID", ddMainUser.SelectedValue.ToString()); commChk.Parameters.AddWithValue("@sUId", gvUsers.Rows[i].Cells[2].Text); commChk.Connection.Open(); commChk.ExecuteNonQuery(); conn.Close(); If so exactly how do I do this? How do I reset the parmaters for the proc? I haven't done this before where I need to loop through passing parameter to the same proc. thanks
I would like to know what are the ways to call a parameterized stored procedure within a loop in ASP.NET. Scenario: I have a loop through a dataSet and for each record I am going to execute a stored procedure to insert data in many tables. I can not use the following syntax: cmd.CommandType = CommandType.StoredProcedure cmd.CommandText = "dbo.storedprocedurename" With cmd .Parameters.Add(New SqlClient.SqlParameter("@param1", paramValue1)) .Parameters.Add(New SqlClient.SqlParameter("@param2", paramValue2)) End With What are the other ways to execute the parameterized stored procedures within a loop in ASP.NET?
We are seeing a regression bug with the Microsoft JDBC driver 1.2 CTP.
Using this driver, we don't seem to be able to call stored procedures which return a result set, if those stored procedures use temporary tables internally.
The 1.2 CTP driver fails to access such stored procedures in both SQL Server 2000 and SQL Server 2005 databases. The previous 1.1 driver, suceeds in both cases.
Here is a test case which demonstrates the problem (with IP addresses and logins omitted). The prDummy stored procedure being called is quite simple, and I've copied it below:
Code Snippet
public class MicrosoftJDBCDriverCallingStoredProceduresTest extends TestCase {
// CREATE PROCEDURE [dbo].[prDummy] // AS // // CREATE TABLE #MyTempTable ( // someid BIGINT NOT NULL PRIMARY KEY, // userid BIGINT, // ) // // SELECT 1 as TEST2, 2 as TEST2 // GO
public void testStoredProcedureViaDirectJDBC() { Connection conn = null; String driverInfo = "<unknown>"; String dbInfo = "<unknown>"; try { // Set up driver & DB login... Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); String connectionUrl = "jdbc:sqlserver://xxx.xxx.xxx.xxx:1433"; Properties dbProps = new Properties(); dbProps.put("databaseName", "xxxxxx"); dbProps.put("user", "xxxxxx"); dbProps.put("password", "xxxxxx"); // Get a connection... conn = DriverManager.getConnection(connectionUrl, dbProps); driverInfo = conn.getMetaData().getDriverName() + " v" + conn.getMetaData().getDriverVersion(); dbInfo = conn.getMetaData().getDatabaseProductName() + " v" + conn.getMetaData().getDatabaseProductVersion(); // Perform the test... CallableStatement cs = conn.prepareCall("{CALL prDummy()}"); cs.executeQuery(); // If the previous line executes okay, the test is passed... System.out.println("Accessing "" + dbInfo + "" with driver "" + driverInfo + "" calls the stored procedure successfully."); } catch (Exception e) { // Fail the unit test... fail("Accessing "" + dbInfo + "" with driver "" + driverInfo + "" fails to call the stored procedure: " + e.getMessage()); } finally { // Close the connection... try { if (conn != null) conn.close(); } catch (Exception ignore) { } } } } The output of this test under both drivers and accessing both databases is as follows:
Code Snippet
Accessing "Microsoft SQL Server v8.00.2039" with driver "Microsoft SQL Server 2005 JDBC Driver v1.1.1501.101" calls the stored procedure successfully.
Accessing "Microsoft SQL Server v9.00.3042" with driver "Microsoft SQL Server 2005 JDBC Driver v1.1.1501.101" calls the stored procedure successfully.
Accessing "Microsoft SQL Server v8.00.2039" with driver "Microsoft SQL Server 2005 JDBC Driver v1.2.2323.101" fails to call the stored procedure: The statement did not return a result set.
Accessing "Microsoft SQL Server v9.00.3042" with driver "Microsoft SQL Server 2005 JDBC Driver v1.2.2323.101" fails to call the stored procedure: The statement did not return a result set.
Hi... I have data that i am getting through a dbf file. and i am dumping that data to a sql server... and then taking the data from the sql server after scrubing it i put it into the production database.. right my stored procedure handles a single plan only... but now there may be two or more plans together in the same sql server database which i need to scrub and then update that particular plan already exists or inserts if they dont...
this is my sproc... ALTER PROCEDURE [dbo].[usp_Import_Plan] @ClientId int, @UserId int = NULL, @HistoryId int, @ShowStatus bit = 0-- Indicates whether status messages should be returned during the import.
AS
SET NOCOUNT ON
DECLARE @Count int, @Sproc varchar(50), @Status varchar(200), @TotalCount int
SET @Sproc = OBJECT_NAME(@@ProcId)
SET @Status = 'Updating plan information in Plan table.' UPDATE Statements..Plan SET PlanName = PlanName1, Description = PlanName2 FROM Statements..Plan cp JOIN ( SELECT DISTINCT PlanId, PlanName1, PlanName2 FROM Census ) c ON cp.CPlanId = c.PlanId WHERE cp.ClientId = @ClientId AND ( IsNull(cp.PlanName,'') <> IsNull(c.PlanName1,'') OR IsNull(cp.Description,'') <> IsNull(c.PlanName2,'') )
SET @Count = @@ROWCOUNT IF @Count > 0 BEGIN SET @Status = 'Updated ' + Cast(@Count AS varchar(10)) + ' record(s) in ClientPlan.' END ELSE BEGIN SET @Status = 'No records were updated in Plan.' END
SET @Status = 'Adding plan information to Plan table.' INSERT INTO Statements..Plan ( ClientId, ClientPlanId, UserId, PlanName, Description ) SELECT DISTINCT @ClientId, CPlanId, @UserId, PlanName1, PlanName2 FROM Census WHERE PlanId NOT IN ( SELECT DISTINCT CPlanId FROM Statements..Plan WHERE ClientId = @ClientId AND ClientPlanId IS NOT NULL )
SET @Count = @@ROWCOUNT IF @Count > 0 BEGIN SET @Status = 'Added ' + Cast(@Count AS varchar(10)) + ' record(s) to Plan.' END ELSE BEGIN SET @Status = 'No information was added Plan.' END
SET NOCOUNT OFF
So how do i do multiple inserts and updates using this stored procedure...
I think this is a very simple question, however, I don't know the answer. What is the difference between a regular Temp table and a Global Temp table? I need to create a temp table within an sp that all users will use. I want the table recreated each time someone accesses the sp, though, because some of the same info may need to be inserted and I don't want any PK errors.
How can I determine when a sproc or table was last used? I suspect that I have many obsolete tables and sprocs in my database but how can I find out for sure?? Thanks, DL
Here's the error: Input string was not in a correct format. 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.FormatException: Input string was not in a correct format.
Source Error:
Line 159: Line 160:objConn.Open() Line 161:objCmd.ExecuteNonQuery() Line 162:objConn.Close() Line 163:
[FormatException: Input string was not in a correct format.] System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) +723 System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +194 managecatalog.SubmitProd(Object s, EventArgs e) in C:FullerAviationSupplymanagecatalog.vb:161 System.Web.UI.WebControls.Button.OnClick(EventArgs e) +108 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +57 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +18 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain() +1277
-------------------------------------------------------------------------------- Version Information: Microsoft .NET Framework Version:1.1.4322.573; ASP.NET Version:1.1.4322.573
Here's the code from my vb file:
Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init 'CODEGEN: This method call is required by the Web Form Designer 'Do not modify it using the code editor. InitializeComponent() End Sub
#End Region
Protected WithEvents dgProductCatalog As datagrid Protected WithEvents tbxProductName As TextBox Protected WithEvents tbxInventory As TextBox Protected WithEvents dgCatProd_Active As DropDownList Protected WithEvents dlvendor As DropDownList Protected WithEvents dlSubcat As DropDownList Protected WithEvents btnLinkvend As Button Protected WithEvents tbxshortdesc As TextBox Protected WithEvents tbxlongdesc As TextBox Protected WithEvents qtyPA As TextBox Protected WithEvents pricePA As TextBox Protected WithEvents qtyPB As TextBox Protected WithEvents pricePB As TextBox Protected WithEvents qtyPC As TextBox Protected WithEvents pricePC As TextBox Protected WithEvents sizeX As TextBox Protected WithEvents dlX As DropDownList Protected WithEvents sizeY As TextBox Protected WithEvents dlY As DropDownList Protected WithEvents sizeZ As TextBox Protected WithEvents dlZ As DropDownList Protected WithEvents weight As TextBox Protected WithEvents dlweight As DropDownList Protected WithEvents dgInsertProduct As Button Protected WithEvents thumbfile As TextBox Protected WithEvents mainfile As TextBox
Dim objDA As SqlDataAdapter Dim objDS As New DataSet() Dim objDX As New DataSet() Dim objDV As New DataSet() Dim objDU As New DAtaSet() Dim objDW As New DAtaSet() Dim objDT As DataTable Dim objDR As DataRow Dim objConn As SqlConnection = New SqlConnection(ConStr)
I have a bunch of un-related tables that I need to make relational.
I have a bunch of SProcs set up to help with the relating of the tables but I need the quickest way to get the values from the tables and into the SProcs.
Currently, I'm using .NET to cycle through the necessary tables, sending in their values into the SProcs, one at a time.
I'd like to have some SQL-ized way to do this so I can just make another step in my DTS package that already copies over the un-related tables from DB2.
What I'd imagine the code to look like would be something like...
EXEC SP_EMPLOYEES (SELECT FIRST_NAME,LAST_NAME FROM oldEmployees)
I know, now, that it's not as easy as that but I'm thinking there MUST be a way - even if it requires creating a new SProc. TIA
This Table is updated as users rate forum threads.
Then I have Table_B which should display the some info in a Gridview Table_B has 3 fields: Name, ID and Score
I want the Rating from Table_A to update to Table_B's Score Field.
I suppose I should write a sproc to do this, but I am not quite sure how; This is what I would like the sproc to do...
Update Table_B SET Score = Table_A.Rating WHERE ID = Table_A.ThreadID
Any help on this...or is there a should I not use a sproc. Also...how do I get the sproc to update the table automatically (without me running the sproc manually)?
My goal is to recreate a table daily so that the data is updated. This could be a bad decision performance-wise, but I felt this was simpler than running a daily update statement. I created a stored procedure:SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO
CREATE PROCEDURE sp_CreatetblImprintPhrase
AS
DROP TABLE tblImprintPhrase
GO
CREATE TABLE tblImprintPhrase ( CustID char(12), CustName varchar(40), TranNoRel char(15) ) GO However, I was looking to edit the stored procedure, changing CREATE to ALTER, but when I do so, I am prompted with: Error 170: Line 2: Incorrect syntax near "(". If I change back to CREATE, the error goes away, but the sproc cannot be run because it already exists. Any thoughts?
I have a sproc called usp_validTicker that will take 2 parameters: ticker and date. It will return the valid ticker for that date. I like to have the sproc going through each ticker in the table and return the valid tickers.
For example exec usp_validTicker 'AAB-Bank','2008-6-10' will return 'AAB' and my final table will be
Hullo folks, I'm having what I assume is a fairly mundane security issue.
I have a SQL login that I am trying to restrict as much as possible. This account's sole goal in life is to hit the server, return some usage statistics, then truncate the table it received the statistics from. I would like to refrain from granting this login permissions on the physical target table if possible.
Usually I can wrap up "protected" operations in a stored procedure, then grant exec permissions for my user and I'm good to go. However, TRUNCATE TABLE gets cranky with me when I attempt the above method. I suspect that has to do with the fact that TRUNCATE TABLE is effectively modifying the table itself as opposed to merely deleting data.
Is it possible to grant this login ONLY execute permission on a stored proc that TRUNCATE's tables without giving the user any physical permissions? Am I going about this the wrong way?
I'm trying to capture the value returned from sprocs. I stored the sproc name in the table and use cursor to run each sproc. Now the question is how can I capture and store the return value in a variable?
Here is the scenario:
Table1 has 1 column varchar(50) called vchsprocname count_A -- procedure, select count(*) from ... count_B -- procedure, select count(*) from ... count_C -- procedure, select count(*) from ...
here is my query: ---------------------------------------------------- DECLARE @vchsprocname varchar(50) DECLARE @count int
DECLARE cur CURSOR FOR SELECT vchsprocname from table1
OPEN cur FETCH NEXT FROM cur into @vchsprocname
WHILE @@FETCH_STATUS = 0 BEGIN
exec @count = @vchsprocname -- I know I cannot do this, the vchsprocname cannot be variable. What else can I do?
i am writing a sproc that calls another sproc. this 2nd sproc returns 3 or 4 rows. i want to be able to insert these rows into a table. this sproc is inside a cursor, as i have to call it many times.
how do i insert the rows that it returns into another table??
