CK--
I read over the article, it seems like it was more about using REGEX and WILDCARDS. What I was trying to avoid was splitting the string apart and building a dynamic query. Like I said before I am going to pass in an array of about 6 numbers that the column might start with. The value is 28 digits long, I am going to pass in the first 4 or 5 digits, I want to pull all the records that match the set of the first 4 or 5 digits. Example:...
dcharnigo
Last Activity: Jan 21 '18, 06:38 PM
Joined: Feb 15 '07
Location:
-
Can I use LIKE with IN?
I found this example of passing a list into a stored procedure:
...Code:CREATE PROC dbo.GetOrderList1 ( @OrderList varchar(500) ) AS BEGIN SET NOCOUNT ON DECLARE @SQL varchar(600) SET @SQL = 'SELECT OrderID, CustomerID, EmployeeID, OrderDate FROM dbo.Orders WHERE OrderID IN (' + @OrderList + ')' EXEC(@SQL) END -
Thanks, Apparently I wasn't paying attention, it works fine with a list dictionary! Thanks!Leave a comment:
-
SortedList Custom IComparer NullReferenceException
I thought I would be tricky and force the SortedList to not sort using a custom IComparer, I used the following code, but when the list is sorting using the custom sort the accessors become unusable and throw exceptions. I have since discovered that the accessors use the IComparer to find things and since it is set at -1 it gets a null return value, however the foreach loops dont use it. I must preserve the insert order, I don't want the sortinglist... -
Oh &^%$$%^^ If you notice in the first post I said the query was "where guid = guid" the actual query was "where guid like 'guid'", i changed it to an equal sign and it returns in a half second.Leave a comment:
-
Slow Query causing application problems
Hi All,
I am running a sql query from a web application. The web application timeout is set to 30 seconds and really should not be increased. I am running a very specific query [select * from table where guid = 'guid']. The table currently has 1 million rows and is growing everyday. The table is indexed but not on the guid field. Other queries return quickly but since this query is for only a single record based on the unique guid... -
Thanks, that was a real "Duh" after I read your post. I was even going to title it dynamic query building! Thanks again.Leave a comment:
-
Leave a comment:
-
Cases in Select, Where, and OrderBy Dynamic Clause
Well I am at it again with my complex statements, I need some guidance on this one, in case you cannot tell I am a C++/C# programmer and not used to SQL, I think it is clear what I am trying to accomplish by what I have but ask if you need me to clarify:
...Code:PROCEDURE [dbo].[spGetCIFSearch] ( @type int, @max int, @data varchar(25), @flag int ) AS SET NOCOUNT ON
-
Thanks for the help, You gave me a good start I will get it to work. I guess whenever I need to do a "foreach" I need to select into a temp table and then execute the next select on the temp table.
Thanks,
DanLeave a comment:
-
I ended up solving the problem myself, I guess if I would have posted the entire procedure it might have given it away. Apparently you cannot use a ROWCOUNT:
BAD PROCEDURE
...Code:SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[spGetCIFOrphans1] @max as int = 101 AS SET ROWCOUNT @max BEGIN SELECT * INTO zCIFRecord1
Leave a comment:
-
CK-
Been working on this some more, running our application in the debugger, The application uses a DSN to connect to the sql server, I am running the profiler as well, do you know if there is a way to view the data coming across the DSN?
I think there is some sort of problem in these commands:
...Code:while ((retcode = SQLFetch(_hstmt)) != SQL_NO_DATA) { .... ok = SQLGetData(_hstmt,Leave a comment:
-
I might not have understood exactly what you meant, but instead of selecting into a temp table I let the server create a actual table, the data looked good, but again the same thing happened, in the Management Studio I get the three rows I am expecting, in my web applications I get about 17,000 empty records. I need to be specific, the "temp" table should have about 17,000 records in it, which is slightly less than the original table, the...Leave a comment:
-
Stored Procedure Problem
I created a stored procedure... and when I execute it in the sql managment studio I get the data I was expecting. But when I execute it through TSQL in code I get back an empty row for each row in the table, 17,000 records and not the results I see in management studio. I think this is because I have multiple selects in my SP, how do I get around this? (I was watching in the profiler and saw it returned 17,000 whatever rows both when executed in... -
Foreach in MSSQL 2005
I am writing a purge routine for a database that I have. I select all records that have aged to a certain threshold into a temp table, now I want to remove some of these items, then in the end after everything is sorted remove the remaining records from the original table. Each record has a unique guid.
So to delete at the end I think I just need to do:
DELETE * FROM orig_table where orig_table.guid IN temp_table... -
Thanks, that got me headed in the correct direction and I got it to work, here is the final code for anyone who might want to do something similar:
...Code:SELECT CIFPan, CIFMemNum, CIFLName, CIFFName, CIFInitial, CIFExpDate FROM zCIFRecord WHERE CASE WHEN ISDATE(CIFExpDate) = 1 THEN CONVERT(datetime, CIFExpDate)Leave a comment:
-
Ok Closer I got the select to work:
But when I add the WHERE clause it fails:Code:SELECT CIFExpDate = CASE WHEN ISDATE(CIFExpDate) = 1 THEN CONVERT(datetime, CIFExpDate) ELSE CURRENT_TIMESTAMP END FROM zCIFRecord
This is obviously because the field is only being temporarily updated, so I assigned...Code:WHERE CIFExpDate < CURRENT_TIMESTAMP
Leave a comment:
-
smalldatetime convert problem
I am converting a varchar(10) field to a smalldatetime in a stored procedure but have run into a problem. Some of the rows contain an invalid date. varchar(10) format is mm/dd/yyyy some rows are filled with 00/00/0000 however causing the convert to fail. how can I get around this? Updating the table is not an option. My convert is as follows:
...
WHERE CONVERT(datetim e, CIFExpDate) < CURRENT_TIMESTA MP
ORDER BY CONVERT(datetim e,... -
Thanks for the help her is my end result:
SELECT i.user_id, i.login, i.Institution_i nst_id, t.inst_name, m.Role_role_id, r.role_name
FROM IsUser AS i
LEFT JOIN Institution AS t ON (i.Institution_ inst_id=t.inst_ id)
LEFT JOIN Membership AS m ON (i.user_id=m.Is User_user_id)
LEFT JOIN Role AS r ON (m.Role_role_id =r.role_id)...Leave a comment:
-
SQL Statement Problem Missing Data
Here is my database layout:
Institution:
...inst_id
...inst_name
IsUser:
...user_id
...login
...Institution_ inst_id
Membership:
...IsUser_user_ id
...Role_role_id
Role:
...role_id
...Institution_ inst_id
...role_name
The Database is the back end for an application. When a user is created they are NOT assigned...
No activity results to display
Leave a comment: