Select and Display Products Not Sold
SELECT DISTINCT i.description, p.invID FROM Inventory i FULL OUTER JOIN Purchasing p ON I.iD = p.invID --WHERE i.invID
Socks 1 Ties NULL Underwear NULL
With where unblock this T-SQL should show Ties, Underwear but shows nothing.
Where am I going wrong?
See also questions close to this topic
SQL Server function returns question marks instead of real result
I have this function which retrieves first word from String:
CREATE FUNCTION dbo.FIRST_WORD(@value nvarchar(1000)) RETURNS nvarchar(1000) AS BEGIN RETURN CASE CHARINDEX(' ', @value, 1) WHEN 0 THEN @value ELSE SUBSTRING(@value, 1, CHARINDEX(' ', @value, 1) - 1) END END GO
The problem is the data in my table is in
non-ASCIIformat, so when i pass some value to that function, i get question marks instead of a result:
SELECT dbo.FIRST_WORD('ничего не поделаешь')
But if i pass
ASCIIcharacters, for example:
SELECT dbo.FIRST_WORD('hello world')
I tried to add
Nbefore the argument, but it didn't help:
SELECT dbo.FIRST_WORD(N'ничего не поделаешь')
SQL Server loop through a table for every 5 rows
I need to write a stored procedure or table function to return a new data table as a new data source. I wish to loop through the original table for every 5 rows base on the invoice ID column (it's possible not start from 1), the first 5 rows add to the left of the new table and the second 5 rows add to the right of the new table, the third 5 rows to the left and so on.
Thanks in advance!
How to stop showing messages from procedures in SQL Server?
How to stop showing messages from procedure?
Currently, the problem is I have created a stored procedure
Procedure A. It executes another
procedure Bfrom its code, and the
Bprocedure executes yet other procedures C,D,E from its code.
The problem is when the
Aprocedure executes, it shows 4 result sets like 0,1 or another values. But I want to show only the result from procedure A. How can I achieve this? I can't change the other procedures B,C,D,E because they also perform their individual tasks.
If you need clarification please ask.
Create range of ID without cursor
Not sure if this or similar question is asked already but i could not find one.
The Requirement to create range of IDs while the Value is not changed. This schema can be used:
declare @mytable as table(ID int, Val int) insert into @mytable values (1, 1), (2, 1), (3, 1), (4, 2), (5, 2), (6, 2), (7, 2), (8, 1), (9, 1), (10, 1), (11, 4), (12, 4), (13, 4), (14, 4), (15, 4), (16, 5);
And the expected result would be
StartID EndID Val 1 3 1 4 7 2 8 10 1 11 15 4 16 16 5
Now I can achieve this by running cursor and If n case the number of records will be millions, I think, cursor will be slower. I hope it can be written using some compound query but could not figure-out how.
So I need help in writing that kind of query and needless to mention yet, it is not a school/collage project/assignment.
TSQL - subquery inside Begin End
Consider the following query:
begin ;with t1 as ( select top(10) x from tableX ), t2 as ( select * from t1 ), t3 as ( select * from t1 ) -- -------------------------- select * from t2 join t3 on t3.x=t2.x end go
I was wondering if
t1is called twice hence tableX being called twice (which means
t1acts like a table)?
or just once with its rows saved in t1 for the whole query (like a variable in a programming lang)?
Just trying to figure out how tsql engine optimises this. This is important to know because if
t1has millions of rows and is being called many times in the whole query generating the same result then there should be a better way to do it..