Stored procedure retrieves a single row from a single table... Based on the specific values in 4 different columns, different branch actions are taken using 4 nested IF statements.
The question is, what is more efficient: storing column values in 4 variables and then evaluting each of them, or executing the same query 4 times?
Scenario A:
DECLARE @var1 char(20), @var2 char(20), @var3 char(20), @var4 char(20) SELECT @var1 = col1, @var2 = col2, @var3 = col3, @var4 = col4 FROM theTable WHERE rid = 12345 IF @var1 = 1 ... ELSE IF @var2 = 2 ... ELSE IF @var3 = 3 ... etc. ---------------
Scenario B:
IF (SELECT col1 FROM theTable WHERE rid = 12345) = 1 ... ELSE IF (SELECT col2 FROM theTable WHERE rid = 12345) = 2 ... ELSE IF (SELECT col3 FROM theTable WHERE rid = 12345) = 3 ... etc. --------
We have SELECT INTO queries that use the IDENTITY function to assign a sequential row number to a result set based upon a sort order. This has been helpful in SQL Server 2000 for median determination. It appears, however, that in SQL Server 2005, the row numbers are not assigned sequentially, or maybe they are assigned before the sort order is applied.
Can anyone verify whether the IDENTITY function has changed behavior between 2000 and 2005? We would prefer not to have to make changes to existing queries. Thank you.
(BTW, the workaround we found so far is to put the initial SELECT...ORDER BY in a subquery, but then we had to include a phrase like TOP 10000000 to pass syntax check. Is there a better way?)
Hello all,I'm trying to request a number of URLS (one for each user) from my database, then place each of these results into a separate string variables. I believed that SqlDataReader could do this for me, but I am unsure of how to accomplish this, or if I am walking down the wrong road. The current code is below (the section in question is in bold), please ignore the fact that I'm using MySQL as the commands work in the same way. public partial class main : System.Web.UI.Page{ String UserName; String userId; String HiveConnectionString; String Current_Location; ArrayList Location; public String Location1; public String Location2; public String Location3; //Int32 x = 0; private void Page_Load(object sender, EventArgs e) { if (User.Identity.IsAuthenticated) { UserName = Membership.GetUser().ToString(); userId = Membership.GetUser().ProviderUserKey.ToString(); HiveConnectionString = "Database=hive;Data Source=localhost;User Id=hive_admin;Password=West7647"; using (MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection(HiveConnectionString)) { // Map Updates MySql.Data.MySqlClient.MySqlCommand Locationcmd = new MySql.Data.MySqlClient.MySqlCommand( "SELECT Location FROM tracker WHERE Location = IsOnline = '1'"); Locationcmd.Parameters.Add("?PKID", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255).Value = userId; Locationcmd.Connection = conn; conn.Open(); MySql.Data.MySqlClient.MySqlDataReader LocationReader = Locationcmd.ExecuteReader(); while (LocationReader.Read()) { Location1 = LocationReader.GetString(0); //Location2 = LocationReader.GetString(1); // This does not work.. } LocationReader.Close(); conn.Close(); // IP Display MySql.Data.MySqlClient.MySqlCommand Checkcmd = new MySql.Data.MySqlClient.MySqlCommand( "SELECT UserName FROM tracker WHERE PKID = ?PKID"); Checkcmd.Parameters.Add("?PKID", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255).Value = userId; Checkcmd.Connection = conn; conn.Open(); object UserExists = Checkcmd.ExecuteScalar(); conn.Close(); if(UserExists == null) { MySql.Data.MySqlClient.MySqlCommand Insertcmd = new MySql.Data.MySqlClient.MySqlCommand( "INSERT INTO tracker (PKID, UserName, IpAddress, IsOnline) VALUES (?PKID, ?Username, ?IpAddress, 1)"); Insertcmd.Parameters.Add("?IpAddress", MySql.Data.MySqlClient.MySqlDbType.VarChar, 15).Value = Request.UserHostAddress; Insertcmd.Parameters.Add("?Username", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255).Value = UserName; Insertcmd.Parameters.Add("?PKID", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255).Value = userId; Insertcmd.Connection = conn; conn.Open(); Insertcmd.ExecuteNonQuery(); conn.Close(); } else { MySql.Data.MySqlClient.MySqlCommand Updatecmd = new MySql.Data.MySqlClient.MySqlCommand( "UPDATE tracker SET IpAddress = ?IpAddress, IsOnline = '1' WHERE UserName = ?Username AND PKID = ?PKID"); Updatecmd.Parameters.Add("?IpAddress", MySql.Data.MySqlClient.MySqlDbType.VarChar, 15).Value = Request.UserHostAddress; Updatecmd.Parameters.Add("?Username", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255).Value = UserName; Updatecmd.Parameters.Add("?PKID", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255).Value = userId; Updatecmd.Connection = conn; conn.Open(); Updatecmd.ExecuteNonQuery(); conn.Close(); } } } } Can anyone advise me on what I should be doing (even if its just a "you should be using this command) if this is not correct? In fact any pointers would be nice !Thanks everyone!
I was wondered what the most efficient way to do the following in MS SQL (2000, 2005, and above): Psudo Code: DECLARE @var1, @var2, @var3 VARCHAR(100) SET @var1, @var2, @var3 = (SELECT var1, var2, var3 FROM Table WHERE [ID] = @someID) <-- Returns only one row Right now I'm making a call for every variable. I should play around with temp tables but seems like allot of overhead code just to get 3 values. I'm thinking there's a simple way to do this. Thanks for any ideas
It's come up more than once for me, where I need to DECLARE and SET several SQL variables in a Stored Procedure where many of these values come from the same table record - what is the best method for doing this, where I don't have to resort to making a separate query for each value. Currently I'll do something like this: DECLARE @var1 intSET @var1 = (SELECT TOP 1 field1 FROM table1 WHERE recordkey = @somekey)DECLARE @var2 nvarchar(20)SET @var2 = (SELECT TOP 1 field2 FROM table1 WHERE recordkey = @somekey) Of course, I'd rather just have to query "table1" just once to assign my variables. What obvious bit of T-SQL am I missing? Thank you in advance.
I'm having trouble with a script component in which I'm trying to use two ReadOnlyVariables. If I use only one of the two variables, everything works without issue. If I use both of the variables (as part of a comma-delimited list) I get the following:
The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.
I don't believe the variables themselves are the problem. Both are scoped to the package level and I can use either of them if I have it as the only variable. Seems bug-like, but thought I'd get some ideas before pursuing that route.
Pretty simple question and I see it's been asked a few times with no answers so I thought I'd try again. :) Is there a way to put a variable into an SQL connection string? Like: <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:BGHelpdeskConnectionString %>" OldValuesParameterFormatString="original_{0}" SelectCommand="SELECT * FROM MyTable WHERE name=" & response.write(MyName) .... etc
I'd like to be able to call different packages from a control flow. These packages will have different requirements for parameters therefore I'd like to create them dynamically.
Is this possible? Can I do it using a script task?
I was wondering if it is possible to assign values to multiple variables from within the same execute sql task, ie I want to use only one execute sql task and have multiple T-SQL statements within it and then assign the results of these sql statemenst as values to multiple variable.
Typically I would declare variables var1 , var2 and var3 , then can I just add one execute sql task and have mutiple sql statements within it? something like this
Hi I'm a n00b at ASP.Net and C# but the company I work for requires me to know this and develop their website using these languages. Anyway I've been doing pretty good so far I got the page looking as it should etc and databases all set up, now I've hit my first real problem... I am using Visual Studio 2005 and in the Web Developer section. I want to make a SELECT query using the <asp:LoginName> control as the where clause. e.g. if admin logs in there is a welcome message : Welcome Admin now I want to use the 'Admin' <asp:LoginName> as the SELECT in my query i.e. SELECT * FROM tbla WHERE ([fldCustomerCode] = 'LoginName' ) I was just really wondering if that is in the .aspx file or the aspx.cs code behind file? Thanks for any help given
Is there a way to write an insert where the values you're inserting depend on another value in the row. Here is an example of pseudo-code that hopefully demonstrates what I'm tryihg to do:
field1, field2, field3, @k1=(select sm from tbl3 where mm=field1), @k2=(select sm from tbl3 where mm=@k1), @k3=(select sm from tbl3 where mm=@k2) from
tbl1 where
blah= blahblah and bldfddf = dfaljk
My main focus is using the variables @k1 - @k2 in the where part of subsequent subqueries in the select statement. I haven't been able to get this to work, but I thought maybe something like it would work. I'd like to do this in one statement rather than using a cursor or loop. I'm also trying to avoid functions. Any help would be appreciated.
I have problems to place my variable into the select statement.
DECLARE @DB_NAME varchar(64) DECLARE MR_ReqPro_DB_cursor CURSOR FOR select name from dbo.sysdatabases where name like '%MR_req%' OPEN MR_ReqPro_DB_cursor FETCH NEXT FROM MR_ReqPro_DB_cursor INTO @DB_NAME
WHILE @@FETCH_STATUS = 0 BEGIN print @DB_NAME; --works fine
Select NAME, FILEDIRECTORY FROM @DB_NAME.MR_ReqPro.RQDOCUMENTS WHERE (FILEDIRECTORY LIKE '%\%');
FETCH NEXT FROM MR_ReqPro_DB_cursor INTO @DB_NAME END CLOSE MR_ReqPro_DB_cursor DEALLOCATE MR_ReqPro_DB_cursor
GO
How could i use a variable like @DB_Name in my select ?
Hi, figured out where I was going wrong in my post just prior, but isthere ANY way I can assign several variables to then use them in anUpdate statement, for example (this does not work):ALTER PROCEDURE dbo.UpdateXmlWF(@varWO varchar(50))ASDECLARE @varCust VARCHAR(50)SELECT @varCust = (SELECT Customer FROM tblWorkOrdersWHERE WorkOrder=@varWO)DECLARE @varAssy VARCHAR(50)SELECT @varAssy=(SELECT Assy FROM tblWorkOrdersWHERE WorkOrder=@varWO)UPDATE statement here using declared variables...I can set one @variable but not multiple. Any clues? kinda new tothis.Thanks,Kathy
Hi I am trying to include a string variable in a Select Statement. My problem is that when I code with the user name hard coated in the SQL Statement it works fine (see below:) cmd.CommandText = "SELECT UserPswd, StudioID, StudioCode FROM Users WHERE UserName = 'jdoe' " But when I try to use the String variable I get an error (See below): cmd.CommandText = "SELECT UserPswd, StudioID, StudioCode FROM Users WHERE UserName = " & StrUserName I know there must be something wrong with my syntax ?? Thanks Jackson
I've tried declaring and setting variables in my sql statement and then trying to use them instead of defining a column directly - sorry quite hard to explain, i'll do a simple example
eg
DECLARE @column DECLARE @value SET @column = 'col1' SET @value = 'bloggs'
Select * FROM table1 WHERE @column = @value
It keeps returning no results even though i've tried
Select * FROM table1 WHERE col1 = 'bloggs' -- which returns results
I realise its the column which is not being selected, but there must be a way by using a variable?
I have a MSSQL2000 table called partspec.dbo.locationIn this table are 2 smallint columns: Tester (numbered 1-40) and Line (numbered with various integers)I am trying to create a stored procedure to read the tester number like so:Select Tester from partspec.dbo.location where Line = 2which will return up to 5 tester number results, lets say 11, 12, 24, 29 ,34My question is how do I store these tester numbers into 5 variables so that I may use them later in the sp ? So it would go something like this:CREATE PROCEDURE Table_Line (@Tester1 integer,@Tester2 integer,@Tester3 integer,@Tester4 integer,@Tester5 integer)ASSELECT Tester FROM partspec.dbo.location where Line = 2Now this is where I'm confused on how to get 1 value into 1 variable and so on for all 5 values returned. This is what I would like to happen: @Tester1 = 11@Tester2 = 12@Tester3 = 24@Tester4 = 29@Tester5 = 34GOThank you for any and all assistance.
I think I'm just braindead or simply thick...since this shouldn't be that hard, but I'm stumped right now.
So, I'm trying to retrieve from a table, with a sql stored procedure with the sql like "select height, width, depth from products where id=@idinput"
OK, so this part is easy, but if I wanted to say, return this to my code and assign height to a variable Ht, width to Wd and depth to Dp, how could I do that?
I'm getting the error "No recepient is specified".
I have set up a dataflow from a select statement into a record set, then have that dataflow point to a ForEach group with a mail task in it. I have set up variables for the username and subjectline. So in the mail task I have no value in the To: line because I specify an "http://www.sqlis.com/59.aspx">http://www.sqlis.com/59.aspx exactly.
I couldn't figure out how to included screen shots.
I have a problem where my users complain that a select statement takes too long, at 90 seconds, to read 120 records out of a database. The select statement reads from 9 tables three of which contain 1000000 records, the others contain between 100 and 250000 records. I have checked that each column in the joins are indexed - they are (but some of them are clustered indexes, not unclustered). I have run the SQL Profiler trace from the run of the query through the "Database Engine Tuning Advisor". That just suggested two statistics items which I added (no benefit) and two indexes for tables that are not involved at all in the query (I didn't add these). I also ran the query through the Query window in SSMS with "Include Actual Execution Plan" enabled. This showed that all the execution time was being taken up by searches of the clustered indexes. I have tried running the select with just three tables involved, and it completes fast. I added a fourth and it took 7 seconds. However there was no WHERE clause for the fourth table, so I got a cartesian product which might have explained the problem. So my question is: Is it normal for such a type of read query to take 90 seconds to complete? Is there anything I could do to speed it up. Any other thoughts? Thanks
I am moving some code that was created in visual studio 2002 into Visual Web Developer 2005 express edition and am getting a warning on some of my code before compilation. The warning is "Variable lSqlTransaction is used before it has been assigned a value. A null reference exception could result at runtime". I experienced no problems in vs2002. The offending line is the rollback command within the exception. If I move it out of the exception it no longer flags the warning, if I move it after the throw statement it now longer flags the warning. Any help appreciated. Heres the code Public Shared Sub ExecuteNonQuery(ByVal lSQLCommand As SqlCommand)
Dim lSQLConnection As New SqlConnection(SQLConnString(enmSQLDB.enmSQLDB_TPSDB)) Dim lSQLTransaction As SqlTransaction
Try '----------------------------------------------------- 'Try and open the database '----------------------------------------------------- lSQLConnection.Open()
'----------------------------------------------------- 'Create a transaction '----------------------------------------------------- lSQLTransaction = lSQLConnection.BeginTransaction
'----------------------------------------------------- 'Assign the connection and transaction to our object '----------------------------------------------------- lSQLCommand.Connection = lSQLConnection lSQLCommand.Transaction = lSQLTransaction
'----------------------------------------------------- 'Execute the stored proc '----------------------------------------------------- lSQLCommand.ExecuteNonQuery()
'----------------------------------------------------- 'Use the transaction to commit the changes '----------------------------------------------------- lSQLTransaction.Commit()
Catch ex As Exception '----------------------------------------------------- 'If any errors have been thrown then roll back without 'commiting.... '----------------------------------------------------- lSQLTransaction.Rollback()
Throw New Exception("An error has occured whilst trying to update the database. " & _ "Your changes have not been saved.", ex)
Finally If lSQLConnection.State = ConnectionState.Open Then lSQLConnection.Close() End If End Try
I'm tryin to copy information from a fact table from one server to another server but when try using a DTS package to do this, an error pops up that says "the Filegroup <filegroup_name> has no files assigned to it."
We have a standard audit trigger on one of our tables. The trigger type is "after insert, update". The trigger populates four table columns, telling us the login and time the row was created and last updated.
We use replication to synchronize three servers. The trigger specifies "not for replication" on all servers.
The code used to grab the identity and time of the last update is shown below.
LastEditedDate = GETUTCDATE(),
LastEditedBy = SUSER_SNAME()
What I observe is that the LastEditedBy value is sometimes different for the same row on different servers even though the time value is identical. I don't know how to explain this. It looks like the login value is being recalculated during replication while the edit time is not.
For the record, the correct login will be something like "MYDOMjoe" while the incorrect value on another server will read "NT AUTHORITYNETWORK SERVICE". Do these symptoms ring a bell with anyone?
I have a problem with datatype mismatches. In my Data Flow I am reading a Flat File that includes a DateOfBirth which SSIS correctly assigns a datatype of [DT_DATE]. I need to check to see if the record has previously been submitted, so I do a Lookup that uses a SQL statement that combines fields (LastName, Zip & BirthDate) from three different tables. My problem is that SSIS assigns a datatype of [DT_DBTIMESTAMP] to BirthDate, which I can't use because of the mismatch. I have tried every form of re-formating the field in SQL that I can think of, but it still gets assigned that DT. And I'm unaware of a way of changing the DT once SSIS assigns it in the Lookup task.
I need to update a table using a stored procedure that "UPSERTS". In this case, @OfficeId is passed with ParameterDirection.OutputSo when I update I assign the value to OfficeId like this (the office id value is being correctly assigned): SqlParameter OfficeIdParam = new SqlParameter("@OfficeId", SqlDbType.Int, 4); if (_officeId > 0) { OfficeIdParam.Value = _officeId; } OfficeIdParam.Direction = ParameterDirection.Output; The following segment of the stored procedure is throwing an error that the OfficeId does not exist: Update --- @OfficeId int output if not exists (select 1 from ORG_Office where OfficeId=@OfficeId) begin RAISERROR ('OfficeId does not exist in ORG_Office: E002', 16, 1) -- OfficeId does not exist. return -1 end May this approach work, and if the operation turns out to be an Insert I may do this:select @error_code = @@ERROR, @OfficeId= scope_identity() Thank you,jspurlin
I have a report where certain columns have values that get repeated, but the client has a rigid requirement for not wanting these columns as groups in the reports (other programs and exports doing specific tasks with those values). In these reports, the "hide duplicates" value is checked, so as to give an aesthetic sense of grouping.
The data for the report, in raw form is:
Value 1 A
Value 1 B
Value 1 C
Value 2 D
Value 2 E
Value 2 F
Value 3 G
Value 3 Hetc...
In its aesthetic form, it is:
Value 1 A
B
C
Value 2 D
E
F
Value 3 G
H
My question is, how can I use RunningValue() to give me the following row numbers based upon those repeated values acting as grouping, since I'm not using formal grouping in the report design?
This seems like a simple question but I still haven't been able to get an answer from Microsoft Canada. We're looking at buying 75 device cals to connect to a single instance of SQL Server Standard Edition. Our question is on how those licenses are assigned and/or activated.
Do we need to manually do something for each station or is it an automated process done the first time a station connects to the server? We also wat to understand what happens when a workstation dies and is replaced by another one. Do you have to do something on the server to transfer the license to the new station ?
In other words, when using device cals (and/or user cals), do you have to do any type of license management or is it all transparent ?
