Search results for query: *

I'm trying to use TOP 3 in a query that I've made the recordsource of a subreport and it returns no records. If I simply remove the "TOP 3" portion of the query, it returns records, no problem. Is there a caveat to using TOP 3 in certain ways? Here's the query I am using as my recordsource (the...

LNBruno, Thanks for the references! I will look at these in detail- so far it seems that my needs are pretty simple and a cursor won't do much harm, but I'll experiment a bit with the other way of doing it and see what I get!

New to SQL Server and I'm having a BLAST! Converting an Access database, and really enjoying the experience. Since I'm not the most experienced SQL guy yet, I was wondering what the best approach might be to this problem: I have a simple query with four tables: tAGProjects, tProjectTypes...

OK, I will review the debug parameter info. Again, thanks for all the help SQLSister!

SQLSister, Actually, sommarskog's site is where I went to get info on building my dynamic sql and I am implementing sommarskog's suggestions. I'm using sp_executesql with parameters. I've looked at every one of sommerskog's suggestions and really, I have no other option as the requirements...

SQLSister, Thank you for your response! I was already toying with the idea of writing an IF statement that would include joins to the tables as they were needed based on whether or not a parameter was passed in. Essentially, like this: IF @Prod Is Not Null BEGIN sSQL = sSQL + 'LEFT JOIN...

This query requires a lot of tables because I'm giving the user a LOT of flexibility with optional parameters. Everything is working fine except I added four more tables/parameters to the end of the query (Producer, ExecProducer, Director, Contact) and it has caused a huge jump in the time to...

philhege, Thank you for the reply- that answers it perfectly. Looks like nvarchar(max) doesn't work so well with the PRINT option in SQL Server if you have more than 4000 chars. I'll try their workaround.

Thanks for the reply vbkris and SQLSister! What I discovered is the nvarchar(max) variable IS storing all the characters, but when I print the variable to the message tab for troubleshooting, it only prints 4000 characters. Hence, I'm not able to find the problem in the WHERE clause because it...

I've created a stored proc with a dynamic where clause - I've declared sSQL as a variable of type nvarchar(max) so that I can then run sp_executesql at the end of the stored proc passing in the sSQL variable. Everything was working fine last week, but now, the sp won't return any results and...

Remou, Much more succinct, thank you! Ended up with: Do While InStr(sParsed,",,")>0 sParsed = Replace(sParsed,",,",",") Loop Thanks for showing me the short way to write it! Sometimes I complicate it too much!

genomon, Yeah, I was going that route and thought there might be something more elegant too- alas, here's what I came up with: Private Sub cmdTest_Click() Dim bEqual As Boolean Dim sParsed As String Dim sNew As String sParsed = "1,,,,5009" Do Until bEqual = True...

I have created a function that accepts two comma separated text strings sNew and sExisting- I put both csv strings into an array then I check sNew against sExisting to see if any of the sNew are contained in sExisting. If so, I remove the sNew value from sExisting. The problem I'm running into...

Oops, mixed table and parameter names, sorry, reposting with the proper names: I'm a little fuzzy here- I get that if the column isn't allowed to be null, I'm fine as is. But what I'm trying to understand is if the parameter tests as null, I want to essentially ignore the criteria in the where...

I'm a little fuzzy here- I get that if the column isn't allowed to be null, I'm fine as is. But what I'm trying to understand is if the parameter tests as null, I want to essentially ignore the criteria in the where clause identified with that parameter. So if @AGProjectKey IS NULL, do not use...

OK, getting rid of the ISNULL completely eliminated the delay. My problem is that the parameters for this stored proc are optional and when I declare them in the sp I'm setting them to null so that in the WHERE clause I can test for the existence of the criteria. Should I approach testing for a...

Wow, you weren't kidding about it not being optimal! It took 9 seconds to run with the above code. I have a very long query (150 lines) prior to the WHERE statement, so though I thought about approaching it from a JOIN, it just seemed too daunting. But if you think it would speed it up, I've got...

Hey guys, what you turned me onto here is AWESOME! It's working great! I have a question though about a line in my WHERE statement that is giving me an error. Essentially, what I'm trying to do is check if the parameter passed into the sp, @AGProjectKey, is not null, then I run my split...

I'm reading this MSDN article, and I'm wondering if there's anything like the Split() function built into SQL Server 2005?

Thank you guys~ sorry to ask what it seems like many have asked for already. I'm looking at George's links right now and I'm going to get it wired. Thanks!