Right now I have two stored procedures that return data sets, and I'd like to create another stored procedure that executes both stored procedures and returns their combined datasets (to a .Net application). Is this as simple as just running "EXEC" on both of the stored procedures or do I need to add in some logic that combines the two ...
How can I unzip a varbinary(max) value in a stored procedure? I'd like to implement or invoke a gunzip algorithm within TSQL code, preferably without enabling the CLR. ...
Hi, We have a bit of a SQL quandry. Say I have a results that look like this... 61E77D90-D53D-4E2E-A09E-9D6F012EB59C | A 61E77D90-D53D-4E2E-A09E-9D6F012EB59C | B 61E77D90-D53D-4E2E-A09E-9D6F012EB59C | C 61E77D90-D53D-4E2E-A09E-9D6F012EB59C | D 7ce953ca-a55b-4c55-a52c-9d6f012ea903 | E 7ce953ca-a55b-4c55-a52c-9d6f012ea903 | F is...
I would like a query that will show a sum of columns with a default value for missing data. For example assume I have a table as follows: type_lookup: id name ----------- 1 self 2 manager 3 peer And a table as follows data: id type_lookup_id value -------------------------- 1 1 1 2 1 4 3 2 ...
I am wondering what the literal for a Null character (e.g. '\0') is in TSQL. Note: not a NULL field value, but the null character (see link). I have a column with a mix of typical and a null character. I'm trying to replace the null character with a different value. I would have thought that the following would work but it is unsucce...
Given a table that represents a hierarchical tree structure and has three columns ID (Primary Key, not-autoincrementing) ParentGroupID SomeValue I know the lowest most node of that branch, and I want to copy that to a new branch with the same number of parents that also need to be cloned. I am trying to write a single SQL INSERT IN...
Hi, I got to work on database with read only permission. Now i wrote a query with a reusable code in it. the query will be like select col1, clo2 from tablename where col1 = substring(substring(x,charindex('\',x),len(x)),0,charindex('\',substring(x))) here 'x' is my resusable code which would be like below one. x= stufff(col3,char...
I have created a sample query in sql server to parse data from xml and to display it right now. Although I will be inserting this data in my table but before that I am facing a simple problem. I want to insert NULL in datetime field ADDED_DATE="NULL" as shown in xml given below. But when I executes this query. It gives me error Convers...
I have two same tables. I need to union them in such way: SELECT f1,f2, xxx FROM (SELECT * FROM tbl1 UNION ALL SELECT * FROM tbl2) where xxx would query for a table name, where f1 and f2 fields are taken from. Example output: 123 345 'tbl1' -- this rows are from the first table 121 345 'tbl1' 121 345 'tbl1' 1...
I'd like to get all tags that match a given id where: select tag where id = 101 //returns 4 rows soup nuts juice milk Only now, i'd like to use that as a subquery - select idList, (select tag where id = 101) itemsOnList, shopper from assignedLists becomes: 10 | soup,nuts,juice,milk | Mom ...
Microsoft has the following example for try...catch in tsql: USE AdventureWorks; GO -- SET XACT_ABORT ON will render the transaction uncommittable -- when the constraint violation occurs. SET XACT_ABORT ON; BEGIN TRY BEGIN TRANSACTION; -- A FOREIGN KEY constraint exists on this table. This -- statement will genera...
Hi, Hope my subject line was descriptive enough. Basically, I have a table called Students, and one called Absences. The Students table contains student information (Name, DOB, StudentID etc.), the Absences table contains any absences (Date, Reason, related by StudentID). I want to get a full list of the students, plus a count of their...
There's probably any easy solution for this, but I can't see it. I have a table with consecutive dates and often duplicate associated data for several of these consecutive dates: Date Col1 Col2 5/13/2010 1 A 5/14/2010 1 A 5/15/2010 2 B 5/16/2010 1 A 5/17/2010 1 A 5/18/2010 3 C 5/19/2010 3 C 5/2...
Hi all, I'm tryng to build a stored procedure that makes use of another stored proceudre. Taking its result and using it as part of its where clause, from some reason I receive an error: Invalid object name 'dbo.GetSuitableCategories'. Here is a copy of the code: select distinct top 6 * from ( SELECT TOP 100 * FROM [dbo].[produ...
i write this query using t-sql and give me an error (Incorrect syntax near the keyword 'OR') How to write it in correct way ? SELECT SUM(Quantity) FROM Invoices WHERE Invoices.InvoiceDocStatusID = CASE @InventoryType WHEN 1 THEN ((2)OR(1)) ELSE 2 ...
Which approach is better to use: BoundField.NullDisplayText isn't set. NULL-case is foreseen in SQL query, i.e. SELECT ISNULL(amount, 0) FROM table or BoundField.NullDisplayText is set, e.g. "0.00 %". NULL-case isn't foreseen in SQL query, i.e. SELECT amount FROM table What do you think? ...
I have a stored procedure which is returning me about 50 columns. I want to write a query, where I will be able to select a particular column from the list of column returned by the SP. I tried writing select RSA_ID from exec(uspRisksEditSelect '1') But Its throwing me an error. I think we need to write some dynamic sql for it. But I am...
I have a table of sales data for example: SELECT ItemCode, ItemDesc, TotalYearlySales, ShareOfBusiness, ABCIndicator FROM Sales WHERE Yir = Year(getdate()) AND Manth = Month(getdate()) ORDER BY TotalYearlySales DESC The ShareOfBusiness is computed as the Item's (TotalYearlySales/SUM(TotalYearlySales))*100 The ABCIndicator is A for th...
Basically I have a User-Defined Table Type (for use as a table-valued variable) which I'm referencing in a stored procedure that effectively just calls two other stored procedures, then inserts those values into the table type. Id est INSERT INTO @tableValuedVariable (var1, var2, var3, var4, var5) EXEC [dbo].StoredProcedure1; ...
We store a decimal(9,8) in our database. It can have any number of places after the decimal point (well, no more than 8). I am frustrated because I want to display it as human-readable text as part of a larger string created on the server. I want as many decimals to the right of the decimal point as are non-zero, for example: 0.05 0....