Question With IF..ELSE And Assignments In Transact SQL
Dec 22, 2004
I really need help with the following. I have a stored procedure that assigns values to declared variables and I need to make sure that I'm doing it right. It uses a lot of IF..ELSE statements which adds to the complexity, but here is a sample of code...
Code:
DECLARE @Code Int, @Weights Float
SELECT somestuff , @Code, @Weights
IF(something )
BEGIN
IF(something )
BEGIN
SELECT @Code = 2
SELECT @Weights = A*B
END
END
FROM a bunch of tables
WHERE a bunch of stuff
Now the stored procedure is way more complicated than above, I just want to make sure I can assigne values to the declared variables the way I have done in the example above. Currently I get an error on the FROM line of the stored procedure.
Any help on this would be greately appreciated 'cause I have several people telling different things in regards to assigning variables in Transact-SQL. Thanks in advance
From the Report Manager home page, if you click on the Properties tab, you get to a security page where you can add new role assignments for users or groups. We need to add role assignments programmatically (not manually using Report Manager).
How can we programmatically add new role assignments?
I have a table of employee assignments that I'm narrowing down to a specific group. Employees have multiple assignments (job positions essentially) and each has start and end dates. I need to identify the date range(s) that are covered by the assignments in my data set. Here's an example of data for one person ID. NULL in the end_date just means it is an active assignment. The pos_id column isn't necessary, but it define the data I'm looking at. The end result won't use it.
In this case I want results to say that ID 999 has a range from 9/2/2011 to NULL. There are no gaps in the date range. Or to say it differently, there's always an assignment starting the next day after an end_date. Or an assignment that overlaps the end and beginning of another assignment.
Here's another example where there is a gap in the ranges.
There would be 2 result rows for this with a range from 2011-09-02 to 2013-01-06 and a second for 2013-09-01 to NULL.
The end result would be to have a row per date range for every ID. I've written a script that will find the ranges but it is a painful RBAR solution that takes forever to run. Every different path I've gone down trying to solve it ends in evaluating row by row grouped by the Person ID. The assignments are not always continuous, so I can't use a MAX and MIN and there may be 1 or more gaps in the dates to account for.
I have the following SELECT statement. It returns the first record from EVENT_TEACHERS. I want the last record. 'iID' is the unique identifier so I want to select this stuff where iID is max. How can I add this to my string? I can't get the block out of my head of wanting to put the aggregate in the WHERE clause. str = "SELECT vwStaffList4Assessment.*, EVENT_TEACHERS.iID, EVENT_TEACHERS.iAsmtID, ASMT.* FROM EVENT_TEACHERS INNER JOIN ASMT ON EVENT_TEACHERS.iAsmtID = ASMT.iAsmtID INNER JOIN vwStaffList4Assessment ON ASMT.DVN=vwStaffList4Assessment.DVN" Thanks
I am trying to insert to our DB. Here is my insert statement. str = "INSERT INTO SURVEY (q3) VALUES (" & q3 & ") WHERE iID="&iID Here is my debug: DEBUG: INSERT INTO SURVEY (q3) VALUES (1) WHERE iID=23 And here is my error: Incorrect syntax near the keyword 'WHERE'. Can someone please tell me what I am doing wrong? Thanks in advance.
I am new to SQL and need some help. Would anyone be able to tell how to write a statmement, that would bring back several columns with specific amounts of spaces in it? I don't want to change the sizes of the columns forever, just for this one query.
I START A NEW JOB WITH A COMPANY PROVIDES IT SUPPORT TO CALL CENTERS. WE USE VB6 CONNECTING VIA ADO TO SQL SERVER 2000 STORED PROCEDURES. OUR ONLINE REPORTING TOOL USES CRYSTAL REPORTS & ASP (LITTLE VB6). CAN ANYONE RECOMMEND GOOD BOOKS AND WEBSITES TO PREPARE ME FOR MY NEW POSITION WITH THIS COMPANY?
ALSO, HOW DOES ONE GO ABOUT BUYING RELEVANT CODE IN VB6, TRANSACT SQL, ASP?
WITH CALL CENTERS (OUR CLIENTS) MOVING OVERSEAS-OUTSOURCING, WHAT ABOUT ENRICHING OUR POTENTIAL CLIENT BASE...FOR EXAMPLE, RECOMMENDING MARKETING EFFORTS TO 'NAILED DOWN' CLIENTS LIKE PHARMACEUTICAL COMPANIES WHERE THE GOVERNMENT DEMANDS SUCH WORK BE DONE IN AMERICA. OR EXPANDING OUR ROLE TO ALSO DO...?
ANY IDEAS TO ABOVE THREE ISSUES PLEASE-PLEASE EMAIL mikelynn@comcast.net
Hi: Can any one please tell me the difference b/w TSQL and simple SQL. Also there is a a TSQL use in my VB6 Program, how to i find that SQL in SQL Server? cnADOSQL.Execute "DBCC CHECKIDENT ('tbl_distlist_balrange', RESEED, 0)"
I am trying to use LIKE in a statement but I can't seem to pull all the rows for Age of Empire. I have at least 50 rows with the word Age of Empire but I only get two rows. What is the best way to use LIKE in the statement below so that it pulls all rows with the words Age of Empire?
Select * from dbo.proddesc where codeabbreviation Like '%Age of empire%'
Hi Im reading about active and temporal databases. I have a quesiton though. In which category does SQL server falls into? Also What is the difference between Transact SQL and TSQL2?
I am trying to tally up information from two tables, but I am runningin to trouble and I can't seem to figure this out. I have aCreditsTable and a DebitsTable and I am trying to create a querry thatsums up the info from each per UserID and returns TotalCredits,TotalDebits, and a Ballance.CreditsTableUserID Ammount Applied+----------+----------+----------+| 192 | 1 | True || 192 | 2 | True || 207 | 1 | True || 207 | 1 | True || 207 | 2 | True || 212 | 3 | True |DebitsTableUserID Ammount Applied+----------+----------+----------+| 192 | 1 | True || 207 | 1 | True || 207 | 1 | True |***This is the Function I have tried, but it doesn't return the correctresultsALTER FUNCTION [dbo].[BallanceTotals]()RETURNS TABLEASRETURN(SELECT DISTINCTdbo.CreditsTable.UserID, SUM(dbo.CreditsTable.Ammount) AS TotalCredits,SUM(dbo.DebitsTable.Ammount) AS TotalDebits,SUM(dbo.CreditsTable.Ammount - dbo.DebitsTable.Ammount) AS BallanceFROMdbo.CreditsTable FULL OUTER JOINdbo.DebitsTable ON dbo.CreditsTable.UserID = dbo.DebitsTable.UserIDWHERE(dbo.CreditsTable.Applied = 1) OR (dbo.DebitsTable.Applied = 1)GROUP BYdbo.CreditsTable.UserID)*** This is what it returns, it is not adding things up correctly (itlooks like it is adding NULL values as 1 instead of 0 or something)BallanceTotalsTotal TotalUserID Credits Debits Ballance+----------+----------+----------+----------+| 192 | 3 | 2 | 1 || 207 | 4 | 3 | 1 || 212 | 3 | | |*** This is what I want it to return!BallanceTotalsTotal TotalUserID Credits Debits Ballance+----------+----------+----------+----------+| 192 | 3 | 1 | 2 || 207 | 4 | 2 | 2 || 212 | 3 | 0 | 3 |I would really appreciate some help in getting this to work correctly!-Daniel
Why is my where skewing data? This 1st statement shows everything perfectly, but this go around Ineed to add one more stipulation and adding in the where returns 0 results. But if I run a simple select with the where added, I get the intended result set.
--works with cte as(select *, row_number() over(partition by time1, time2, userID order by userID ) as rn from timetable) delete from cte where rn > 1
--no work with cte as(select *, row_number() over(partition by time1, time2, userID order by userID ) as rn from timetable where timeadded > 0) delete from cte where rn > 1
Below is my SQL code and after that output i get. For result i would like to get SUM of column Expr1 (which is 60.668,66) so that means only 1 row not 5. I know it's not that hard but i've been searching around and trying but no luck...
SELECT INVENTTABLE_1.ITEMID AS MA, INVENTDIM_1.INVENTLOCATIONID, SUM(INVENTSUM_1.PHYSICALINVENT) AS Expr1 FROM dbo.INVENTDIM AS INVENTDIM_1 INNER JOIN dbo.INVENTSUM AS INVENTSUM_1 ON INVENTDIM_1.INVENTDIMID = INVENTSUM_1.INVENTDIMID INNER JOIN dbo.ECORESPRODUCTTRANSLATION AS ECORESPRODUCTTRANSLATION_1 INNER JOIN dbo.INVENTTABLE AS INVENTTABLE_1 ON ECORESPRODUCTTRANSLATION_1.PRODUCT = INVENTTABLE_1.PRODUCT ON
I have 3 tables in SQL 2005. I have dumbed them down significantly, to illustrate my question.
1) Item Table - Contains a list of 2 items, "Coca-Cola" and "Pepsi-Cola". 2) Market Share - Contains the 'market share' information for the items in "Item Table". 1 to 1 relationship. 3) Retail Price - Contains the 'average retail' price data for the items in "Item Table". 1 to 1 relationship.
All three tables are linked by "iItemID".
I am trying to build a simply query that generates the following results:
Where the first two values for Market Share are repeated again for row 3 and 4.
How do I syntax my SQL statement to return a single data set in the following fashion? ---------------------------------------------------------- Item Name Market Share Retail Price ----------------------------------------------------------- Coca-Cola mm.mm% $xx.xx Pepsi-Cola mm.mm% $xx.xx
I know this is simple for someone who write queries all the time. But this is driving me crazy. I have tried various JOINS (LEFT OUTER, RIGHT, etc.), but I am missing something.
I need to return a computed value and have no idea how to do it.
I have a table of dates: Column DataType RowID int Person nvarchar(20) Percent real Date smalldatetime
What I need is the Annual Fiscal Year Average. The fiscal year starts and ends in June. I need it to add all Percent values between the months of June 2001 - June 2002, June 2002 - June 2003, and so on and on....
A result set like this...
Person - FiscalYear - FiscalYearResult John D - 2001 - .58 John D - 2002 - .52 John D - 2003 - .50 Jane D - 2001 - .58 Jane D - 2002 - .52 Jane D - 2003 - .50
so on and so on...
How do I write this in Transact-SQL to get this result set...
Currently, I am running the code in an Access/VBA module through an ODBC connection, but I would like to be able to run all my code directly on the server.
Thanks in advance for any help you might be able to provide.
I came to know one of the better option in Oracle SQL for TOP-N Analysis-To find the top 5 usage accounts, top 10 users, etc. Is there any such way in SQL server ?
Is there any ways of using JOINS ( using keyword INNER & OUTER ) ? Some of the scripts that is generated automatically in Microsoft access with Join condition is not even working in MSSQL server 6.5. Is it true ? Or is there any other way that I can use these scripts in SQL 6.5.
If possible, pls. help me with some sample scripts ?
Hi! I need help! We have 98% transact log full. After run DUMP TRANSACTION WITH TRUNCATE ONLY it still doesn't purge the log. How can we resolve problem.
Ok here is the situation I need a little help with.
I am passing an XML document to a stored procedure. My XML document looks like this: <ROOT> <customer ID="10" Address="1234 Somewhere"></customer> <customer ID="20" Address=""></customer> </ROOT>
Now I use sp_xml_preparedocument and OpenXML to Insert the XML ID and Address attributes into a table in my database. The table has 2 fields (ID int) and (Address varchar(100) nullable)
The result of the insertion is 2 rows in my table with the ID field containing (10 and 20) and the Address field containing (1234 Somewhere and an empty string).
My first question is, why isn't the second row Address field in my database null instead of an empty string since thats what I would like happen. Is it because the XML Address atribute = "".
If that is the case then that should bring up my second question. Since the OpenXML inserts Address="" as an empty string instead of NULL, why doesn't a regular TransactSQL INSERT statement called from ASP does the same thing?? If I lost you here then let me give a quick example. An html web form with a text field (txtAddress). The user leaves the text box empty and submits the form. My ASP takes the value of the form, Request.Form("txtAddress") and passes it to stored proceudre through an ASP parameter object. My stored procedure simply does an INSERT into tablename (ID, Address) VALUES (@passedID, @PassedAddress). Now the outcome in the database is that the Address field is NULL and not an empty string.
That is why im confused. Why arent the OpenXML and the straight-forward insertion yield the same result (either null or empty string). It seems to me that an empty text box is an empty string and not null. ASP's ISNull(txtAddress) agrees and returns false.
Any insight ot help on this issue is greatly appreciated.
What I am trying to do is iterate through a set of records from a table, and setting one field to a common value on each record, and setting another to an incremented value. Here it is:
Table contains columns A, B, and C. A is a unique ID. B is an ID. C is another value.
I want to, for all records where field B = "AAA", Set B to "AAB", and set A on each record to the next unique ID that I get from somewhere else. Here's my SQL Stored Proc:
DECLARE MYCURSOR SCROLL Cursor FOR SELECT A, B FROM MyTable where B = 1 FOR UPDATE.
Loop:
FETCH NEXT INTO @vcA, @vcB FROM MYCURSOR. UPDATE MYTABLE SET A = @nctr, B = "AAB" WHERE CURRENT OF MYCURSOR SELECT @nctr = @nctr + 1 If @@FETCH_STATUS = 0 goto Loop:
I hit an error as soon as I get to the Update - "Cursorfetch: Number of variables declared in the INTO list must match that of selected columns"
Go Figure. I really need an answer on this ASAP! Help!
I have a table named conversion. In this table there is not a field for "first name", but only a field for "last name" and "Contact Name". The "Contact Name" holds the full name (First and Last), and the "Last Name" holds the last name.
In theory I was thinking that I could take the "Last Name" field and subtract the amount of characters + 1 additional (to account for space in between Contact Name Field - FirstName LastName) and then I would have the first name?
I can find the amount of characters in a field name by using this:
SELECT LEN(lastname_column) AS 'Length', lastname_column FROM conv
Here's a fun little puzzle that I thought up based on some rather gnarly problems I faced today. It might brighten up your morning. See if you can figure out what it does and how it does it.
How does it work? When will it fail as written (this example does not fail)? Why will it fail? How can you change it to increae its capacity?-- ptp 20080117 SQL puzzle
DECLARE @iINT , @cVARCHAR(360)
SET @i = 90 SET @c = ''
WHILE 64 < @i BEGIN SET @c = Char(@i) + Space(@i - 64) + @c SET @i = @i - 1 END
1. A duplicate record is defined as a record where PPN_I and CPN_I have identical ID's. My new DB doesn't allow duplicate records. In order to 'strip' out the unwanted records - I need to enforce a couple of data conditions:
I delete both records for this case only - if qty required =1 and a duplicate record has qty required = 0 then both get deleted. In the above example - records 4 & 5 both get deleted. In all other duplicate record scenarios - I want to keep the record with the highest ECN_02 number. So, records 2 & 3 get deleted because they are duplicate (same PPN_I and CPN_I id's) and their ECN_02 is less than the highest value (4506). If no duplicate record exists - no action taken (records 6,7,8).
So, records 4 & 5 - both get deleted. Records 1,2 & 3 become one record (highest ECN_02) records 1 stays - records 2 & 3 deleted.
8 records become 4.
Any thoughts? How do I 'loop thru the table and identify duplicate records and then enforce some kind of rule?
I'm using the Transact-SQL Debugger for the first time here.I go to Object Browser, find my stored procedure, right click andchoose "Debug".The "Debug Procedure" window comes up, and I click on the Executebutton.The Debug environment comes up and automatically executes myprocedure. It doesn't allow me to single step through the code.What am I missing here?All of the "step" buttons, "restart", and "stop debugging" buttonsare dimmed out.Thanks.Ken
Updating the Table . I need to update Two Column .. DEPT and Product .
For a Same EmployeeName DEPT and Product Name should Repeat till Next Value in encountered.
Example For EmployeeName 'A' Dept Value 100 should Repeat till 20 comes, based on Date, because 100 comes before 20 and Project P1 should repeat till P2 comes.
Below is the Sample Source Table.
CREATE TABLE #MYTEMP3 (NAME VARCHAR(50), DEPT INT ,PROJECT VARCHAr(100), ORDERDT DATE) INSERT INTO #MYTEMP3 SELECT 'A',100,'P1', '2015-01-01' UNION ALL SELECT 'A','','', '2015-01-02' UNION ALL SELECT 'A','','', '2015-01-03' UNION ALL
[Code] ....
Looking for UPDATE Query . I do not want to use Common table expression to achieve this .
I need the Last 3 (by Date) process Keys , by Catalog and Process_ID. The ID column is an incrementing identity column unique to this table. Process_ID and Process Key are keys to other tables. I need some tips to get started.
