I have the query like this SELECT TABLE_NAME, COLUMN_NAME, IS_NULLABLE, DATA_TYPE FROM MY_DB.INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'OrderId' ORDER BY TABLE_NAME GO OrderId column is of user-defined data type. But DATA_TYPE in the query shows underlying system type (i.e. bigint). How to show user-defined type name? ...
How do I escape a string in SQL Server's stored procedure so that it is safe to use in LIKE expression. Suppose I have an NVARCHAR variable like so: declare @myString NVARCHAR(100); And I want to use it in a LIKE expression: ... WHERE ... LIKE '%' + @myString + '%'; How do I escape the string (more specifically, characters that ar...
I've written a paged search stored procedure using SQL Server 2005. It takes a number of parameters and the search criteria is moderately complex. Due to the front-end architecture I need to be able to return the number of results that would come back without actually returning the results. The front end would then call the stored proce...
SQL Server 2005. I'm adding Foreign Key constraints to the database of an application that allegedly didn't need them. Naturally, the data has become unreliable and there are orphaned entries in the foreign key field. Setup: Two tables, TableUser and TableOrder. TableUser has Primary Key 'UserID', and TableOrder has Foreign Key 'UserI...
Let's say I have a simple stored procedure that looks like this (note: this is just an example, not a practical procedure): CREATE PROCEDURE incrementCounter AS DECLARE current int SET current = (select CounterColumn from MyTable) + 1 UPDATE MyTable SET CounterColumn = current GO We're assuming I have a table called 'myTable...
I have a query in which I am pulling the runtime of an executable. The database contains its start time and its end time. I would like to get the total time for the run. So far I have: SELECT startTime, endTime, cast(datediff(hh,starttime,endtime) as varchar) +':' +cast(datediff(mi,starttime,endtime)-60*datediff(hh,starttime,endtime) as...
I know you cannot use a alias column in the where clause for T-SQL; however, has Microsoft provided some kind of workaround for this? Related Questions: Unknown Column In Where Clause Can you use an alias in the WHERE clause in mysql? “Invalid column name” error on SQL statement from OpenQuery results ...
Hi, I am trying to do something like this: while @nrOfAuthlevels >= @myAuthLevel begin set @myAuthLevel = @myAuthLevel + 1 SELECT Role.name, Role.authorityLevel FROM [dbo].[Role] ORDER BY Role.authorityLevel end The result of this stored procedure shall be a table with all Role.authorityLevel below my own. But this genera...
Hi, I have a very simple question. I have 15 stored procedures that return data from a common table, then join that table with a specific table to retrieve inventory. Ex. Common: tblCommonSpecific: tblSpecific Is there i was i can pass the name "tblSpecific" into a single stored procedure as a variable, like this: SELECT .... FROM ...
A co-worker recently ran into a situation where a query to look up security permissions was taking ~15 seconds to run using an = comparison on UserID (which is a UNIQUEIDENTIFIER). Needless to say, the users were less than impressed. Out of frustration, my co-worker changed the = comparison to use a LIKE and the query sped up to under 1...
I've got a Clr user defined type that takes a string of values sepertated by a comma. I'm losing a little bit of precision when converting to this type, I've narrowed it down to this line of code: cast(cast(nlinkjt as nvarchar(100)) + ',' +cast(avglrwf as nvarchar(100)) + ',' + cast(avglrwfjt as nvarchar(100)) as dbo.CLRJourneyTime) as...
My table has the following schema: id, parent_id, text Given the following data I would like to return an xml hierarchy: Data: (1,null,'x'), (2,1,'y'), (3,1,'z'), (4,2,'a') XML: [row text="x"] [row text="y"] [row text="a"/] [/row] [row text="z"/] [/row] Added: the hierachy has no maximum depth ...
I'm working on an app that takes data from our DB and outputs an xml file using the FOR XML AUTO, ELEMENTS on the end of the generated query, followed by an XSLT to transform it the way we want. However in a particular case where we are generating some data using an sql scalar function, it always puts that element into a sub-table node ...
When @RadioServiceGroup is set to NULL, I want to return all the records from the sbi_l_radioservicecodes table which has about 120 records. However, when I execute the following procedure and set the @RadioServiceGroup to NULL, it returns no records. Here is the stored proc: CREATE PROCEDURE [dbo].[GetRadioServiceCodes] @RadioServic...
I added the columns in the select list to the order by list, but it is still giving me the error: ORDER BY items must appear in the select list if SELECT DISTINCT is specified. Here is the stored proc: CREATE PROCEDURE [dbo].[GetRadioServiceCodesINGroup] @RadioServiceGroup nvarchar(1000) = NULL AS BEGIN SET NOCOUNT ON; SELECT DISTIN...
For a rigorous marker of the source database state, I'd like to capture the @@DBTS of an external database in a sproc. Yeah, I think I could issue USE ExternalDB GO SELECT @myVarbinary8 = @@DBTS GO USE OriginalDB GO but, even if I could, it seems ugly. For now, I've embedded a scalar-valued function in the source database to i...
I have a table "Blah" with a PK column BlahID and an XML column BlahItems in a database on SQL Server 2005. This table has records as follows... BlahID BlahItems ------ ------------------------------------------------------ 1 <root><item name="Fred" /><item name="Wilma" /></root> 2 <root><item name="Lisa" /><item name="Bart"...
I have a stored procedure that has a optional parameter, @UserID VARCHAR(50). The thing is, there are two ways to work with it: Give it a default value of NULL, the have an IF...ELSE clause, that performs two different SELECT queries, one with 'WHERE UserID = @UserID' and without the where. Give it a default value of '%' and then just ...
I am trying to convert a date with individual parts such as 12, 1, 2007 into a datetime in SQL Server 2005. I have tried the following: CAST(DATEPART(year, DATE)+'-'+ DATEPART(month, DATE) +'-'+ DATEPART(day, DATE) AS DATETIME) but this results in the wrong date. What is the correct way to turn the three date values into a proper date...
I'm having some trouble figuring out how to use more than one left outer join using LINQ to SQL. I understand how to use one left outer join. I'm using VB.NET. Below is my SQL syntax. T-SQL SELECT o.OrderNumber, v.VendorName, s.StatusName FROM Orders o LEFT OUTER JOIN Vendors v ON v.Id = o.VendorId LEFT OUTER JOI...