Passing table variable into dynamic SQL 2008
I need to run a dynamic sql which uses table variable created in the scope of the parent. How do I pass table variable into dynamic sql in SQL2008 ? ...
tsql
T-SQL - Weired Result's on Search
I am trying to search for any string that contains ";" in a column of a table which results in some weired resultset. Its not only returning me the results that has ";" but also returns those rows which doesn't contain any ";" but contains "'". Following is my query select * from fields where Comment like '%;%' and returned re...
Error using CSV string with IN Operator
When I run the following code declare @aaa nvarchar(10) set @aaa='1,2,3' Select * from Customer where CustomerId in (convert(nvarchar,@aaa,10)) I get this Error Msg 8114, Level 16, State 5, Line 3 Error converting data type nvarchar to bigint. plz help ...
Is it a good idea to store attributes in an integer column and perform bitwise operations to retrieve them?
In a recent CODE Magazine article, John Petersen shows how to use bitwise operators in TSQL in order to store a list of attributes in one column of a db table. Article here. In his example he's using one integer column to hold how a customer wants to be contacted (email,phone,fax,mail). The query for pulling out customers that want to ...
Trying to understand a SQL where clause
I am hoping someone can help interpret the last code line (@Active=1....) for this SQL clause: SELECT DISTINCT LOC_ID ,LOC_CODE ,ADDR_LINE_1 ,ADDR_LINE_2 ,ADDR_LINE_3 ,CITY ,STATE ,COUNTRY ,POSTAL_CODE ,COMPANY ,OPERATION_TYPE ,PROCESS ,ADDR_LINE_1 + ',' +ADDR_LINE_2+ ',' + CITY + '-' ...
Exclude .LOG file while restoring a database
Hi I have a database backup file (MyDatabase.bak). When restoring the database through SSMS it gives me "Not enough space on disk" message. I looked for an option to exclude .LOG file from restore but could not find one. I even tried to remove the "MOVE .LOG" from restore query but got an error message "could not find .LOG file use WI...
SQL and ALL operator
Looking for an elegant way to workaround this... DECLARE @ZIP INT SET @ZIP = 55555 IF @ZIP = ALL(SELECT ZIP FROM PEOPLE WHERE PERSONTYPE = 1) PRINT 'All people of type 1 have the same zip!' ELSE PRINT 'Not All people of type 1 have the same zip!' The issue is that, if (SELECT ZIP FROM PEOPLE WHERE PERSONTYPE = 1) returns no reco...
SQL Server - Auto-incrementation that allows UPDATE statements
When adding an item in my database, I need it to auto-determine the value for the field DisplayOrder. Identity (auto-increment) would be an ideal solution, but I need to be able to programmatically change (UPDATE) the values of the DisplayOrder column, and Identity doesn't seem to allow that. For the moment, I use this code: CREATE PROC...
Need help creating complex T-SQL SELECT statement.
I am trying to make SELECT statement for following situation and need help to make this SELECT statement. It's SQL Server 2005. When the select statement is run, it should return rows which have SentDate as NULL assuming that there are no duplicate PersonID in table. It will return result set with Status as 'Initial Record' (as the same...
VERY WEIRD T-SQL issue - query works but not if I encapsulate it as a sub-query to just select * from ()
Hello All, I have the following code which works FANTASTIC. Except - It is a sub-query to another (final) outer query and when I attempt to encapsulate this as a sub-query it refuses to run. To make it simple, I just did a "SELECT * FROM ( MY QUERY HERE)" and that even fails to run. I can only figure I am putting the right ")" in the...
Using C# to Select from SQL database Table
Hello All- I have a List of UserID's and a open connection to SQL Server. How can I loop through this List and Select matching UserID with First_Name and Last_Name columns? I assume the output can be in a datatable? many thanks ...
SQL Syntax error on one machine but not the other
I have a legacy web-site that I am maintaining (built mostly in classic ASP with vbscript). I am making some modifications to it, and got tired of waiting 5 minutes everytime I had to go through one of the pages that was loading 99000 records from the database before displaying the first 20 of them, so I rewrote the code as: sSQL = "WIT...
Count based on condition in SQL Server
Does anyone know how can I do a count in SQL Server based on condition. Example: How can I do a column count for records with name 'system', and total caseid records in the table?? Customer table userid caseid name 1 100 alan 1 101 alan 1 102 amy 1 103 system 1 ...
Using a User Defined Function in a View
I wrote the code for a View which needs to call a user defined function which returns a table to join with it. The problem here is passing the parameter that this functions needs straight out of my view. Below is the code of my view: select GG.Gid, GG.StockType StockType, COALESCE(STC.Contract, 0) ContractId, COALESCE(C...
SQL LIKE Command trouble searching for '
Hi I am facing a problem with the like command in SQL, I want to search for special characters within a column . The special characters are a single quotation mark ' and { and }.. I have tried placing these special characters under [] but still it doesn't work for ' I have also used the except option but that was also of no help.. Wa...
Retrieve last row inserted with Uniqueidentifier that it is not IDENTITY
Hi, I am puzzled with a query. I need find out the LAST row added in a table with a column with datatype Uniqueidentifier column is: aspnet_Applications.ApplicationId Notes: This column Uniqueidentifier it is NOT and IDENTITY Column. I need also take the last row inserted and update it on a different table aspnet_Users.ApplicationId I...
How to avoid T-SQL function being called more times when needing combined results?
Hello, I have two T-SQL scalar functions that both perform calculations over large sums of data (taking 'a lot' of time) and return a value, e.g. CalculateAllIncomes(EmployeeID) and CalculateAllExpenditures(EmployeeID). I run a select statement that calls these and returns results for each Employee. I also need the balance of each empl...
What is the use of "&" operator in SQL SERVER.
What is the use of & operator in the code specified below. IS there any benefit of using & instead of "AND". Please elaborate. CASE ( C.[Status] & F.[Status] & D.[Status] & DWT.[Status] & P.[Status] ) WHEN 1 THEN CASE ( C.IsDeleted & F.IsDeleted & D.IsDeleted & P.IsDeleted ) WHEN 0 THEN NULL ELSE 7 EN...
Search by date SQL server 2005
Hello everyone i want search data from invoices and client by today date I'm using GETDATE() for example two tables 1 Client - ID int - Name Varcher 2 Invoice - ID int - ClientID int - date Datetime - Total money query Select * from client c inner join invoice i on c.id = i.ClientID where i.date = g...
SQL Sever: in...case...in WHERE clause
Hi, I need to code up a query for something like this: Select [something] Where condition in case when (if another_condition = A and 3rd Condition = B) then (C,D) when (if another_condition = N and 3rd Condition = E) then (F,G) else (J,K) end essentially, what I want is if A and B are met, condition could be set to either C or D, if N...